Re: High query cost but low execution time

From: Carlos Sierra <carlos.sierra.usa_at_gmail.com>
Date: Wed, 18 Apr 2012 06:47:52 -0500
Message-ID: <CAGzKQQe9Sxm1t3X-O9rWLaiAJGqySz_OuQy8RLDr7zBYsBqV8Q_at_mail.gmail.com>

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 <Amir.Hameed_at_xerox.com> 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_at_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

Received on Wed Apr 18 2012 - 06:47:52 CDT