iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Performance

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 |

 

Lesson 07

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