FAQ
Hi,
I have a statement which produces a high CBO cost but executes very
quickly, as shown below. Based on the execution timings, is the cost
figure a true representative of the response time? The DB version is
11.1.0.7.

Thanks

Amir



call count cpu elapsed disk query current
rows

------- ------ -------- ---------- ---------- ---------- ----------
----------

Parse 1 0.08 0.08 0 10 0


Execute 1 0.00 0.00 0 0 0


Fetch 54 0.82 0.79 0 18922 0
788

------- ------ -------- ---------- ---------- ---------- ----------
----------

total 56 0.90 0.87 0 18932 0
788



Rows Row Source Operation

------- ---------------------------------------------------

788 HASH GROUP BY (cr�017 pr=0 pw=0 time=7 us costs709
size08608 card224)

2633 NESTED LOOPS (cr932 pr=0 pw=0 timeW2 us costs403
size08608 card224)

2633 NESTED LOOPS (cr593 pr=0 pw=0 timeI5 us costY169
size37920 card224)

2633 NESTED LOOPS (cr�36 pr=0 pw=0 time46 us cost0699
sizeh2752 card224)

2633 NESTED LOOPS (cr79 pr=0 pw=0 time6 us cost 19
size@1212 card329)

1 NESTED LOOPS (cr pr=0 pw=0 time=0 us cost size
card=1)

1 VIEW VW_SQ_1 (cr pr=0 pw=0 time=0 us cost=8 size=4
card=1)

1 HASH UNIQUE (cr pr=0 pw=0 time=0 us)

1 HASH JOIN (cr pr=0 pw=0 time=0 us cost=8 size5
card=1)

1 TABLE ACCESS BY INDEX ROWID XMAF_GEN_INT_PARAMS (cr=2
pr=0 pw=0 time=0 us cost=2 size' card=1)

1 INDEX RANGE SCAN PG_IDX1 (cr=1 pr=0 pw=0 time=0 us
cost=1 size=0 card=1)(object id 4486804)

215 TABLE ACCESS FULL MTL_PARAMETERS (cr pr=0 pw=0
time=4 us cost=5 size20 card!5)

1 TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS (cr=2 pr=0
pw=0 time=0 us cost=1 size=8 card=1)

1 INDEX UNIQUE SCAN MTL_PARAMETERS_U1 (cr=1 pr=0 pw=0
time=0 us cost=0 size=0 card=1)(object id 7243)

2633 TABLE ACCESS BY INDEX ROWID MTL_ONHAND_QUANTITIES_DETAIL
(cr59 pr=0 pw=0 time1 us cost 10 size"9264 card3

29)

2633 INDEX RANGE SCAN MTL_ONHAND_QUANTITIES_N8 (cr pr=0
pw=0 time us costV size=0 card329)(object id 3695328)

2633 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cru57
pr=0 pw=0 time=0 us cost=2 size card=1)

2633 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (crR18 pr=0 pw=0
time=0 us cost=1 size=0 card=1)(object id 7343)

2633 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cru57 pr=0
pw=0 time=0 us cost=2 size2 card=1)

2633 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (crR18 pr=0 pw=0
time=0 us cost=1 size=0 card=1)(object id 7343)

Search Discussions

  • Kellyn Pot'vin at Apr 16, 2012 at 7:05 pm
    No, the cost is not a true representative of time and cost is not *everything*.  If no one is complaining and you are happy with the performance, I would not concern myself with the cost that is being reported.Â
    Thanks!

    Â
    Kellyn Pot'Vin
    Senior Technical Consultant
    Enkitec
    DBAKevlar.com


    ________________________________
    From: "Hameed, Amir" <Amir.Hameed@xerox.com>
    To: oracle-l@freelists.org
    Sent: Monday, April 16, 2012 12:54 PM
    Subject: High query cost but low execution time

    Hi,
    I have a statement which produces a high CBO cost but executes very
    quickly, as shown below. Based on the execution timings, is the cost
    figure a true representative of the response time? The DB version is
    11.1.0.7.

    Thanks

    Amir



    call    count      cpu    elapsed      disk      query    current
    rows

    ------- ------  -------- ---------- ---------- ---------- ----------
    ----------

    Parse        1      0.08      0.08          0        10          0


    Execute      1      0.00      0.00          0          0          0


    Fetch      54      0.82      0.79          0      18922          0
    788

    ------- ------  -------- ---------- ---------- ---------- ----------
    ----------

    total      56      0.90      0.87          0      18932          0
    788



    Rows    Row Source Operation

    -------  ---------------------------------------------------

        788  HASH GROUP BY (cr�017 pr=0 pw=0 time=7 us costs709
    size08608 card224)

      2633  NESTED LOOPS  (cr932 pr=0 pw=0 timeW2 us costs403
    size08608 card224)

      2633    NESTED LOOPS  (cr593 pr=0 pw=0 timeI5 us costY169
    size37920 card224)

      2633    NESTED LOOPS  (cr�36 pr=0 pw=0 time46 us cost0699
    sizeh2752 card224)

      2633      NESTED LOOPS  (cr79 pr=0 pw=0 time6 us cost 19
    size@1212 card329)

          1      NESTED LOOPS  (cr  pr=0 pw=0 time=0 us cost size
    card=1)

          1        VIEW  VW_SQ_1 (cr pr=0 pw=0 time=0 us cost=8 size=4
    card=1)

          1        HASH UNIQUE (cr pr=0 pw=0 time=0 us)

          1          HASH JOIN  (cr pr=0 pw=0 time=0 us cost=8 size5
    card=1)

          1          TABLE ACCESS BY INDEX ROWID XMAF_GEN_INT_PARAMS (cr=2
    pr=0 pw=0 time=0 us cost=2 size' card=1)

          1            INDEX RANGE SCAN PG_IDX1 (cr=1 pr=0 pw=0 time=0 us
    cost=1 size=0 card=1)(object id 4486804)

        215          TABLE ACCESS FULL MTL_PARAMETERS (cr pr=0 pw=0
    time=4 us cost=5 size20 card!5)

          1        TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS (cr=2 pr=0
    pw=0 time=0 us cost=1 size=8 card=1)

          1        INDEX UNIQUE SCAN MTL_PARAMETERS_U1 (cr=1 pr=0 pw=0
    time=0 us cost=0 size=0 card=1)(object id 7243)

      2633      TABLE ACCESS BY INDEX ROWID MTL_ONHAND_QUANTITIES_DETAIL
    (cr59 pr=0 pw=0 time1 us cost 10 size"9264 card3

    29)

      2633        INDEX RANGE SCAN MTL_ONHAND_QUANTITIES_N8 (cr pr=0
    pw=0 time us costV size=0 card329)(object id 3695328)

      2633      TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cru57
    pr=0 pw=0 time=0 us cost=2 size  card=1)

      2633      INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (crR18 pr=0 pw=0
    time=0 us cost=1 size=0 card=1)(object id 7343)

      2633    TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cru57 pr=0
    pw=0 time=0 us cost=2 size2 card=1)

      2633      INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (crR18 pr=0 pw=0
    time=0 us cost=1 size=0 card=1)(object id 7343)
  • John Clarke at Apr 16, 2012 at 7:20 pm
    
    Þ{­„§¥—)ÿ¢w¦¢·©¢)í¶‰š‘ëa¢è!ŠËajÖÚ±çhžØ^¶Jk¡ú.¶›­¶ª¹êòméÞ~+^uúèšz-…«âž hv†§ÈòþØ^v+$žé›z»­í"©e¶«Šm²š-³ÿÒ¢Ø^zwÿºÇ«¥êߢ¹šÇ¢²X¤z\­¡·yǧµ«,ºh§©î™·«¡ûaŠx,ýÊ'rêëzw2ü#Ô±«n­«b¢ÞµÏÿ!ùèê¶›–‡$±ªÞnçßz·Ê‹¦Šm±çµóÝö¦¬‰Æ¥ ë(­«.nÇ­¡ûaŠÏڝÛaŠÉšÊ)í®‡nqëÚ®&­Š‰ìŠz®z¼©z·è®f§qæÚ±çhž0ªèœ’\²¢çÚ¦¬‰Æ¥ çè®Ø^±ë‚g§¶Ïÿ&ˆgº&ü§¥—)Ï¢ßïŠäzYrŸúh¶ø§ÿ)šŠ_Ü¢oæj)m£ù–\§þš-¾)ÿÊf¢—÷(›ÿÑz™rý:ÿþG¥—)ÿ¦‹oŠò™¨¥ýÊ&þf¢–Úÿ‘éeÊé¢ÛâŸü¦j)ÿr‰ÿÿù–\§þš-¾)ÿÊf¢—÷(›ùšŠ[hþG¥—)ÿ¦‹oŠò™¨¥ýÊ&ÿðÚµïÌ¢õè
    kÛMvןôßþ:ÿN4Ñ:ÿü ¢¯ñÚ™çÿ«£Ü¢oæj)m£ð&Š¿Çjgžwü^®Œÿr‰ÿÿð&Š¿Çjgžwü^®Œÿr‰ÿ™¨¥¶Àš*ÿ©žyßñzº1ýÊ&ÿÿÿ¢¶œ•ïåýúÞzX¬¶Ïè®æj)m£ú+iÉ^þ_߭祊ËlþŠàÿÿè­§%{ùÿ~·ž–+-³ú+ƒùšŠ[hþŠÚrWÿ—÷ëyéb²Ûÿ¢¸ÿý+›ç-ýÿ(!ªç«ÉÊ,µ»­–ŒÅç.¶*'¶)ž6í…ç(²Ø¬ž‹Z¶»ž­êkzǧµ«b½ê¶)žjw\¢Ëb²z-ýëÞ¯+aŠxÿü‡ç¢‰ÞŠÇ(š™ZŠx§©ÝÊ‹š­èZ¦œ°ŠØm…ê^­ú+™©Ü{ò0¢é]ž‹\¢w®y²±é_Â+a¶œ¢Ëm…«b±·¢ž
    Þ¦ŠíyßÓ…©ä³òž–\§>‹ÿV)Òzx¨­7œ†xœjP¨žË¥µ©íy"µçž¾V«ýÊ&ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿº&ÿñÚ™çü ¢¯ÿÀš*ÿ©žyßñzº1ýÊ&þf¢–Úÿh«üv¦yçÿÅêèÇ÷(›ÿÓ£ú+iÉ^þ_߭祊ËlþŠàþf¢–Úÿ¢¶œ•ïåýúÞzX¬¶Ïè®Òz{ÿ2‰Ýk/À¦¸¥×¯öÓ]µÛþx<Ä®n7œ·ñâ‚®z¼œ¢Ë[ºÙhÁì^rëb¢{b™áâüˆZ½æ¬µ«^™éíÂœ†šèv籨b‚9Ê,µ»­{œº×¬½êòªèœ’\ÿjË!£ ÛzZ0ü¬yÚ'¶žÅç.¶*'¶)¢ž ÿŠËayÊ,µø º·š¶»ž­êkzǧµ«b½ê¶«zÊhžÇ­ŠgÿNƒ÷«²*'ŠÍuÿ_ôÿ¿Ó…©ä° ¢­Æ¥•Ê.ž×)¹éZ¦Çv+$ªç«ÉË«­éí®Œ,ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÏj»×OôóOôóMtÓA1yË­{]ÿÓMÿÓM4ÓA^µÈyãOüÛOû÷M|÷m´ïÏÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿûhµ©yëOýÓOüïM|÷}´ïÏ£ £¨º·:—«jبŸÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿûóÁÀHq‘9CÁc÷+ÿÿô׺k
  • Carlos Sierra at Apr 18, 2012 at 11:50 am
    Amir,
    I you want some analysis in this issue, I would suggest you install and use
    SQLTXPLAIN (MOS 215187.1). You may want to use XTRACT or XECUTE methods. As
    usual, I offer to review the output for a quick analysis.

    Cheers -- Carlos


    On Mon, Apr 16, 2012 at 1:54 PM, Hameed, Amir wrote:

    I have a statement which produces a high CBO cost but executes very
    quickly, as shown below. Based on the execution timings, is the cost figure
    a true representative of the response time? The DB version is 11.1.0.7.
    Thanks Amir call count cpu elapsed disk query
    current rows ------- ------ -------- ---------- ---------- ----------
    ---------- ---------- Parse 1 0.08 0.08 0
    10 0 Execute 1 0.00 0.00 0 0
    0 Fetch 54 0.82 0.79 0 18922
    0 ------- ------ -------- ---------- ---------- ---------- ----------
    ---------- total 56 0.90 0.87 0 18932
    0 Rows Row Source Operation -------
    --------------------------------------------------- 788 HASH GROUP BY
    (cr 017 pr=0 pw=0 time=7 us costs709 size 08608 card 224) 2633 NESTED
    LOOPS (cr 932 pr=0 pw=0 timeW2 us costs403 size 08608 card 224) 2633
    NESTED LOOPS (cr 593 pr=0 pw=0 timeI5 us costY169 size 37920 card 224)
    2633 NESTED LOOPS (cr 36 pr=0 pw=0 time46 us cost0699 sizeh2752 card
    224) 2633 NESTED LOOPS (cr 79 pr=0 pw=0 time 6 us cost 19 size@1212card 329) 1 NESTED LOOPS (cr pr=0 pw=0 time=0 us cost size
    card=1) 1 VIEW VW_SQ_1 (cr pr=0 pw=0 time=0 us cost=8 size=4
    card=1) 1 HASH UNIQUE (cr pr=0 pw=0 time=0 us) 1
    HASH JOIN (cr pr=0 pw=0 time=0 us cost=8 size5 card=1) 1
    TABLE ACCESS BY INDEX ROWID XMAF_GEN_INT_PARAMS (cr=2 pr=0 pw=0 time=0 us
    cost=2 size' card=1) 1 INDEX RANGE SCAN PG_IDX1 (cr=1 pr=0
    pw=0 time=0 us cost=1 size=0 card=1)(object id 4486804) 215
    TABLE ACCESS FULL MTL_PARAMETERS (cr pr=0 pw=0 time=4 us cost=5 size 20
    card!5) 1 TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS (cr=2
    pr=0 pw=0 time=0 us cost=1 size=8 card=1) 1 INDEX UNIQUE SCAN
    MTL_PARAMETERS_U1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id
    7243) 2633 TABLE ACCESS BY INDEX ROWID
    MTL_ONHAND_QUANTITIES_DETAIL 59 pr=0 pw=0 time 1 us cost 10 size"9264 card
    2633 INDEX RANGE SCAN MTL_ONHAND_QUANTITIES_N8 (cr pr=0 pw=0
    time us costV size=0 card 329)(object id 3695328) 2633 TABLE
    ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cru57 pr=0 pw=0 time=0 us cost=2
    size card=1) 2633 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (crR18
    pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 7343) 2633 TABLE
    ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cru57 pr=0 pw=0 time=0 us cost=2
    size2 card=1) 2633 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (crR18
    pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 7343)
    http://www.freelists.org/webpage/oracle-l

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 16, '12 at 6:55p
activeApr 18, '12 at 11:50a
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase