DB version: 11.2.0.4
Platform : RHEL 6.5
I have a standard Siebel DB with lots of indexes. In Siebel DBs, OPTIMIZER_INDEX_COST_ADJ parameter is set to 1 to favour Indexes rather than full table scans.
In the below test, I set OPTIMIZER_INDEX_COST_ADJ parameter to 100 in my Siebel DB and I can see from the execution plan that the optimizer is preferring Full table scan for most of the large tables.
But, OPTIMIZER_INDEX_COST_ADJ parameter's default value is 100 and I don't see Full table Scans being preferred in non-Siebel DBs in my shop.h
So, I am guessing that there is an accompanying DB parameter (or parameters) for OPTIMIZER_INDEX_COST_ADJ . I would like to know which is other parameter (along with OPTIMIZER_INDEX_COST_ADJ) which is set in Siebel DBs that will favour Indexes rather than full table scans.
Below test shows the effects of OPTIMIZER_INDEX_COST_ADJ parameter when set to 100 and 1
-- Settin OPTIMIZER_INDEX_COST_ADJ parameter to 100
alter session set optimizer_index_cost_adj = 100;
explain plan for
SELECT /*+ ALL_ROWS */
T11.CONFLICT_ID,
T11.LAST_UPD,
T11.CREATED,
T11.LAST_UPD_BY,
T11.CREATED_BY,
T11.MODIFICATION_NUM,
T11.ROW_ID,
T11.ASSET_NUM,
T11.DESC_TEXT,
T11.ASSET_VAL_CURCY_CD,
T7.ONL_PAGESET_ID,
T11.ASSET_VAL_EXCH_DT,
T11.CFG_TYPE_CD,
T11.COST_LST_ID,
T11.INTEGRATION_ID,
T7.PROD_TYPE_CD,
T11.CFG_STATE_CD,
T6.CMPND_PROD_NUM,
T11.PR_POSTN_ID,
T11.OWNER_CON_ID,
T11.PROM_INTEG_ID,
T1.PROD_TYPE_CD,
T10.INTEGRATION_ID,
T2.ROW_ID,
T11.BU_ID,
T11.ASSET_MEM_INTEG_ID,
T11.PROM_GROUP_ID,
T11.PROM_GROUP_ITEM_ID,
T9.OU_NUM,
T11.PR_CON_ID,
T11.PR_EMP_ID,
T11.PORT_VALID_PROD_ID,
T7.NAME,
T11.PROD_ID,
T7.DESC_TEXT,
T7.PR_PROD_LN_ID,
T4.NAME,
T11.ASSEMBLY_PORT_ID,
T7.SERIALIZED_FLG,
T7.PROD_CD,
T11.XA_CLASS_ID,
T11.REGISTERED_DT,
T11.QTY,
T11.ROOT_ASSET_ID,
T10.CFG_TYPE_CD,
T10.PROD_ID,
T11.SERIAL_NUM,
T11.SP_NUM,
T11.STATUS_CD,
T11.VERSION,
T9.NAME,
T11.BILL_ACCNT_ID,
T8.NAME,
T11.EXTD_QTY,
T11.SERV_ACCT_ID,
T11.PR_ACCNT_ID,
T11.OWNER_ACCNT_ID,
T11.OWNERSHIP_TYPE_CD,
T11.PAR_ASSET_ID,
T7.PROMO_TYPE_CD,
T5.PAR_BU_ID,
T6.ROW_ID,
T6.PAR_ROW_ID,
T6.MODIFICATION_NUM,
T6.CREATED_BY,
T6.LAST_UPD_BY,
T6.CREATED,
T6.LAST_UPD,
T6.CONFLICT_ID,
T6.PAR_ROW_ID,
T3.ROW_ID
FROM
SIEBEL.S_PROD_INT T1,
SIEBEL.S_VOD T2,
SIEBEL.S_PARTY T3,
SIEBEL.S_PROD_LN T4,
SIEBEL.S_ORG_EXT T5,
SIEBEL.S_ASSET_OM T6,
SIEBEL.S_PROD_INT T7,
SIEBEL.S_ORG_EXT T8,
SIEBEL.S_ORG_EXT T9,
SIEBEL.S_ASSET T10,
SIEBEL.S_ASSET T11
WHERE
T11.BILL_ACCNT_ID = T9.PAR_ROW_ID AND
T11.SERV_ACCT_ID = T8.PAR_ROW_ID AND
T11.PROD_ID = T7.ROW_ID AND
T7.PR_PROD_LN_ID = T4.ROW_ID AND
T7.CFG_MODEL_ID = T2.OBJECT_NUM AND
T10.PROD_ID = T1.ROW_ID AND
T11.ROOT_ASSET_ID = T10.ROW_ID AND
T11.ROW_ID = T6.PAR_ROW_ID AND
T11.BU_ID = T3.ROW_ID AND
T11.BU_ID = T5.PAR_ROW_ID AND
(T11.X_MIG_FLAG = 'Y');
Explain plan: So many Full table Scans on large tables when OPTIMIZER_INDEX_COST_ADJ = 100
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 996K| 816M| | 329K (1)| 01:05:58 |
|* 1 | HASH JOIN RIGHT OUTER | | 996K| 816M| | 329K (1)| 01:05:58 |
| 2 | VIEW | index$_join$_004 | 34 | 748 | | 2 (0)| 00:00:01 |
|* 3 | HASH JOIN | | | | | | |
| 4 | INDEX FAST FULL SCAN | S_PROD_LN_P1 | 34 | 748 | | 1 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN | S_PROD_LN_U1 | 34 | 748 | | 1 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 996K| 795M| | 329K (1)| 01:05:58 |
| 7 | TABLE ACCESS FULL | S_VOD | 9157 | 169K| | 53 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 996K| 777M| 136M| 329K (1)| 01:05:57 |
| 9 | TABLE ACCESS FULL | S_ASSET_OM | 1460K| 119M| | 5705 (1)| 00:01:09 |
|* 10 | HASH JOIN RIGHT OUTER | | 989K| 691M| 9M| 282K (1)| 00:56:29 |
| 11 | TABLE ACCESS FULL | S_ORG_EXT | 415K| 5271K| | 11500 (1)| 00:02:19 |
|* 12 | HASH JOIN RIGHT OUTER | | 983K| 674M| | 236K (1)| 00:47:18 |
| 13 | TABLE ACCESS FULL | S_PROD_INT | 7419 | 115K| | 210 (1)| 00:00:03 |
|* 14 | HASH JOIN RIGHT OUTER | | 983K| 659M| | 236K (1)| 00:47:15 |
| 15 | TABLE ACCESS FULL | S_PROD_INT | 7419 | 1079K| | 210 (1)| 00:00:03 |
|* 16 | HASH JOIN RIGHT OUTER | | 983K| 519M| 17M| 236K (1)| 00:47:13 |
| 17 | VIEW | index$_join$_008 | 415K| 13M| | 5840 (1)| 00:01:11 |
|* 18 | HASH JOIN | | | | | | |
| 19 | INDEX FAST FULL SCAN | S_ORG_EXT_M15 | 415K| 13M| | 3248 (1)| 00:00:39 |
| 20 | INDEX FAST FULL SCAN | S_ORG_EXT_M23 | 415K| 13M| | 2156 (1)| 00:00:26 |
|* 21 | HASH JOIN RIGHT OUTER | | 983K| 488M| 22M| 204K (1)| 00:40:55 |
| 22 | TABLE ACCESS FULL | S_ORG_EXT | 415K| 17M| | 11499 (1)| 00:02:18 |
|* 23 | HASH JOIN RIGHT OUTER | | 983K| 447M| 121M| 169K (1)| 00:33:51 |
| 24 | TABLE ACCESS FULL | S_ASSET | 2283K| 95M| | 60151 (1)| 00:12:02 |
|* 25 | HASH JOIN RIGHT OUTER| | 983K| 406M| 21M| 82282 (1)| 00:16:28 |
| 26 | INDEX FAST FULL SCAN| S_PARTY_P1 | 977K| 10M| | 774 (1)| 00:00:10 |
|* 27 | TABLE ACCESS FULL | S_ASSET | 983K| 395M| | 60265 (1)| 00:12:04 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T7"."PR_PROD_LN_ID"="T4"."ROW_ID"(+))
3 - access(ROWID=ROWID)
6 - access("T7"."CFG_MODEL_ID"="T2"."OBJECT_NUM"(+))
8 - access("T11"."ROW_ID"="T6"."PAR_ROW_ID"(+))
10 - access("T11"."BU_ID"="T5"."PAR_ROW_ID"(+))
12 - access("T10"."PROD_ID"="T1"."ROW_ID"(+))
14 - access("T11"."PROD_ID"="T7"."ROW_ID"(+))
16 - access("T11"."SERV_ACCT_ID"="T8"."PAR_ROW_ID"(+))
18 - access(ROWID=ROWID)
21 - access("T11"."BILL_ACCNT_ID"="T9"."PAR_ROW_ID"(+))
23 - access("T11"."ROOT_ASSET_ID"="T10"."ROW_ID"(+))
25 - access("T11"."BU_ID"="T3"."ROW_ID"(+))
27 - filter("T11"."X_MIG_FLAG"='Y')
51 rows selected.
--- setting OPTIMIZER_INDEX_COST_ADJ to 1
alter session set optimizer_index_cost_adj = 1 ;
--- Not repeating the EXPLAIN PLAN command for readability
-- Only 1 Full table scan and S_PROD_INT is a small table
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 996K| 816M| 106K (1)| 00:21:15 |
| 1 | NESTED LOOPS OUTER | | 996K| 816M| 106K (1)| 00:21:15 |
| 2 | NESTED LOOPS OUTER | | 990K| 798M| 86381 (1)| 00:17:17 |
|* 3 | HASH JOIN RIGHT OUTER | | 983K| 712M| 56877 (1)| 00:11:23 |
| 4 | TABLE ACCESS FULL | S_PROD_INT | 7419 | 115K| 210 (1)| 00:00:03 |
| 5 | NESTED LOOPS OUTER | | 983K| 697M| 56664 (1)| 00:11:20 |
| 6 | NESTED LOOPS OUTER | | 983K| 656M| 36993 (1)| 00:07:24 |
| 7 | NESTED LOOPS OUTER | | 983K| 646M| 27158 (1)| 00:05:26 |
| 8 | NESTED LOOPS OUTER | | 983K| 604M| 14119 (1)| 00:02:50 |
|* 9 | HASH JOIN RIGHT OUTER | | 983K| 573M| 1066 (2)| 00:00:13 |
| 10 | VIEW | index$_join$_002 | 9157 | 169K| 2 (0)| 00:00:01 |
|* 11 | HASH JOIN | | | | | |
| 12 | INDEX FAST FULL SCAN | S_VOD_P1 | 9157 | 169K| 1 (0)| 00:00:01 |
| 13 | INDEX FAST FULL SCAN | S_VOD_U2 | 9157 | 169K| 1 (0)| 00:00:01 |
|* 14 | HASH JOIN RIGHT OUTER | | 983K| 556M| 1061 (1)| 00:00:13 |
| 15 | VIEW | index$_join$_004 | 34 | 748 | 2 (0)| 00:00:01 |
|* 16 | HASH JOIN | | | | | |
| 17 | INDEX FAST FULL SCAN | S_PROD_LN_P1 | 34 | 748 | 1 (0)| 00:00:01 |
| 18 | INDEX FAST FULL SCAN | S_PROD_LN_U1 | 34 | 748 | 1 (0)| 00:00:01 |
|* 19 | HASH JOIN RIGHT OUTER | | 983K| 535M| 1056 (1)| 00:00:13 |
| 20 | TABLE ACCESS FULL | S_PROD_INT | 7419 | 1079K| 210 (1)| 00:00:03 |
| 21 | TABLE ACCESS BY INDEX ROWID| S_ASSET | 983K| 395M| 844 (1)| 00:00:11 |
|* 22 | INDEX RANGE SCAN | S_ASSET_F20_X | 983K| | 18 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 33 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 44 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | S_ASSET | 1 | 44 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | S_ASSET_P1 | 1 | | 1 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | S_ASSET_OM | 1 | 86 | 1 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | S_ASSET_OM_U1 | 1 | | 1 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 13 | 1 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T10"."PROD_ID"="T1"."ROW_ID"(+))
9 - access("T7"."CFG_MODEL_ID"="T2"."OBJECT_NUM"(+))
11 - access(ROWID=ROWID)
14 - access("T7"."PR_PROD_LN_ID"="T4"."ROW_ID"(+))
16 - access(ROWID=ROWID)
19 - access("T11"."PROD_ID"="T7"."ROW_ID"(+))
22 - access("T11"."X_MIG_FLAG"='Y')
24 - access("T11"."SERV_ACCT_ID"="T8"."PAR_ROW_ID"(+))
26 - access("T11"."BILL_ACCNT_ID"="T9"."PAR_ROW_ID"(+))
27 - access("T11"."BU_ID"="T3"."ROW_ID"(+))
29 - access("T11"."ROOT_ASSET_ID"="T10"."ROW_ID"(+))
31 - access("T11"."ROW_ID"="T6"."PAR_ROW_ID"(+))
33 - access("T11"."BU_ID"="T5"."PAR_ROW_ID"(+))
57 rows selected.