Re: SQL Tuning

what also puzzles me is the CPU time. About the same number of buffer gets, but 10 times more CPU. Can't remember where I have seen that before.

Regards

Lothar
On 02.05.2016 12:54, Jonathan Lewis wrote:
>
>
> Niall,
>
> Given there are only 68K consistent gets for 31K blocks read I think
> that some of the disk count must be from a tablescan or index fast
> full scan - which might make the average seem less wonderful. Can't
> argue with the "check the wait summary", though, for a quick check.
> Better still, the plan headed Rowsource Operations" should show the
> time, disk and CR buffer gets accumulated - so easy to see exactly
> where most of the time went.
>
> Regards
> Jonathan Lewis
>
>
> ------------------------------------------------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org]
> on behalf of Niall Litchfield [niall.litchfield_at_gmail.com]
> *Sent:* 02 May 2016 09:51
> *To:* Jack van Zanen
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: SQL Tuning
>
> Jack
>
> 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
> <mailto: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