Your explanation is correct..
My only concern is why query is using 68K blocks.. Look at opportunity to
tune it
Regards,
Harman
On Mon, May 2, 2016 at 9:44 AM, Jack van Zanen <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
