 |
|
Transient Disk Hot Spots
Oracle Database Tips by Donald Burleson |
In any Oracle database it is
not uncommon to see sporadic hot spots appear on disks as the I/O signature
changes. As you know, Oracle offers a wealth of data buffering tools that are
designed to keep Oracle data blocks within RAM and prevent disk I/O. These
techniques include table caching and separate data buffers. However, unless you
have your database fully cached in the data buffers, you will always experience
I/O activity.
I/O Patterns Within High-Update Tables
There is a special case of
disk I/O that occurs when a transaction table experiences high-volume INSERT and
UPDATE operations. For example, consider an order processing system with 3,000
data entry operators constantly slamming orders into a large order table. Let's
further assume that at any given time, there are 200 transactions inserting into
this table. As we will see, a close inspection of the datafiles will reveal
several important characteristics within tables that have high-volume inserts:
Roving Hot Spots on Disk
To understand roving hot
spots on disk, we will give a simple example. Let's assume that our database has
a table named transaction that has 200 data entry operators constantly adding
rows. The transaction table is defined with 200 freelists, and the table is
gathering free blocks by raising the high-water mark for the Oracle table. This
example assumes that there are not any free blocks on the freelists for the
table, such as the case where the APPEND hint is used with the INSERT
statements. Since we know that Oracle bumps the high-water mark for a table in
increments of five blocks, our 200 concurrent inserts would generate intensive
SQL INSERT activity that is isolated to a set of 1,000 blocks within the table.
Because each of the INSERT transactions must request a
separate free block from the transaction table to insert their new transactions,
Oracle will grab free blocks (five at a time) from sequential free space in the
tablespace. These free blocks are likely to be contiguous blocks on the disk.
Since these contiguous blocks are likely to reside on the same disk cylinder, it
is likely that this disk would experience I/O contention at the end of the
table.
As our data entry
operators continue to hand key entries into the transaction table, we see the
hot spot moving along the disk as new cylinders are accessed by the table (see
Figure 8-2).
Figure 8-40: The roving hot spot within a transaction
table
As we can see, the hot spot will travel through the
tablespaces as Oracle blocks become full and the Oracle tables expand. So long
as all of the SQL INSERT statements add blocks into a new data block, the hot
spot will travel cleanly across the disks.
However, what happens
after a transaction purge job is run? When older transactions are deleted from
the table, blocks become free and are added to one of the 200 freelists for the
table. As the freelists are loaded with newly empty blocks from the purge job,
the hot spot will travel backward in the table, returning to the area of the
table where the purge job removed the rows, as shown in Figure 8-3.
Figure 8-41: The hot spot travels back in the table
Oracle tablespaces that contain high-volume tables often
experience the ?roving hot spot? phenomenon. This is especially true for tables
where rows are inserted and deleted on a date-time basis, such as a fact table
within an Oracle data warehouse.
In standard business applications, we see this type of
time-based entry into Oracle tables. For example, orders for goods are inserted
in a time-based sequence, data warehouses load their data in a time sequence,
and most every online system adds and purges rows based on a time sequence.
So, given that these
roving hot spots will appear, what can you do to manage the activity? The trick
to managing roving hot spots is to ensure that the free blocks always reside on
adjacent cylinders. When Oracle data blocks are re-added to the freelists, we
cannot guarantee that they will be close together on the disk, and this
condition can create a ?disk thrashing? situation. Disk thrashing occurs when
free blocks are located on widely distant cylinders on the disk (see Figure
8-4). As our 200 tasks compete for free blocks, the read-write heads thrash back
and forth attempting to meet the needs of each transaction. The time required
for a disk's read-write head to move between cylinders is called 'seek? delay,
and seek delay is the single most time-consuming factor in disk access.
Figure 8-42: High seek delay on a disk
There are several techniques that can be done to remedy
this problem:
-
Segregate objects Identify
all tables with high insert activity and segregate them into a separate
tablespace.
-
Use fresh data blocks Ensure that all new inserts
go onto new data blocks at the end of the table by using the APPEND hint in
all INSERT statements.
-
Reorganize tables Reorganize the table after purge
jobs are run to reclaim the freed blocks onto the end of the table.
In addition to roving hot spots, these highly active
Oracle tables will also manifest themselves as sparse tables.
The Sparse Table Phenomenon
in RAC
Sparse tables generally
occur in RAC when a highly active table is defined with many freelists, and the table
has heavy INSERT and DELETE activity. In a sparse table, the table will appear
to have thousands of free blocks, yet the table will continue to extend, and the
table will behave as if Oracle does not have any free data blocks. A sparse
table in a data warehouse can consume a huge amount of unnecessary storage,
consuming many gigabytes of new storage while the table appears to have lots of
free space. Remember, when you have multiple freelists, the freelists are
independent and Oracle cannot share freelist blocks. An INSERT task will only
attach to one freelist, and it is only able to use free blocks that are attached
to that freelist.
The cause of a sparse
table in Real Application Clusters is a lack of balance between INSERT and DELETE activity. In our example,
we have three freelists defined for the table, and each freelist gets new blocks
in five-block chunks as the table expands.
As we can see, only one of the three freelists is
populated with the free blocks! The other two freelists remain empty, and must
request blocks by increasing the high-water mark for the table. This causes the
table to extend, even though it may be largely empty. Extension occurs because
each freelist is unaware of the contents of other freelists inside the segment
header.
The remedy, of course, is
to parallelize the RAC purge job into three simultaneous DELETE tasks. By
parallelizing the purge, all three freelists are evenly populated with newly
empty blocks.
Of course, we must set the number of freelists to the
number of simultaneous INSERT or UPDATE operations, so we cannot reduce the
number of freelists without introducing segment header contention.
So, what can we do to identify sparse tables
in RAC? The
following query selects tables that contain multiple freelists, with more than
one extent, where there is excessive free space.
To find tables with excessive free blocks on a freelist,
we must compute the amount of data used within the table. First, we calculate
the average row length (avg_row_len) in the data dictionary view and the
number of rows (num_rows) by performing a table analyze (that is,
analyze table xxx estimate statistics). When we multiply the number of
rows in the table by the average row length, we approximate the actual consumed
size of the data within the table. We then compare this value with the actual
number of allocated bytes in the table.
The idea is that a sparse table will have far more
allocated space than consumed space because a single freelist contains a
disproportional number of free blocks. Here is a script called sparse.sql
that generates this report.
sparse.sql
column c1 heading "Tablespace";
column c2 heading "Owner";
column c3 heading "Table";
column c4 heading "Bytes M" format 9,999;
column c5 heading "Extents" format 999;
column c7 heading "Empty M" format 9,999;
column c6 heading "Blocks M" format 9,999;
column c8 heading "NEXT M" format 999;
column c9 heading "Row space M" format 9,999;
column c10 heading "Pct Full" format .99;
select
substr(dt.table_name,1,10) c3,
ds.extents c5,
ds.bytes/1048576 c4,
dt.next_extent/1048576 c8,
(dt.empty_blocks*4096)/1048576 c7,
(avg_row_len*num_rows)/1048576 c9,
(ds.blocks*4096)/1048576 c6,
(avg_row_len*num_rows)/(ds.blocks*4096) c10
from sys.dba_segments ds,
sys.dba_tables dt
where ds.tablespace_name = dt.tablespace_name
and ds.owner = dt.owner
and ds.segment_name = dt.table_name
and dt.freelists > 1
and ds.extents > 1
and dt.owner not in ('SYS','SYSTEM')
and (avg_row_len*num_rows)/1048576 > 50
and ds.bytes/1048576 > 20
order by c10;
Next is the output from sparse.sql. This will identify
tables that have lots of free space within their existing extents. If any of
these tables extend before using up their free blocks, we can assume that the
table has a freelist imbalance. The remedy for this imbalance is to reorganize
the table.
Table Extents Bytes M NEXT M Empty M Row space M
Blocks M Pct Full
---------- ------- ------- ------ ------- ----------- -------- --------
TST03 65 1,241 20 14 118 1,241 .10
LIKE 3 148 49 24 76 148 .52
VBRK 2 124 4 0 69 124 .56
STXL 35 1,775 40 7 1,021 1,775 .57
VBAK 5 234 49 0 136 234 .58
KOCLU 27 1,889 49 27 1,144 1,889 .61
VBUP 2 866 49 0 570 866 .66
VBUK 2 147 28 0 103 147 .70
VBAP 46 4,314 50 0 3,034 4,314 .70
NASTY 3 137 10 2 97 137 .71
VBPA 5 582 32 0 426 582 .73
LIME 7 2,350 49 0 1,735 2,350 .74
VBRP 45 2,675 49 0 2,029 2,675 .76
WFPRC 30 123 10 7 95 123 .77
VLPMA 16 575 25 23 444 575 .77
EXCDOC 18 432 20 13 337 432 .78
VRPMA 24 700 20 7 549 700 .78
VBEP 4 2,134 49 49 1,698 2,134 .80
Now that we understand the general nature of I/O activity
in an Oracle database, let's look at some global solutions for placing Oracle
datafiles onto our disk devices.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
and include the URL for the page.
