OraFAQ Forum: Performance Tuning » DB paramater Optimizer Index Cost Adjustment

Home » RDBMS Server » Performance Tuning » DB paramater Optimizer Index Cost Adjustment

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DB paramater Optimizer Index Cost Adjustment [message #65468]

Tue, 28 September 2004 03:13

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.

Report message to a moderator

Re: DB paramater Optimizer Index Cost Adjustment [message #65470 is a reply to message #65468]

Wed, 29 September 2004 22:07

Frank
Messages: 7901
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

Report message to a moderator

Re: DB paramater Optimizer Index Cost Adjustment [message #65474 is a reply to message #65468]

Thu, 30 September 2004 06:09

Mahesh Rajendran
Messages: 10707
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.).

Report message to a moderator

Previous Topic:	PE6650 and disk configuration
Next Topic:	PLUSTRACE Role

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 18:59:55 CDT 2024