check out asktom. its up for today. if you use an 8i tkprof on a 9i trace file, you get bad cpu results.
are you doing that?
>
> From: Muqthar Ahmed <Muqthar.Ahmed_at_decoratetoday.com>
> Date: 2003/10/28 Tue AM 11:04:24 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: Execution Plan is good but HIGH CPU
>
> Jared,
>
> The output is from TKPROF.
>
> Muqthar
>
> -----Original Message-----
> Sent: Tuesday, October 28, 2003 9:24 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Have you run SQLTRACE on this query?
>
> The detail in the trace file will show
> where the cpu is being consumed.
>
> There is insufficient data in the summary
> to reach any conclusion.
>
> Jared
>
> On Mon, 2003-10-27 at 21:24, Muqthar Ahmed wrote:
> > Hi,
> >
> > Execution plan looks good but the query is consuming 800 seconds CPU time........why?
> >
> > ********************************************************************************
> >
> > SELECT sampleavail, sample_cost_amount, sample_sale_amount,
> > discount_room, discount_case, discount_half_case, allow_cut,
> > retail_cut_amount, cost_cut_amount, gp_room
> > from tbljnwpbookvendortype t1, tbljnwpbookvendor t2
> > where t1.jnwpbvid = t2.jnwpbvid
> > and t2.prsuid = :b3
> > and t2.wpbkid = :b2
> > and t1.wpptid = :b1
> >
> > call count cpu elapsed disk query current rows
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> > Parse 1 0.00 0.00 0 0 0 0
> > Execute 5618 0.63 0.58 0 0 0 0
> > Fetch 5617 800.05 782.07 0 1409683 0 4187
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> > total 11236 800.68 782.66 0 1409683 0 4187
> >
> > Misses in library cache during parse: 0
> > Optimizer goal: CHOOSE
> > Parsing user id: 109 (DDTBL) (recursive depth: 1)
> >
> > Rows Execution Plan
> > ------- ---------------------------------------------------
> > 0 SELECT STATEMENT GOAL: CHOOSE
> > 0 NESTED LOOPS
> > 0 TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDOR'
> > 0 INDEX (UNIQUE SCAN) OF 'TBLJNWPBOOKVENDOR_UQ1' (UNIQUE)
> > 0 TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDORTYPE'
> > 0 INDEX (RANGE SCAN) OF 'TBLJNWPBOOKVENDORTYPE_IDX1'
> > (NON-UNIQUE)
> >
> > ********************************************************************************
> >
> > Muqthar Ahmed
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Muqthar Ahmed
> > INET: Muqthar.Ahmed_at_decoratetoday.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Muqthar Ahmed
> INET: Muqthar.Ahmed_at_decoratetoday.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <ryan_oracle_at_cox.net
INET: ryan_oracle_at_cox.net
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Oct 28 2003 - 10:34:24 CST
