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

490 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_COMP'
(UNIQUE) PARTITION: START=1 STOP=1
487 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'USEC_INDX1'(NON-UNIQUE)
The optimizer decides to use the new index USRL_INDX1 and reduces the number of rows retrieved. Now, if
another index is added to the EL_USER table, further improvement in the query can be obtained. Indexes that are not
selective do not improve query performance but can degrade DML performance. In RAC, unselective index blocks
may be subject to interinstance contention, increasing the frequency of cache transfers for indexes belonging to
INSERT -intensive tables.
Physical Reads
As illustrated in Figure 7-2 , seeking of data occurs from the outside to the inside of the disk. The disk head seeks to
find the data first. Once the sector and track where the data resides is found, the data is read. A typical disk drive
today has a minimum seek time of approximately 1 ms for seeking the next track and a maximum seek time of
approximately 11 ms for seeking the entire width of the disk. 1 A PIO involves all of these operations, making a PIO
very expensive.
Seek
Figure 7-2. Disk reads and seeks
Oracle has to consult with the O/S to access data from the buffer. Every time Oracle needs to access a row in the
buffer, there is a latch involved; a latch is a lock in memory. To obtain a latch, the session depends on the O/S. That
means every time a row is read from the buffer (memory), Oracle needs to request a latch, and a request for a latch is
a request for CPU. Getting a latch is not a straightforward task either. When an Oracle process needs a specific type of
latch (e.g., cache buffers chains latch\ ), the latch may not be available, which means that after the process spins
(for 2,000 times) trying to acquire the latch, it sleeps and tries again after x amount of seconds. This repeated activity
of requesting a latch and CPU goes on until such time when it is able to successfully get a latch. If there are many rows
in the buffer that need to be read, and every read requires a latch, which in turn requires CPU cycles, could mean high
CPU activity. Such frequent requests for latches make the operation really expensive.
The other value that catches one's attention is the physical reads or PIOs. PIO, as we have discussed earlier, is
even more expensive. PIOs can be tuned by examining the query and the underlying tables to ensure that there are
indexes and if the optimizer is using the correct path when generating the execution plan. Event 10053, discussed in
Chapter 6, could be helpful method to get this accomplished.
1 Murali Vallath, Oracle 10g RAC - Grid, Services and Clustering, Digital Press, 2007.