Database Reference
In-Depth Information
Logical Reads
When data is read from physical storage (disk), it is placed into the buffer cache before filtering through the rows that
match the criteria specified in the WHERE clause. Rows thus read are retained in the buffer, assuming other sessions
executing similar queries may require the same data, reducing physical I/O. Queries not tuned to perform minimal
I/O operations will retrieve a significantly larger number of rows, causing Oracle to traverse through the various
rows, filtering what is not required instead of directly accessing rows that match. Such operations cause a significant
amount of overhead and consume a large number of resources in the system.
Reading from buffer, or logical reads (or LIO operations), is cheaper compared to reading data from disk.
However, in Oracle's architecture, high LIOs are not cheap enough that they can be ignored because when Oracle
needs to read a row from buffer, it needs to place a shared lock on the row in buffer. To obtain a shared lock, Oracle
has to request a latch on the buffer (cache buffers chains latch) from the O/S. Latches are not available in abundance.
Often when a latch is requested, one is not immediately available because other processes are using them. When
a latch is requested and the latch is not available, the requesting process will go into a sleep mode and after a few
nanoseconds will wake up and request the latch again. This time it may or may not obtain the latch and may have to
sleep again. Latch contention involves CPU spins and context switches and may cause high CPU consumption on the
host if sessions access the same resource and data. When Oracle has to scan a large number of rows in the buffer to
retrieve only a few rows that meet the search criteria, this can prove costly.
SQLs that issue high logical read rates in comparison to the actual number of database rows processed are
possible candidates for SQL tuning efforts. Often the introduction of a new index or the creation of a more selective
index will reduce the number of blocks that must be examined to find the rows required. For example, we examine the
performance of the following query:
SELECT eusr_id,
us.usec_total_logins,
eu.eusr_role_cd,
c.comp_scac_code,
eu.eusr_login_name,
ul.usrli_id
FROM el_user eu,
company c,
user_login ul,
user_security us
WHERE ul.usrli_active_status_cd = 'Active'
AND ul.usrli_logged_in_eusr_id = eu.eusr_id
AND eu.eusr_comp_id = c.comp_id
AND eu.eusr_id = us.usec_eusr_id
ORDER BY c.comp_comp_type_cd,
c.comp_name,
eu.eusr_last_name;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.28 0.29 0 51 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 26.31 40.35 12866 6556373 0 87
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 26.59 40.64 12866 6556424 0 87
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 334 (MVALLATH)
 
Search WWH ::




Custom Search