"There are only
two tragedies in life: one is not getting what one wants, and
the other is getting it." - Oscar Wilde (1854-1900) |
Read
first then play the video:
PRF-VIDEO -Database
I/O Problem
Database I/O problem
Introduction
As a DBA, you are
responsible for monitoring and detecting I/O problems of your
organization"s database in case of performance problems. Your job"s
responsibilities dictate that you should at least be aware of the
following basic fundamental subjects:
Monitoring the Database I/O
problem
Monitoring the Checkpoint
process activities
Tuning the Checkpoint
process activities
Using the V$FILESTAT view
Using the V$SYSSTAT view
Using the V$SYSTEM_EVENT
view
Using the DBA_DATA_FILES
view
Setting the UNDO_MANAGEMENT
parameter
Monitoring the SQL
statement sorting
Distributing tablespaces on
different disks
Using the Redundant Array
of Inexpensive Disks (RAID)
Commands:
ALTER SYSTEM SET
undo_management=AUTO SCOPE=spfile
Hands-on
In this exercise you will learn how to: detect, monitor and fix the
database I/O problem, monitor and tune the checkpoint process
activities, set the undo_management to AUTO, and monitor the SQL
sorting on the disk space.
Connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
Avoid I/O contention
In order to reduce the I/O contention we should at least consider
the following steps:
01 -- Use Oracle to distribute the data files across multiple disks
evenly.
02 -- Use the Oracle partitioning.
03 -- Use the locally managed tablespace option, unless you have a
reason not to do so.
04 -- Use only the Redo Log files, controlfiles, and dump files on the
same disk.
05 -- Use all UNDO or ROLLBACK Segments on the same disk.
06 -- Use the Rollback and redo log files on a separate disk.
07 -- Use the data, index, SYSTEM, and UNDO tablespaces on a separate
disk.
08 -- Use the data and temporary tablespaces on a separate disk.
09 -- Use the Redundant Array of Inexpensive Disks. The RAID
(Redundant Array of Inexpensive Disks) is some type of redundancy that
you can build in your system a part from Oracle in order to provided
data duplication. You can use RAID supported by hardware or software
application. It is used in the case of a disk crash or failure.
Multiple disks can be formatted in a RAID format such that if one of
them fail, when you replace the bad disk with a new disk then all its
data will be regenerated from other disks.
10 -- Use the raw device if
possible.
|
Display I/O activities
Check the I/O transaction
activity in the data files across multiple disks.
SQL> SELECT file_name, phyrds, phywrts
FROM v$filestat a, dba_data_files b
WHERE a.file# = b.file_id
/
Make sure to distribute the data files so that the average
I/O volumes are as close as possible. It is normally bad practice to
have all datafiles in the same disk.
UNDO mode
Also, unless you have a
good reason, make sure to set the automatic undo management mode to
AUTO.
SQL> ALTER SYSTEM SET undo_management=AUTO
SCOPE=spfile
/
When the system is in AUTO mode, and the transaction needs
more space, Oracle automatically will borrow more space from other
undo segments that have extra space.
Database tables scan
Now, check to see how many times you have to scan the short and long
tables.
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN ('table scans (short tables)','table scans (long tables)')
/
Try to reduce the number by creating proper table indexes.
Note that the count for 'Long Tables scan' must be very small.
Checkpoint activites
Query the V$SYSSTAT directory view for the checkpoint process
activity to monitor the checkpoint process.
SQL> SELECT name, value
FROM v$sysstat
WHERE name like 'background check%'
/
If the "background check started" value is
greater than the "background check completed" value, you
should increase the size of the REDO LOG files.
REDO log file parallel
write
Query the V$SYSTEM_EVENT directory view to see the Log File
Parallel Write Event to monitor and tune a Redo Log file.
SQL> SELECT event, total_waits,
time_waited
FROM v$system_event
WHERE event = 'log file parallel write'
/
The "Waits" column indicates a possible I/O
problem.
"College isn't the
place to go for ideas. " Helen Keller (1880 - 1968) |
Questions:
Q: How do you reduce a
database I/O problem?
Q: How do you monitor a
database I/O problem?
Q: How do you monitor the
checkpoint process activities of a database?
Q: How do you tune the
checkpoint process activities?
Q: How do you use the
V$FILESTAT view?
Q: How do you use the
V$SYSSTAT view?
Q: How do you use the
V$SYSTEM_EVENT view?
Q: How do you use the
DBA_DATA_FILES view?
Q: How do you set the
UNDO_MANAGEMENT parameter?
Q: What does the
UNDO_MANAGEMENT parameter?
Q: Why and how do you
distribute your tablespaces on different disks?
Q: Describe RAID?
Q: What does the
SCOPE=spfile mean in the ALTER SYSTEM SET statement?
Q: How do you avoid I/O
contention in an Oracle database?
Q: What does the following
SQL statement?
SQL> SELECT file_name,
phyrds, phywrts
FROM v$filestat a, dba_data_files b
WHERE a.file# = b.file_id
Q: What does the UNDO_MANAGEMENT=AUTO parameter mean?
Q: What does the following
SQL statement?
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN ('table scans (short tables)',
'table scans (long tables)')
/
|