Notes

Outline

Database Configuration and I/O Issues

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