Maximizing Oracle 10g Block Space Usage
The RAM that is allocated to the data buffers will have to be
carefully managed until memory becomes cheap enough to cache the
entire database. Properly allocating tables and indexes
according to block size is a balancing act. If the data blocks
are set too large, valuable buffer space is wasted holding row
data that will never be referenced. If the blocks are set too
small, Oracle is forced to perform more disk I/O to satisfy a
query.
The following are some further general guidelines that can be
used for allocating data block sizes:
§
Segregate large-table full-table scans. Tables
subject to large-table, full-table scans will benefit from the
largest supported block size. They should be placed in a
tablespace with the largest block size.
§
Set
db_recycle_cache_size carefully. If
db_cache_size is not set to the
largest supported block size, the db_recycle_cache_size
parameter should be used. Instead, a db_32k_cache_size
, or whatever the max size is, should be created, and then
assign all tables and indexes subject to large-table, full-table
scans to the largest data buffer in the database.
The data dictionary will use the default block size. Make sure
that the dictionary, the SYSTEM tablespace for example, is
always fully cached in a data buffer pool. The block size, per
se, of the dictionary is less important than having enough RAM
in the SYSTEM tablespace buffer to fully cache all of the
dictionary blocks.
Finding Baselines
Oracle databases are always changing, and the databases that are
examined at 10:00 AM may be completely different than the
databases that exist at 3:00 PM. Does this mean that a broad
brush application of SSD is not valid?
When the performance of Oracle disk I/O is examined over
different time periods, regular signatures appear when the I/O
information is aggregated by hours of the day and day of the
week as shown in Figure 14.11.
Figure 14.11 –
Average
disk reads and writes by hour of the day
Most Oracle professionals will use Oracle9i STATSPACK or Oracle10g AWR information to gather these
baselines. Once the repeating I/O trends have been identified,
the DBA will be able to apply a broad brush to the use of SSD,
placing the fast I/O devices where they will do the most good.
I/O information can be captured at the file level and this can
give insight into the best data files to place on super fast
SSD. The following script extracts the physical read
information from the Oracle 10g
dba_hist_filestatxs
view:
break on begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
select
begin_interval_time,
filename,
phyrds
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
;
The sample output below shows a running total of physical reads
by datafile. The snapshots are collected
every half-hour. Starting from this script, the DBA could
easily add a WHERE clause criteria and create a unique
time-series exception report.
SQL> @reads
BEGIN_INTERVAL_TIME
FILENAME PHYRDS
------------------------- --------------------------------------
--------
24-FEB-04 11.00.32.000
PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF 164,700
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 26,082
E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF
472,008
E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF 1,794
E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA 2,123
24-FEB-04 11.30.18.296
PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF 167,809
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 26,248
E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 476,616
E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF 1,795
E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA 2,244
25-FEB-04 12.01.06.562
AM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF 169,940
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 26,946
E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 483,550
E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF 1,799
E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA 2,248
A little tweaking to the
reads.sql script and the DBA could report on physical
writes, read time, write time, single block reads, and a host of
other interesting metrics from the
dba_hist_filestatxs view.
SEE CODE DEPOT FOR FULL SCRIPTS
