Lothar,
Some extra CPU for the chain management is inevitable; though the difference is
rather dramatic so Stefan's comments are also relevant. It's also possible that
"the same plan" may also have done something different with predicates that
turned an access predicate into a filter predicate which could result in the
same number of blocks accessed in an index range scan (say) but a much larger
amount of work per index entry to filter out entries.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Lothar Flatz [l.flatz@xxxxxxxxxx]
Sent: 02 May 2016 16:46
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: SQL Tuning
Hi Jonathan,
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Niall Litchfield
[niall.litchfield@xxxxxxxxx<mailto:niall.litchfield@xxxxxxxxx>]
Sent: 02 May 2016 09:51
To: Jack van Zanen
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxx<mailto:jack@xxxxxxxxxxxx>> 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
--
