FAQ
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

Search Discussions

  • Kerber, Andrew at Dec 15, 2006 at 2:37 pm
    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_at_umb.com

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

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Milen Kulev
    Sent: Friday, December 15, 2006 8:31 AM
    To: oracle-l_at_freelists.org
    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
  • Allen, Brandon at Dec 15, 2006 at 3:39 pm
    Sorry, don't have time to read through all this thoroughly, but from a
    brief scan it looks like "_sort_elimination_cost_ratio"=5 might help.
    Check out Metalink 272202.1.

    Regards,
    Brandon

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Milen Kulev at Dec 18, 2006 at 11:44 am
    Hi Brandon,
    thank you for the hint
    ML Note:272202.1 discussed Bug bug 4570598, related to FIRST_ROWS hint/optimizer mode.
    FIRST_ROWS is as far as know some kind of "hybrid" between RBO und CBO -> many heuristic rules are used, and that is why the cost calculations in this mode are not always driving the decisions of CBO.
    Anyway, I have tried with
    "_sort_elimination_cost_ratio" 1, 10, 50, even 100, but I am getting the same (suboptimal) plan.

    Best Regards, Milen
    P.S. I further problem is that I can not use "_sort_elimination_cost_ratio" as hint (Oracle version is 9i, not 10G)

    --
    "Ein Herz für Kinder" - Ihre Spende hilft! Aktion: www.deutschlandsegelt.de
    Unser Dankeschön: Ihr Name auf dem Segel der 1. deutschen America's Cup-Yacht!
    --
    http://www.freelists.org/webpage/oracle-l
  • Milen Kulev at Dec 18, 2006 at 12:04 pm
    Hi Jonathan,
    thanks you for the execellent explanation;)
    I have found out, that _sort_elimination_cost_ratio is not influencing the CBO (in this very case) in no way. Perhaps this parameter exists only fix fix bug 4570598 when FIRST_ROWS mode is used...

    I have tried /*+ ALL_ROWS */ and it is working. I will create an outline
    for this statement.
    I have already tried to use hints /outlines :
    select /*+ leading(T12) (T12 S_ASSET_CON_EXT01_X) */
    ....
    or even
    select /*+ leading(T12) cardinality(T12 1) (T12 S_ASSET_CON_EXT01_X) */
    ...

    The problem with the outline approach is that depending on the values of ASSET_ID predicate (and despite the presence of outline ), the CBO is still
    switching to using suboptimal execution plan, perhaps because "_optim_peek_user_binds" = TRUE.

    I will try with /*+ ALL_ROWS */ and I hope that this time the execution plan will be stable.

    Best Regards. Milen

    Looking at the single table access path doesn't really help.

    For first_rows_K, the optimizer does one pass to work
    out the number of rows (N) the query should return, then it starts again (in
    Kmode) working on a strategy to get enough
    data at each step to get K/N of the data expected at that step.

    If the optimizer predicts a "large" number of rows,
    then K/N is small, and the cost of getting all rows
    at some step, sorting them, then selecting the first
    K can easily be much larger than using an index
    to find the first K in a different way.

    I have noticed some oddities where the optimizer's
    estimate of the number (and cost) of throwing away inappropriate rows whilst
    finding the first K by index is
    unrealistic.

    To bypass this at the statement level, just hint with
    /*+ all_rows */. There is a case for OLTP systems
    (especially across a web app server) that the userfacing
    app should run first_rows_K - where K is the
    typical arraysize used by the app server - but any
    reports should run all_rows either by hint, or by having
    a log-on trigger change the optimizer-mode.

    The _sort_elimination_cost_ratio is not really something
    you should fiddle with.

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com

    Author: Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    --
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 15, '06 at 2:30p
activeDec 18, '06 at 12:04p
posts5
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase