Home » RDBMS Server » Performance Tuning » DB paramater Optimizer Index Cost Adjustment
DB paramater Optimizer Index Cost Adjustment [message #65468] Tue, 28 September 2004 03:13 Go to next message
rajnish sahay
Messages: 6
Registered: April 2004
Junior Member
I executed one of my programs and it doesnot use indexes inspite of indexes already created in the database.It takes 2 hrs to execute it. The source table record count is 1 million.

We changed the DB paramater Optimizer Index Cost Adjustment from 100 to 20 and the program executed in 3 minutes.

The DBAs are not willing to change the DB paramater Optimizer Index Cost Adjustment . They say Oracle has provided a default value of 100 and it should be OK.

They are concerned changing it to 20 might have negative impact on the performance of other programs.

Kindly advice what is the negative impact of changing the parameter from 100 to 20.

And is this approach of changing the paramter to force index usage a correct approach.
Re: DB paramater Optimizer Index Cost Adjustment [message #65470 is a reply to message #65468] Wed, 29 September 2004 22:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In an otherwise well performing application, changing the parameter will force other (well performing) statements to choose other plans.
This could have unwanted side-effects.
If your query is the only non-performant one, I can't blame the DBAs. You might want to build a test-environment, identical to your production environment, and test the new value in there, to see the side-effects if any. Do this in cooperation with the DBAs.

hth
Re: DB paramater Optimizer Index Cost Adjustment [message #65474 is a reply to message #65468] Thu, 30 September 2004 06:09 Go to previous message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Agreed with Frank.

If your "program" is not using the index, then you need to investigate why it is NOT using the index.

Does the program need some "tuning"?
Are statistics updated?

Is your query pulling almost all the records of table? (then CBO settles with full tablescan.).
Previous Topic: PE6650 and disk configuration
Next Topic: PLUSTRACE Role
Goto Forum:
  


Current Time: Tue Sep 22 07:40:50 CDT 2020