| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> ORDER BY and first_rows_10 madness
Hi Listers,
on one of our SIEBEL OLTP databases I have a to understand the CBO choices.
Facts:
1) Oracle 9.2.0.6, OS = HPUX 11.11
2) SIEBEL Application server issues after creation of each connection the following sql statements:
alter session set optimizer_mode = first_rows_10; alter session set hash_join_enabled = false; alter session set "_optimizer_sortmerge_join_enabled" = false; alter session set "_optimizer_join_sel_sanity_check" = true;
3)Another CBO relavant parameters
optimizer_index_caching integer 75 optimizer_index_cost_adj integer 25 optimizer_max_permutations integer 500
4) The SQL
explain plan for
SELECT
T8.CONFLICT_ID,
T8.LAST_UPD,
T12.ASSET_ID
FROM
SIEBEL.S_ORG_EXT T1,
SIEBEL.S_ORG_EXT T2,
SIEBEL.S_MED_SPEC T3,
SIEBEL.S_CONTACT_X T4,
SIEBEL.S_CONTACT_XM T5,
SIEBEL.S_POSTN T6,
SIEBEL.S_POSTN T7,
SIEBEL.S_PARTY T8,
SIEBEL.S_PARTY T9,
SIEBEL.S_EMP_PER T10,
SIEBEL.S_CONTACT_SS T11,
SIEBEL.S_ASSET_CON T12,
SIEBEL.S_USER T13,
SIEBEL.S_CONTACT_FNX T14,
SIEBEL.S_ORG_EXT_FNX T15,
SIEBEL.S_PARTY T16,
SIEBEL.S_CONTACT_LOYX T17,
SIEBEL.S_USER T18,
SIEBEL.S_POSTN_CON T19,
SIEBEL.S_POSTN_CON T20,
SIEBEL.S_POSTN T21,
SIEBEL.S_ADDR_PER T22,
SIEBEL.S_CONTACT T23
WHERE
T23.PR_DEPT_OU_ID = T2.PAR_ROW_ID (+) AND
T2.PR_POSTN_ID = T21.PAR_ROW_ID (+) AND
T23.PR_POSTN_ID = T7.PAR_ROW_ID (+) AND
T8.ROW_ID = T20.CON_ID (+) AND T20.POSTN_ID (+) = :s1 AND
T21.PR_EMP_ID = T13.PAR_ROW_ID (+) AND
T7.PR_EMP_ID = T18.PAR_ROW_ID (+) AND
T23.PR_PER_ADDR_ID = T22.ROW_ID (+) AND
T23.MED_SPEC_ID = T3.ROW_ID (+) AND
T8.ROW_ID = T23.PAR_ROW_ID AND
T8.ROW_ID = T17.PAR_ROW_ID (+) AND
T8.ROW_ID = T10.PAR_ROW_ID (+) AND
T8.ROW_ID = T14.PAR_ROW_ID (+) AND
T8.ROW_ID = T4.PAR_ROW_ID (+) AND
T8.ROW_ID = T11.PAR_ROW_ID (+) AND
T23.PR_POSTN_ID = T19.POSTN_ID AND T23.ROW_ID = T19.CON_ID AND
T19.POSTN_ID = T16.ROW_ID AND
T19.POSTN_ID = T6.PAR_ROW_ID (+) AND
T23.PR_DEPT_OU_ID = T9.ROW_ID (+) AND
T23.PR_DEPT_OU_ID = T1.PAR_ROW_ID (+) AND
T23.PR_DEPT_OU_ID = T15.PAR_ROW_ID (+) AND
T23.X_PR_BOND_ID = T5.ROW_ID (+) AND T12.CONTACT_ID = T8.ROW_ID AND
((T12.X_REL_TYPE_FLG_1 = 'Y') AND
(T23.PRIV_FLG = 'N' AND T23.EMP_FLG != 'Y')) AND
(T12.ASSET_ID = :s2)
ORDER BY
T23.LAST_NAME, T23.FST_NAME;
5) The indexes:
there is an index (S_ASSET_CON_EXT01_X) on T12.ASSET_ID (almost unique, 99% distinct values, the index is not unique).
There an index on S_CONTACT_M12 on S_CONTACT(LAST_NAME,FST_NAME,PRIV_FLG) PRIV_FLG is pretty unselective -> only 1 value (99.5% of the rows)
6) The execution plan (from 10053 event trace file). Operation...........Object name.....Options.........Id...Pid..
SELECT STATEMENT 0 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 2 1 NESTED LOOPS OUTER 3 2 NESTED LOOPS OUTER 4 3 NESTED LOOPS OUTER 5 4 NESTED LOOPS OUTER 6 5 NESTED LOOPS 7 6 NESTED LOOPS OUTER 8 7 NESTED LOOPS OUTER 9 8 NESTED LOOPS OUTER 10 9 NESTED LOOPS OUTER 11 10 NESTED LOOPS OUTER 12 11 NESTED LOOPS 13 12 NESTED LOOPS OUTER 14 13 NESTED LOOPS OUTER 15 14 NESTED LOOPS 16 15 NESTED LOOPS OUTER 17 16 NESTED LOOPS 18 17 NESTED LOOPS OUTER 19 18 NESTED LOOPS OUTER 20 19 NESTED LOOPS OUTER 21 20 NESTED LOOPS OUTER 22 21 TABLE ACCESS S_CONTACT BY INDEX ROWID 23 22 INDEX S_CONTACT_M12 FULL SCAN 24 23 TABLE ACCESS S_MED_SPEC BY INDEX ROWID 25 22 INDEX S_MED_SPEC_P1 UNIQUE SCAN 26 25 TABLE ACCESS S_ORG_EXT_FNX BY INDEX ROWID 27 21 INDEX S_ORG_EXT_FNX_U1RANGE SCAN 28 27 TABLE ACCESS S_ORG_EXT BY INDEX ROWID 29 20 INDEX S_ORG_EXT_U3X_U1UNIQUE SCAN 30 29 TABLE ACCESS S_ORG_EXT BY INDEX ROWID 31 19 INDEX S_ORG_EXT_U3X_U1UNIQUE SCAN 32 31 TABLE ACCESS S_POSTN_CON BY INDEX ROWID 33 18 INDEX S_POSTN_CON_M3U1RANGE SCAN 34 33 TABLE ACCESS S_ADDR_PER BY INDEX ROWID 35 17 INDEX S_ADDR_PER_P13U1UNIQUE SCAN 36 35 TABLE ACCESS S_PARTY BY INDEX ROWID 37 16 INDEX S_PARTY_P1_P13U1UNIQUE SCAN 38 37 TABLE ACCESS S_CONTACT_LOYX BY INDEX ROWID 39 15 INDEX S_CONTACT_LOYX_URANGE SCAN 40 39 TABLE ACCESS S_CONTACT_SS BY INDEX ROWID 41 14 INDEX S_CONTACT_SS_U1URANGE SCAN 42 41 TABLE ACCESS S_ASSET_CON BY INDEX ROWID 43 13 INDEX S_ASSET_CON_F11URANGE SCAN 44 43 TABLE ACCESS S_EMP_PER BY INDEX ROWID 45 12 INDEX S_EMP_PER_U1F11UUNIQUE SCAN 46 45 TABLE ACCESS S_POSTN_CON BY INDEX ROWID 47 11 INDEX S_POSTN_CON_M31URANGE SCAN 48 47 TABLE ACCESS S_CONTACT_X BY INDEX ROWID 49 10 INDEX S_CONTACT_X_U11URANGE SCAN 50 49 TABLE ACCESS S_CONTACT_FNX BY INDEX ROWID 51 9 INDEX S_CONTACT_FNX_U1RANGE SCAN 52 51 INDEX S_PARTY_P1FNX_U1UNIQUE SCAN 53 8 INDEX S_PARTY_P1FNX_U1UNIQUE SCAN 54 7 TABLE ACCESS S_CONTACT_XM BY INDEX ROWID 55 6 TABLE ACCESS S_CONTACT_XM BY INDEX ROWID 55 6 INDEX S_CONTACT_XM_P11UNIQUE SCAN 56 55 TABLE ACCESS S_POSTN BY INDEX ROWID 57 5 INDEX S_POSTN_U2XM_P11UNIQUE SCAN 58 57 TABLE ACCESS S_USER BY INDEX ROWID 59 4 INDEX S_USER_U22XM_P11UNIQUE SCAN 60 59 TABLE ACCESS S_POSTN BY INDEX ROWID 61 3 INDEX S_POSTN_U2XM_P11UNIQUE SCAN 62 61 TABLE ACCESS S_USER BY INDEX ROWID 63 2 INDEX S_USER_U22XM_P11UNIQUE SCAN 64 63 TABLE ACCESS S_POSTN BY INDEX ROWID 65 1 INDEX S_POSTN_U2XM_P11UNIQUE SCAN 66 65
CBO is favoring almost always the table having indexes on columns included in the ORDER BY clause. This approach ist OK (the first rows should be returned as fast as possible and that is why the sort operation should be avoided, if possible), but I can not undestand the calculations of the CBO.
Optimally the CBO should choose the index on ASSET_ID, because of its selectivity. Actually the reponse time and LIOs are at least factor 10 better when using S_ASSET_CON_EXT01_X.
The best cost calculated by the optimizer when using index (S_ASSET_CON_EXT01_X) on ASSET_ID is 1 . The best cost when using index (S_CONTACT_M12) on LAST_NAME,FST_NAME,PRIV_FLG is ... 18. Both cost take into account the first_rows_10 optimizer mode.
Is this a bug, or a "feature" ? If it is "feature", how could I "deaktivate" it at SQL statement level
Below is an excerpt of 10053 event trace file.
Index: S_ASSET_CON_EXT01_X
TABLE: S_ASSET_CON
RSC_CPU: 0 RSC_IO: 4
IX_SEL: 0.0000e+00 TB_SEL: 4.4555e-07
Skip scan: ss-sel 0 andv 1481513
ss cost 1481513
index io scan cost 0
Access path: index (scan)
Index: S_ASSET_CON_U1
TABLE: S_ASSET_CON
RSC_CPU: 0 RSC_IO: 4
IX_SEL: 4.4555e-07 TB_SEL: 4.4555e-07
BEST_CST: 1.00 PATH: 4 Degree: 1
....
SINGLE TABLE ACCESS PATH (First K Rows)
TABLE: S_CONTACT ORIG CDN: 16 ROUNDED CDN: 15 CMPTD CDN: 15
Access path: tsc Resc: 2 Resp: 2
.....
Access path: index (no sta/stp keys)
Index: S_CONTACT_M12
TABLE: S_CONTACT
RSC_CPU: 0 RSC_IO: 18
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
....
ORDER BY sort
First K Rows: switch to Amode plans
****** Recost for ORDER BY (using index) ************
First K Rows: switch to Kmode plans
Index: S_CONTACT_M12
TABLE: S_CONTACT
RSC_CPU: 0 RSC_IO: 18
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
BEST_CST: 5.00 PATH: 4 Degree: 1
.....
Any pointers, exeperiences , lessons learned ?
Best Regards. Milen
-- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer -- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 15 2006 - 08:30:51 CST
 |
 |