OPTIMIZER_INDEX_COST_ADJ
- Thomas Amsler
Owned by Thomas Amsler
Hi Prabhu,
I remember I mentioned the parameter OPTIMIZER_INDEX_COST_ADJ and you answered that you have tried to lower the parameter to as low as 20 and it did not work. I did some my own tests today with tables that have 168K rows and a query that has similar structure as the CM query we discussed, i.e, all rows in the select statement comes from one table and none from the other table. The purpose of my tests was to see if adjust the OPTIMIZER_INDEX_COST_ADJ can change the execution plan. I adjust that parameter using "alter session"|| so that other session will not be affected. The parameter can also be adjusted on system level, which will affect all the sessions in the database. The index on PROVIDER_ID is not a unique index. The following are my test results.
The first two tests were when the parameter set to 100. Those two tests show when rows are selected from one table, that table will be full table scanned. Then I start to adjust the OPTIMIZER_INDEX_COST_ADJ. Just as what you have said, as low as 20 will not change the execution plan. In my case, even as low as 5 still does not use the index. So, I adjust that parameter to 1, the lowest possible value. The execution plan finally pick up the index and no more full table scan. The detail tests steps are shown below.
I am not suggesting anythings here to you and I just want to share my test results, in case you are interested in doing similar tests on your query. Since I don't know if the modification of the parameter will change other queries' behavior, I think more tests have to be done to determine if it's good or bad. In my case, it only needs about 10 minutes to do all the tests.
Thanks,
Drew
SQL> analyze table SAKAI_REALM_test compute statistics;
Table analyzed.
Elapsed: 00:00:03.65
SQL> analyze table SAKAI_REALM_new compute statistics;
Table analyzed.
Elapsed: 00:00:03.60
SQL> set timing on
SQL> set lines 150
SQL> set pages 10000
SQL> set autotrace traceonly explain
SQL> select b.* from SAKAI_REALM_test b,sakai_realm_new a where a.PROVIDER_ID=b.PROVIDER_ID;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1793553846
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17614 | 1668K| 560 (2)| 00:00:07 |
|* 1 | HASH JOIN | | 17614 | 1668K| 560 (2)| 00:00:07 |
|* 2 | INDEX FAST FULL SCAN| SAKAI_REALM_NEW_1 | 17474 | 87370 | 24 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | SAKAI_REALM_TEST | 17474 | 1569K| 535 (2)| 00:00:07 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."PROVIDER_ID"="B"."PROVIDER_ID")
2 - filter("A"."PROVIDER_ID" IS NOT NULL)
3 - filter("B"."PROVIDER_ID" IS NOT NULL)
SQL> select a.* from SAKAI_REALM_test b,sakai_realm_new a where a.PROVIDER_ID=b.PROVIDER_ID;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2576141811
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17614 | 1668K| 560 (2)| 00:00:07 |
|* 1 | HASH JOIN | | 17614 | 1668K| 560 (2)| 00:00:07 |
|* 2 | INDEX FAST FULL SCAN| SAKAI_REALM_TEST_1 | 17474 | 87370 | 24 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | SAKAI_REALM_NEW | 17474 | 1569K| 535 (2)| 00:00:07 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."PROVIDER_ID"="B"."PROVIDER_ID")
2 - filter("B"."PROVIDER_ID" IS NOT NULL)
3 - filter("A"."PROVIDER_ID" IS NOT NULL)
SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=50;
Session altered.
Elapsed: 00:00:00.00
SQL> select a.* from SAKAI_REALM_test b,sakai_realm_new a where a.PROVIDER_ID=b.PROVIDER_ID;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2576141811
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17614 | 1668K| 560 (2)| 00:00:07 |
|* 1 | HASH JOIN | | 17614 | 1668K| 560 (2)| 00:00:07 |
|* 2 | INDEX FAST FULL SCAN| SAKAI_REALM_TEST_1 | 17474 | 87370 | 24 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | SAKAI_REALM_NEW | 17474 | 1569K| 535 (2)| 00:00:07 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."PROVIDER_ID"="B"."PROVIDER_ID")
2 - filter("B"."PROVIDER_ID" IS NOT NULL)
3 - filter("A"."PROVIDER_ID" IS NOT NULL)
SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=5;
Session altered.
Elapsed: 00:00:00.00
SQL> select a.* from SAKAI_REALM_test b,sakai_realm_new a where a.PROVIDER_ID=b.PROVIDER_ID;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2546399704
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17614 | 1668K| 541 (2)| 00:00:07 |
|* 1 | HASH JOIN | | 17614 | 1668K| 541 (2)| 00:00:07 |
|* 2 | INDEX FULL SCAN | SAKAI_REALM_TEST_1 | 17474 | 87370 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| SAKAI_REALM_NEW | 17474 | 1569K| 535 (2)| 00:00:07 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."PROVIDER_ID"="B"."PROVIDER_ID")
2 - filter("B"."PROVIDER_ID" IS NOT NULL)
3 - filter("A"."PROVIDER_ID" IS NOT NULL)
SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=1;
Session altered.
Elapsed: 00:00:00.00
SQL> select a.* from SAKAI_REALM_test b,sakai_realm_new a where a.PROVIDER_ID=b.PROVIDER_ID;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1518020262
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17614 | 1668K| 177 (2)| 00:00:03 |
| 1 | MERGE JOIN | | 17614 | 1668K| 177 (2)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID| SAKAI_REALM_NEW | 17474 | 1569K| 173 (0)| 00:00:03 |
|* 3 | INDEX FULL SCAN | SAKAI_REALM_NEW_1 | 17474 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 17474 | 87370 | 4 (75)| 00:00:01 |
|* 5 | INDEX FULL SCAN | SAKAI_REALM_TEST_1 | 17474 | 87370 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."PROVIDER_ID" IS NOT NULL)
4 - access("A"."PROVIDER_ID"="B"."PROVIDER_ID")
filter("A"."PROVIDER_ID"="B"."PROVIDER_ID")
5 - filter("B"."PROVIDER_ID" IS NOT NULL)
----Back to original (not needed if using alter session)
SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=100;