A full table scan reads all rows from a table, and then filters out those rows that do not meet the selection criteria.
How a Full Table Scan Works
In a full table scan, the database sequentially reads every formatted block under the high water mark. The database reads each block only once.
The following graphic depicts a scan of a table segment, showing how the scan skips unformatted blocks below the high water mark.
Figure 4-2 - High Water Mark
Because the blocks are adjacent, the database can speed up the scan by making I/O calls larger than a single block, known as a multiblock read. The size of a read call ranges from one block to the number of blocks specified by the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.
For example, setting this parameter to 4 instructs the database to read up to 4 blocks in a single call. The algorithms for caching blocks during full table scans are complex. For example, the database caches blocks differently depending on whether tables are small or large.
Full TableScan Data Block Question:What happens to data blocks that are not read during a full-table scan?
The High Water Mark reflects the "last" block that ever contained data, so the only blocks that are not read are the blocks that are allocated to the table but never contained any data. In addition, this prevents a large full-table scan from flushing all of the
other object data blocks from the data buffer.
The size of the buffer is determined by the database administrator and for Oracle, separate buffers can be created for different tables. To maximize the use of buffers, perform a check on the buffer hit ratio.
The buffer hit ratio is the ratio of logical read requests to physical disk reads.
A logical read is a request from a program for a record, while a physical read is a request from a database for a record. The equation for finding the buffer hit ratio is:
Equation for buffer hit ratio
Equation: Hit Ratio = ((Logical Reads - Physical Reads) / Logical Reads)
In general, the buffer hit ratio is a function of the application size of the buffer pool. An application with a very large customer table is not likely to benefit from an increase in buffers because the I/O is widely distributed across tables.
Smaller applications will often see an improvement as the buffer size is increased.
The following query shows a typical buffer hit ratio
Method 1- calculating the buffer hit ratio.
buffer1.sql - displays the buffer hit ratio
PROMPT *****************************************************
PROMPT HIT RATIO SECTION
PROMPT *****************************************************
PROMPT
PROMPT =========================
PROMPT BUFFER HIT RATIO
PROMPT =========================
PROMPT (should be > 70,
else increase db_block_buffers in init.ora)
SELECT TRUNC((1-(sum(decode(name,'physical reads',value,0))/
(sum(decode(name,'db block gets',value,0))+
(sum(decode(name,'consistent gets',value,0)))))
)* 100) " buffer hit ratio "
FROM v$SYSSTAT;
Method 2- calculating the buffer hit ratio.
Buffer2.sql
PROMPT *****************************************************
PROMPT HIT RATIO SECTION
PROMPT *****************************************************
PROMPT
PROMPT =========================
PROMPT BUFFER HIT RATIO
PROMPT =========================
PROMPT (should be > 70,
else increase db_block_buffers in init.ora)
COLUMN " logical_reads " FORMAT 99,999,999,999
COLUMN " phys_reads " FORMAT 999,999,999
COLUMN " phy_writes " FORMAT 999,999,999
SELECT A.value + B.value "logical_reads",
C.value "phys_reads",
D.value "phy_writes",
ROUND(100 * ((A.value+B.value)-C.value) / (A.value+B.value))
" buffer hit ratio"
FROM V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D
WHERE
A.statistic# = 37
AND
B.statistic# = 38
AND
C.statistic# = 39
AND
D.statistic# = 40;
SQL> @buffer1
**********************************************************
Hit Ratio Section
**********************************************************
=========================
BUFFER HIT RATIO
=========================
(should be > 70,
else increase db_block_buffers in init.ora)
logical_reads phys_reads phy_writes BUFFER HIT RATIO
------------- ------------ ------------ ----------------
18,987,002 656,805 87,281 97
Be aware that the buffer hit ratio (as gathered from the V$ tables) measures the overall buffer hit ratio of the system since the Oracle instance was started.
Because the V$ tables keep their information forever, your current buffer hit ratio could be far worse than the 97 percent shown in the example.
To get a short-term buffer hit ratio (i.e., five minutes elapsed), use Oracle's bstat-estat utility, and find the buffer get in the report.txt file. Oracle also provides a utility for predicting the benefit from
adding more buffers .
You must also be aware of the relationship between the DB File multi-block Read Count.
Short-term buffer hit ratio
The buffer hit ratio will fluctuate wildly as new transactions enter the database. You must measure the buffer hit ratio in short increments to get an accurate measure of current activity.
In the next lesson, we will examine adding data buffers.