High query cost but low execution time

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Mon, 16 Apr 2012 14:54:15 -0400
Message-ID: <304F58144267C5439E733532ABC9A3A114B0091F_at_USA0300MS02.na.xerox.net>

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 0

Execute 1 0.00 0.00 0 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_at_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)

--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 16 2012 - 13:54:15 CDT