An employee lookup can run twice and return the same answer while doing different amounts of disk work. Understanding that difference starts with the database block, the working copy in memory, and the version of the data the query needs.
Oracle DBA Lesson 8 Part 1 — How Oracle Reads Data
The block and its working copy
Oracle stores table and index data in database blocks. A block can contain several rows. A buffer is a slot holding a working copy of a block in memory. The database buffer cache is shared instance memory in the SGA, in RAM. Data files are physical files on persistent storage, shown as disk in this lesson.
For an ordinary buffered read, the server process searches the cache for a usable block. On a cache hit, it reads the cached buffer. That is a logical read. On a cache miss, it fetches the block from its data file into memory, a physical read, and then reads the buffered copy logically. Both activities can be involved in accessing the same block.
Caching can save later physical reads, while repeated accesses still add logical reads. Oracle may request the same block several times. A block request is different from a distinct block, a row returned, or a complete SQL execution. See memory architecture.
The version a query needs
Under ordinary READ COMMITTED isolation, a query in a separate reading session sees committed data consistent with the time the query began. If another session changes a block after that point, the current cached block may contain a newer version than the reader needs.
Undo preserves information about earlier values. Oracle can copy the block and apply undo to reconstruct the required version. In the ordinary cached path, that consistent-read copy is held in the buffer cache in RAM. Undo itself resides in undo blocks. Those blocks may already be in memory, or Oracle may read them from undo data files on disk. A consistent read can use a suitable existing version. Reconstruction is performed when it is needed.
Queries also see their own session's changes. This lesson's version example concerns another session's update. Detailed transaction visibility, undo retention, and isolation choices belong to later lessons. See data concurrency and consistency.
Direct reads
Some operations, including some large scans, read into process-private memory and bypass the ordinary shared buffer-cache path. This is a direct read. The destination is still RAM. The source is physical storage. Which path is used depends on the operation and on database behavior. A physical-read total can include this activity as well as reads into the buffer cache.
Inspect read activity in your session
The following query joins the current session's statistic values to their names and selects the three measures discussed in the video:
SELECT n.name, m.value
FROM v$mystat m JOIN v$statname n
ON n.statistic# = m.statistic#
WHERE n.name IN (
'db block gets',
'consistent gets',
'physical reads')
ORDER BY n.name;V$MYSTAT reports statistics for the session executing the query. V$STATNAME supplies statistic names. Match by STATISTIC#. Use the names when selecting statistics, because statistic numbers can differ between releases. Keep the same session and connected database context for the comparison. See V$MYSTAT and V$STATNAME.
| Statistic | What it counts | How to interpret it |
|---|---|---|
consistent gets | Requests for a consistent block version | Work performed to read a suitable version. Repeated requests can revisit a block. |
db block gets | Requests for a current block version | Current-mode work, such as accessing a block to change a row. Other work can also contribute. |
physical reads | Data blocks read from disk | Includes reads into the cache, direct reads, and relevant process-private reads. Blocks and I/O requests are different units. |
These get counters include relevant direct-path activity. Oracle exposes more specific counters, such as consistent gets from cache, db block gets from cache, physical reads cache, and physical reads direct, when distinguishing paths matters. The ordinary buffered lookup explains the main path. The three totals provide an initial activity comparison. See statistics descriptions.
Worked comparison
Take a baseline, run a fully fetched employee lookup, sample again, repeat the lookup, and take a third sample in the same session:
SELECT employee_name
FROM employees
WHERE employee_id = 101;| Statistic | Baseline | After first lookup | After repeated lookup |
|---|---|---|---|
consistent gets | 100 | 104 | 108 |
db block gets | 0 | 0 | 0 |
physical reads | 20 | 22 | 22 |
Subtract the preceding sample from the following sample:
| Interval | Consistent requests | Current requests | Physical block reads |
|---|---|---|---|
| First lookup interval | 104 − 100 = 4 | 0 − 0 = 0 | 22 − 20 = 2 |
| Repeated lookup interval | 108 − 104 = 4 | 0 − 0 = 0 | 22 − 22 = 0 |
The first interval records four consistent requests and two physical block reads. The second records four more consistent requests and no additional physical reads. That pattern fits reuse of cached blocks. These totals alone cannot identify exactly which blocks were accessed, or establish that every count came from the lookup. The sampling query and other work in that session also contribute.
Another run may begin with the table already cached, access different table or index blocks, read undo, or use a different plan. Record what actually happened. A zero physical-read delta is an observed result for an interval, rather than a promise for every second execution.
For a slow query, connect read activity with elapsed time, execution-plan work, and relevant waits. A cache-hit percentage by itself is insufficient to choose a cache size or diagnose the slowest SQL. Detailed performance investigation belongs to later lessons.
Optional cache organizations include the DEFAULT, KEEP, and RECYCLE pools, and caches for supported nondefault block sizes. A KEEP pool is intended to encourage retention of selected objects. Capacity and workload still matter. Detailed cache placement and sizing are outside this part. See tuning the database buffer cache.
Recap
A cache hit reads a block already in the buffer cache. A cache miss fetches that block from its data file and then reads the buffer. Under READ COMMITTED, Oracle can apply undo so a query sees the committed version from when the query began. Compare consistent gets, db block gets, and physical reads by subtracting samples taken in the same session. In the hypothetical repeat, four more consistent requests and no new physical reads fit cached-block reuse for that interval. The sampling query also counts, and a zero physical-read delta is an observation for that interval, not a promise for the next run.
No comments:
Post a Comment