Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: TKPROF

Re: TKPROF

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 1 Mar 2007 15:31:13 -0800
Message-ID: <1172791868.118079.277570@v33g2000cwv.googlegroups.com>


On Mar 1, 11:31 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Hi All,
>
> I am following up on my own post. I ran TKPROF against my large
> query, and, once of the inline functions returned the following
> results.
>
> While there was no disk I/O, there were a lot of buffer hits. Do you
> know of any way to cut these fetches down? I'm just a bit lost on no
> disk I/O, but it took 20 minutes to finish this query......
>
> Thanks!
>
> SELECT MAX(CO.ORDER_DATE)
> FROM
> CUSTOMER_ORDER CO,ORDER_LINE OL,PRODUCT P WHERE CO.ORDER_ID =
> OL.ORDER_ID
> AND OL.PRODUCT_ID = P.PRODUCT_ID AND P.SUBPRODUCT_ID = :b1 AND
> CO.CUSTOMER_ID = :b2
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0
> 0 0
> Execute 456 3.60 3.96 0 0
> 0 0
> Fetch 456 16.67 17.63 0 234384
> 0 456
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 913 20.27 21.59 0 234384
> 0 456

What version of Oracle?
What platform?
What is your database statistics collection parameter set to? Where is the explain plan for the posted query?

It is possible that the query plan is using an index that reads the same blocks over and over again. This would drive logical IO up while the physical IO may be non-existent. How many blocks are in the table?

It would be nice to know the PK of each table and the FK between them so as to be able to tell if all proper join conditions have been specified.

HTH -- Mark D Powell -- Received on Thu Mar 01 2007 - 17:31:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US