Re: SQL Tuning

The "slow" query does 30k disk accesses, the "fast" one 0. If the plans really are identical then you've likely got your explanation right there. I would personally want to doublecheck the summary wait information that TKPROF can produce as well looks like you've got about 60s of wait time which would equate to an average i/o time of 2ms which is actually pretty good (for disk).

On Mon, May 2, 2016 at 5:14 AM, Jack van Zanen <jack_at_vanzanen.com> wrote:

> Hi All,
>
>
> I have two identical databases as far as versions, datasize OS etc is
> concerned and have a query that produces an identical execution plan.
> However this part of the 10046 trace is significantly different.
>
> The slow query is on test (first listing) and is severely constricted in
> its memory so my explanation would be that the tables involved are actually
> in the buffer cache in prod (second listing) where the sga is much larger
> and therefore no disk I/O is required.
>
> I will be having a look at the buffer cache next to check what is in there
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.01 0 0 0
> 0
> Execute 1 0.01 0.22 0 0 0
> 0
> Fetch 1 3.74 65.97 30927 68453 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 3.76 66.21 30927 68453 0
> 0
>
>
>
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.02 0 8 0
> 0
> Execute 1 0.01 0.01 0 83 0
> 0
> Fetch 1 0.32 0.53 0 68507 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 0.34 0.57 0 68598 0
> 0
>
>
> Jack van Zanen
>
> -------------------------
> This e-mail and any attachments may contain confidential material for the
> sole use of the intended recipient. If you are not the intended recipient,
> please be aware that any disclosure, copying, distribution or use of this
> e-mail or any attachment is prohibited. If you have received this e-mail in
> error, please contact the sender and delete all copies.
> Thank you for your cooperation
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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