Thanks for the question, Steve.
Asked: September 20, 2016 - 3:17 am UTC
Last updated: September 20, 2016 - 5:17 pm UTC
Version: 11.2.0.4.0 - 64 bit Enterprise Edition
Viewed 1000+ times
You Asked
We have two Oracle database instances X & Y with the same parameter / configuration. There is a table "bigtable" which has around 2 Million rows in both the databases.
In a query, if the "bigtable" table is full scanned, we observe the below:
1) X & Y both use direct path reads (99% of the I/O time)
2) query on X takes 10x times more time to execute than on Y
3) X shows more LIOs (3 Million approx) and almost the same number of PIOs. Y shows 10x times less LIOs (800K approx) and almost negligible PIOs. Y takes 1 second, while X takes 40 seconds.
4) X & Y both has the same execution plan (FTS on "Big Table")
My Questions:
1) If "direct path reads" read directly from the disk, why so much LIOs (buffer gets) are being shown?
2) What could be the reason of Y being fast despite having slightly more cardinality on the table? Which factors might play a role in faster direct path reads?
and Connor said...
We need to see some wait statistics, ie
exec dbms_monitor.session_trace_enable(waits=>true)
select * from ...
exec dbms_monitor.session_trace_disable
and then look in the trace file (both raw and formatted) to see what kind of delays you are encountering.
if you get stuck, add them in here using the [code] tags and we'll take a look
Is this answer out of date? If it is, please let us know via a Comment