Re: High query cost but low execution time

  • From: Kellyn Pot'vin <kellyn.potvin@xxxxxxxxx>
  • To: "Amir.Hameed@xxxxxxxxx" <Amir.Hameed@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 16 Apr 2012 12:03:46 -0700 (PDT)

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@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx 
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
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@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)


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

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2012