Finding the reasons for excessive logical IOs | Tanel Poder Consulting

[giscus[bot]]

Finding the reasons for excessive logical IOs | Tanel Poder Consulting

There’s another interesting thread going on in Oracle-L, about understanding logical IOs and drilling down into their reasons. Of course sometimes (or rather usually) the excessive logical IOs come from a bad execution plan (when a nested loop loops over lots of datablocks again and again or a wrong index is used for driving a query etc), but sometimes the excessive LIOs are caused by some internal issues, like space management etc.
A convenient tool I use for reporting logical IO reasons is (again) my Snapper! It has an option “b” for reporting Buffer get reasons or as I use below – option “a” shows All information Snapper can show.
There are couple of gotchas though which make this approach imperfect:
The X$ tables Snapper uses for LIO reason reporting contain instance-wide counters, not specific to a single testing session. Thus you either need to be the single user in your database when experimenting and even then the background activity may increment some counters while you are testing too. I have sometimes suspended all other processes (kill -STOP and kill -CONT to resume) or used Flash Freeze (oradebug ffbegin and ffresumeinst) to hang the whole instance that there would be no other activity going on. These buffer get reason counters are not maintained properly in Oracle 11g, probably due an optimization effort and some changes for faster pinning of buffer cache buffers (there’s a parameter called _fastpin_enable which is set to 1 in 11g and it enables so called fastpath buffer gets. If you see v$sesstat statistics such “consistent gets from cache (fastpath) or “db block gets from cache (fastpath)” being inremented, then fastpath buffer gets/pins are used. Note that I do have a script which works also on 11g but I’ll write about that one some time in the future :) Anyway, if you are testing in an environment exclusively used by you, on Oracle 10.2 or lower, then you can run snapper with the gather=a option to report a bunch instance-level statistics in addition to the standard session-level stats:
BUFG – Buffer get reasons (both consistent and current mode gets) LATG – Latch gets (both willing to wait and immediate gets) ENQG – Enqueue gets Here’s an example, prepare for long output:

• Linux, Oracle, SQL performance tuning and troubleshooting - consulting & training.

https://tanelpoder.com/2009/11/19/finding-the-reasons-for-excessive-logical-ios/