Objectives

After completing this lesson, you should be able to

do the following:

Diagnose inappropriate use of SYSTEM, RBS, TEMP, DATA, and INDEX tablespaces

Use locally managed tablespaces to avoid space management issues

Detect I/O problems

Ensure that files are distributed to minimize I/O contention and use appropriate type of devices

Use striping where appropriate

Tune checkpoints

Tune DBWn process I/O

I/O Statistic for Different
Oracle File Types

Tablespace Usage

Reserve the SYSTEM tablespace for data dictionary objects

Create locally managed tablespaces to avoid space management issues

Split tables and indexes into separate tablespaces

Create separate rollback tablespaces

Store very large database objects in their own tablespace

Create one or more temporary tablespaces

Distributing Files Across Devices

Separate data files and redo log files

Stripe table data

Reduce disk I/O

Evaluate the use of raw devices

Oracle File Striping

Operating system striping:

Use operating system striping software or RAID

Decide on the right stripe size

Manual striping:

Use the CREATE TABLE or ALTER TABLE ALLOCATE command

Is worthwhile with parallel query usage

Tuning Full Table Scan Operations

Investigate the need for full table scans

Specify the initialization parameter
DB_FILE_MULTIBLOCK_READ_COUNT:

To determine the number of database blocks the server reads at once

To influence the execution plan of the cost-based optimizer

Monitor long-running full table scans with V$SESSION_LONGOPS view

Diagnostic Tools for Checking I/O Statistics

I/O Statistics

Redo Log Groups and Members

Online Redo Log File Configuration

You can configure redo log files as follows:

Size redo log files to minimize contention

Have enough groups to prevent waiting

Store redo log files on separate, fast devices

Query the dynamic performance views V$LOGFILE and V$LOG

Archive Log File Configuration

Allow the LGWR to write to a different disk from the one the ARCn process is reading

Share the archiving work:

Change archiving speed: LOG_ARCHIVE_MAX_PROCESSES, LOG_ARCHIVE_DEST_n, (LOG_ARCHIVE_DUPLEX_DEST, LOG_ARCHIVE_MIN_SUCCEED_DEST)

Diagnostic Tools

Checkpoints

Checkpoints cause:

DBWn to perform I/O

CKPT to update the data file header and control file

Frequent checkpoints:

Reduce instance recovery time

Decrease run-time performance

Guidelines

Size the online redo log files to cut down the number of checkpoints.

Add online redo log groups to increase the time before LGWR starts to overwrite.

Regulate checkpoints with the initialization parameters:

FAST_START_IO_TARGET

LOG_CHECKPOINT_INTERVAL

LOG_CHECKPOINT_TIMEOUT

DB_BLOCK_MAX_DIRTY_TARGET

Multiple I/O Slaves

Provide nonblocking asynchronous I/O requests

Are deployed by DBWn, LGWR, ARCn, and backup processes

Are typically not recommended if asynchronous I/O is available

Follow the naming convention ora_iNnn_SID

Initialization Parameters

Deploy I/O slaves for DBWn, LGWR, ARCn, and BACKUP processes with:

DBWR_IO_SLAVES

BACKUP_TAPE_IO_SLAVES

Turn on or off the asynchronous I/O with:

DISK_ASYNCH_IO

TAPE_ASYNCH_IO

Multiple DBWn Processes

Deploy multiple DBWn processes with DB_WRITER_PROCESSES (DBW0 to DBW9)

Useful for SMP systems with large numbers of CPUs

Cannot concurrently be used with multiple I/O slaves

Tuning DBWn I/O

Influence the DBWn to write dirty buffers more often with the parameter DB_BLOCK_MAX_DIRTY_TARGET.

If the number of dirty buffers is under the computed low limit, DBWn is not agressive for writing checkpoints buffers.

If the number is between the low and high computed limits, DBWn writes from the checkpoint queue until the number of checkpoint buffers drops under low.

If the number is greater than the high limit, DBWn writes out checkpoint buffers.

The default value is (2*32) – 1.

Summary

In this lesson, you should have learned how to:

Monitor I/O contention

Stripe Oracle files

Configure tablespaces, online redo log files, and archived log files

Configure checkpoint frequency

Deploy I/O slaves

Influence DBWn I/Os


		After completing this lesson, you should be able to
		do the following:
		Diagnose inappropriate use of SYSTEM, RBS, TEMP, DATA, and INDEX tablespaces
		Use locally managed tablespaces to avoid space management issues
		Detect I/O problems
		Ensure that files are distributed to minimize I/O contention and use appropriate type of devices
		Use striping where appropriate
		Tune checkpoints
		Tune DBWn process I/O


		Reserve the SYSTEM tablespace for data dictionary objects
		Create locally managed tablespaces to avoid space management issues
		Split tables and indexes into separate tablespaces
		Create separate rollback tablespaces
		Store very large database objects in their own tablespace
		Create one or more temporary tablespaces


		Separate data files and redo log files
		Stripe table data
		Reduce disk I/O
		Evaluate the use of raw devices


	Operating system striping:
		Use operating system striping software or RAID
		Decide on the right stripe size
	Manual striping:
		Use the CREATE TABLE or ALTER TABLE ALLOCATE command
		Is worthwhile with parallel query usage


		Investigate the need for full table scans
		Specify the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT:
			To determine the number of database blocks the server reads at once
			To influence the execution plan of the cost-based optimizer
		Monitor long-running full table scans with V$SESSION_LONGOPS view


	You can configure redo log files as follows:
		Size redo log files to minimize contention
		Have enough groups to prevent waiting
		Store redo log files on separate, fast devices
		Query the dynamic performance views V$LOGFILE and V$LOG


		Allow the LGWR to write to a different disk from the one the ARCn process is reading
		Share the archiving work:


		Change archiving speed: LOG_ARCHIVE_MAX_PROCESSES, LOG_ARCHIVE_DEST_n, (LOG_ARCHIVE_DUPLEX_DEST, LOG_ARCHIVE_MIN_SUCCEED_DEST)


		Checkpoints cause:
			DBWn to perform I/O
			CKPT to update the data file header and control file
		Frequent checkpoints:
			Reduce instance recovery time
			Decrease run-time performance


		Size the online redo log files to cut down the number of checkpoints.
		Add online redo log groups to increase the time before LGWR starts to overwrite.
		Regulate checkpoints with the initialization parameters:
			FAST_START_IO_TARGET
			LOG_CHECKPOINT_INTERVAL
			LOG_CHECKPOINT_TIMEOUT
			DB_BLOCK_MAX_DIRTY_TARGET


		Provide nonblocking asynchronous I/O requests
		Are deployed by DBWn, LGWR, ARCn, and backup processes
		Are typically not recommended if asynchronous I/O is available
		Follow the naming convention ora_iNnn_SID


	Deploy I/O slaves for DBWn, LGWR, ARCn, and BACKUP processes with:
			DBWR_IO_SLAVES
			BACKUP_TAPE_IO_SLAVES
	Turn on or off the asynchronous I/O with:
			DISK_ASYNCH_IO
			TAPE_ASYNCH_IO


		Deploy multiple DBWn processes with DB_WRITER_PROCESSES (DBW0 to DBW9)
		Useful for SMP systems with large numbers of CPUs
		Cannot concurrently be used with multiple I/O slaves


		Influence the DBWn to write dirty buffers more often with the parameter DB_BLOCK_MAX_DIRTY_TARGET.
		If the number of dirty buffers is under the computed low limit, DBWn is not agressive for writing checkpoints buffers.
		If the number is between the low and high computed limits, DBWn writes from the checkpoint queue until the number of checkpoint buffers drops under low.
		If the number is greater than the high limit, DBWn writes out checkpoint buffers.
		The default value is (2*32) – 1.


		In this lesson, you should have learned how to:
		Monitor I/O contention
		Stripe Oracle files
		Configure tablespaces, online redo log files, and archived log files
		Configure checkpoint frequency
		Deploy I/O slaves
		Influence DBWn I/Os