Thursday, April 05, 2007

optimize parameters

At the customer I working right now, they asked me to look at a query that did run in a test EBS database, but wouldn't run in a developer EBS database.
When running the query in the dev database, the session seems to hang and no records are returned.
So, first I looked at the explain plan from the query running in the test database. It looked like this..

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 339 K 103359

NESTED LOOPS 339 K 8 M 103359
TABLE ACCESS BY INDEX ROWID CSI.CSI_T_EXTEND_ATTRIBS 339 K 6 M 103358
INDEX RANGE SCAN CSI.CSI_T_EXTEND_ATTRIBS_N02 339 K 4066
INDEX UNIQUE SCAN CSI.CSI_T_TXN_LINE_DETAILS_U01 1 6

Then I tried running the query in the dev database and the explain plan looked like..

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 339 K 56396

HASH JOIN 339 K 8 M 56396
TABLE ACCESS BY INDEX ROWID CSI.CSI_T_EXTEND_ATTRIBS 339 K 6 M 7
INDEX RANGE SCAN APPS.XXX_CSI_T_EXTEND_ATTRIBS_N1 339 K 2
TABLE ACCESS FULL CSI.CSI_T_TXN_LINE_DETAILS 11 M 64 M 51170

It seems in the dev database the query is doing a full table scan, instead of indexed scan in the test database. Also in the test database a NESTED LOOP is used, while the dev database uses a HASH JOIN ??
I focussed on the last finding and searched for a reason why the query should use HASH JOIN instead of a NESTED LOOP.
I found out the following two init.ora parameters were different in the dev database, the optimizer_index_caching parameter and the optimizer_index_cost_adj parameter. So, I tried running the query after I changed my session settings..

SQL> alter session set optimizer_index_caching=x;

SQL> alter session set optimizer_index_cost_adj=x;


And this time the query returned the desired rows ! Next step is to edited the init.ora so the whole dev environment can use these settings...

4 comments:

Anonymous said...

What were the values Bas of these parameters before and after you changed the, in Dev...

Bas Klaassen said...

I have mailed them to you..

Paul said...

Please note that Oracle does not let you modify those parameters, note 216205.1 has the full list of optimizer parameters and there suggested or mandatory values

Anonymous said...

Dear Paul,

If you read the note 216205.1, only the Mandatory parameters (MP) can't be changed.

For MP Parameters only: "The Use of values other than those provided in this document will not be supported unless Oracle
Support has specifically instructed you to alter these parameters."

The two parameters changed by Bas were not in the Mandatory Parameters list.

Cheers,

Francisco Munoz Alvarez
www.oraclenz.com