RE: ORDER BY and first_rows_10 madness

When we upgraded to 10g, we had to set the following optimizer settings in 
order to get acceptable performance.  Your mileage will vary:

Optimizer_index_cost_adj  = 30
Optimizer_features_enable = 8.1.7

Andrew W. Kerber
Oracle DBA
UMB
816-860-3921
andrew.kerber@xxxxxxx


"If at first you dont succeed, dont take up skydiving"

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Milen Kulev
Sent: Friday, December 15, 2006 8:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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,
....
110 columns from all tables
....
      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. 
-------------------------------------------------
SINGLE TABLE ACCESS PATH (First K Rows)
  TABLE: S_ASSET_CON     ORIG CDN: 2749192  ROUNDED CDN: 1  CMPTD CDN: 1
  Access path: tsc  Resc:  9654  Resp:  9654
  Access path: index (equal)
      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
***************************************
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
  Skip scan: ss-sel 1  andv 15
    ss cost 15
    index io scan cost 1
  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
  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-l



------------------------------------------------------------------------------
NOTICE:  This electronic mail message and any attached files are confidential.  
The information is exclusively for the use of the individual or entity intended 
as the recipient.  If you are not the intended recipient, any use, copying, 
printing, reviewing, retention, disclosure, distribution or forwarding of the 
message or any attached file is not authorized and is strictly prohibited.  If 
you have received this electronic mail message in error, please advise the 
sender by reply electronic mail immediately and permanently delete the original 
transmission, any attachments and any copies of this message from your computer 
system. Thank you.

==============================================================================

--
http://www.freelists.org/webpage/oracle-l


Other related posts: