SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 207

The time element in the row source operation column of the plan statistics will not be available if TIME_STATISTICS
is not enabled. This is critical in determining the total time each execution step had taken to complete the operation.
Looking further at the statistics generated, the first question that probably arises is why there is 347,172 consistent
gets (column query in the output) and almost an equal number of physical reads (column disk in the output) to get
300 rows. This tells us that Oracle had to filter through 347,172 rows of data to finally arrive at a 300 rows result set.
During this process, Oracle also had to perform 346,671 disk reads, so almost every row read from the disk was loaded
into the buffer before building the result set of 300 rows.

What Are Consistent Gets?
Blocks are retrieved from physical disks or from memory buffers. The blocks retrieved by Oracle are consistent with a
given point in time or SCN (system change number). If such consistent blocks of data are not available in the buffer,
Oracle will reconstruct the data block either by reading the block from disk or in the case of RAC instances, requesting
the block from the current holder. Because these blocks are retrieved from buffer (logical), they are also called logical
I/O's (LIO). Similarly, when blocks are retrieved from disk, they are called physical I/O (PIO). In the preceding
example, blocks are retrieved from disk and then loaded into the buffer, so there are both PIOs and LIOs. The almost
equal number of PIOs and LIOs are because the query is being executed for the first time; subsequent execution of
the same query may only notice LIOs. Once again, because this is a GROUP BY operation, columns from these rows are
summed on pivot column(s) ( OL_W_ID, OL_D_ID, OL_NUMBER ).
Consistent read statistics are incremented either

1. During a full table scan, where for every block read, the consistent read value is increased

by 1, or

2. When data is accessed using indexes, in which case the consistent gets are incremented

based on the index height.

This is because an index scan touches at least two blocks. Therefore, when a single row is accessed, the index
scan takes more consistent gets. Although this is a true behavior, it does not occur all the time. In full table scan,
logical I/O is higher in general. The increment is higher in the case of indexes because for every read there are two
gets (index and the physical row). However, in the case of a full table scan that does not involve an index, it's only
reading the row.
Different statistics counters are updated based on the type of operation performed. Any of the following types of
statistics are LIOs:

• buffer gets

• consistent gets

• db block gets

•
Similarly, db block gets statistic counters are used to get the most current copy of the block. Based on Oracle
architecture, there could only be one current copy of a block in the buffer cache at any given time. The db block gets
statistic is incremented when a block is read for update and when the segment header blocks are accessed. Because
both consistent gets and db block gets are reading blocks from buffer, they are both logical reads. If both these
statistics are present, then LIO is the sum of db block gets and consistent gets . Retrieving from the memory has
always been less expensive compared to retrieving from disk. PIOs are generally more expensive because of the way
the data is stored on disks and how the data has to be found (seek time) before they are actually read (read time).
Comparing this behavior to an LIO operation, LIOs are less expensive because instead of scanning through various
tracks on storage, memory buffers are scanned, and reading through logical memory structures is much cheaper
compared to reading from storage.

buffer pinned count