Hands-On 07 (Database I/O problem)

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 07 (Database I/O problem)
-- Database I/O problem.
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a40
col parameter format a40
col username format a10
col file_name format a50
col event format a40
pause

CLEAR SCR
-- 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.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR

-- In order to reduce the I/O contention we should at least consider 
-- the following steps:

-- Use Oracle to distribute the data files across multiple disks.
-- Use the Oracle partitioning.
-- Use the locally managed tablespace option, unless you have a reason not to do so.
-- Use only the Redo Log files, controlfiles, and dump files on the same disk.
-- Use all UNDO or ROLLBACK Segments on the same disk.
-- Use the Rollback and redo log files on a separate disk.
-- Use the data, index, SYSTEM, and UNDO tablespaces on a separate disk.
-- Use the data and temporary tablespaces on a separate disk.

-- Use the Redundant Array of Inexpensive Disks.

-- Use the raw device if possible.

pause
pause

CLEAR SCR
-- Check the I/O transaction activity in the data files across
-- multiple disks.
pause
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.
pause

CLEAR SCR
-- Unless you have a good reason, make sure to set the automatic
-- undo management mode to AUTO.

pause

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.

pause

CLEAR SCR
-- Now, check to see how many times you have to scan the short and long tables.

pause

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.

pause

CLEAR SCR
-- Query the V$SYSSTAT directory view for the checkpoint process activity
-- to monitor the checkpoint process.

pause

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. 

pause

CLEAR SCR
-- Query the V$SYSTEM_EVENT directory view to see the Log File Parallel
-- Write Event to monitor and tune a Redo Log file.

pause

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.

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause