Skip to Main Content
  • Questions

  • How to identify the total number of distinct blocks (LIO) read by a particular SQL? Is it possible at all?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andy.

Asked: April 30, 2017 - 7:35 pm UTC

Last updated: May 06, 2017 - 2:20 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi,

There are various ways to easily identify the LIO for SQL execution as a primary measure for performance analysis.

As many of the index and table blocks are usually read multiple times over and over again for SQL execution, is there a way to identify the number of distinct blocks read by individual or multiple SQL executions?

Or in other words: Could we identify the sum of data space (individual blocks) scanned for SQL executions e.g. to judge how much space a particular SQL would use in the buffer cache (presumed no other query accessing that data) for fully cached execution?

and we said...

Unfortunately, there is no way to determine the number of physical blocks scanned for a SQL execution, as we do not record the individual blocks read but just the number of reads.

Therefore it's possible that all of those reads were of the same block or completely different blocks entirely.

However, if you are looking for some guidance on sizing the buffer cache you could consider using the V$DB_CACHE_ADVICE View. The view shows the predicts the suspected number of physical reads for each potential cache size.

More details on using the can be found in the Oracle Documentation https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA530

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

To Andy...

J. Laurindo Chiappa, May 08, 2017 - 7:03 pm UTC

Hi, Andy : Maria is 100% correct, by default the RDBMS does not register/mantain a full list of blocks read : see, if it read (say) one million blocks this is just a number, this result can be registered in a few bytes - no warm to Performance, no worries about space... In the other side, if a full list (not a quantity/sum) of I/Os would be mainteined in this same scenery, potentially it could demand an array of 1 million of elements, or a file with one million of rows, or a table wth 1 million of lines... Not a lighweight thing, for sure, so not done by default ...

If you Really, Positively, Absolutely want this information, yes it would be possible : for example, you can activate a TRACE for this SQL... With an active trace in place YES, every and each I/O (together with many other informations) will be logged in a trace file, later you could read this trace file and parse it for distinct block I/Os, Physical and Logical I/Os, timings or anything... If you do not know how to do SQL TRACEs, search here in asktom for it....

OF COURSE, no free lunch here : you WILL pay for it in the form of some overhead and (maybe a lot of) disk space usage, AND these PARSE operation would be done by hand, mostly ...

Regards,

J. Laurindo Chiappa

Andy Haack, May 23, 2017 - 7:50 am UTC

Hi Maria,
many thanks for the clarification. The background of the question was to predict the effect of tuning specific inefficient SQLs (e.g. unnecessarily doing large index scans) on buffer cache usage.
It's not possible to quantify it in that such detail apparently.
Thanks and regards,
Andy
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.