||Oracle Tips by Burleson
Once DBWR has been tuned, the DBA needs to look at disk contention.
Disk contention happens when one or more users attempt to read the
same disk at the same time, or in some cases, access a different
disk through the same controller path at the same time. Spreading
Oracle-related files across several platters or sets of
platters--the more the better--prevents this. The new RAID options
don’t relieve the DBA of file placement concerns. You should be sure
that the RAID volumes are properly set. I had one system where a
system administrator set up multiple RAID5 volumes using two disks
for each volume (a hint: the 5 is a meaningful number for RAID5).
The report in Source 13.17 can be used to monitor relative-fill I/O
efficiency--essentially, how many reads are being performed per
request as a percent. The more times the process has to access the
same datafile to get the same information, the less efficient the
datafile is. This could be caused by colocation of indexes and
tables, a poorly ordered table that is scanned frequently, or having
temporary or undo segments in with data or index segments.
SOURCE 13.17 File efficiency report.
REM NAME :FILE_EFF.SQL
REM PURPOSE :GENERATE FILE IO EFFICIENCIES REPORT
REM USE :FROM STATUS_REPORTS.COM
REM Limitations :MUST BE RUN FROM ORACLE DBA ACCOUNT
REM Date Modified By Reason For change
REM 10-JUL-1992 M. AULT INITIAL CREATE
REM 07-JUN-1993 M.AULT Added reads to writes, reformatted
REM 23-Jun-1997 M.Ault kcffio went away, rewrote to use
REM existing views/tables
SET PAGES 58 NEWPAGE 0
SET LINES 131
COLUMN eff FORMAT A6 HEADING '% Eff'
COLUMN rw FORMAT 9,999,999 HEADING 'Phys Block|read/writes'
COLUMN ts FORMAT A22 HEADING 'Tablespace Name'
COLUMN name FORMAT A40 HEADING ‘File Name’
START title132 "FILE IO EFFICIENCY"
BREAK ON ts
DEFINE OUTPUT = 'rep_out/&db/file_io.lis'
FROM dba_data_files f, v$filestat v
See Code Depot
This is an excerpt by
Mike Ault’s book “Oracle
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download.