Performance

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:

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.

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.
 

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)')
/