A Few More of Oracle 10G's Automatic Features

Before we begin our discussion of these three features, I thought it might be beneficial to provide you with a few links to some of my other blogs that discuss 10G's automatic features:

10G Automatic Statistics Generation - Discusses how the 10G database automatically generates performance statistics and stores them in a repository for analysis.

Automatic Tuning Recommendations - I create a not-so mythical performance problem and use the output from ADDM and the advisors to provide the diagnostic information and recommendations I need to solve the issue.

Automatic Storage Management - An overview on Oracle's disk management system.

Automatic Shared Memory Management - A detailed discussion on 10G's Automatic Shared Memory management feature and memory advisors.

10G OEM Grid Control's Automatic Alert Notification System - Information on how to create automatic notifications that allow database administrators to identify (and correct) performance and availability issues before they negatively impact the database environment.

The Changing Role of the DBA - My opinion on the notion that all of these automated features will be reducing the need for experienced DBAs blogs.

Automatic Retention Tuning
Most of us thought that automatic undo would be the solution to all of our before image management problems. Certainly, before image administration has been simplified by reducing the number of tuning knobs to one (UNDO_RETENTION). The UNDO_RETENTION parameter specifies the length of time that undo data for committed transactions will be kept available to provide read consistency. But sizing the UNDO_TABLESPACE to hold the amount of before images generated during the time period specified in the UNDO_RETENTION parameter can still be a challenge.

The goal we are trying to accomplish is to set the UNDO_RETENTION parameter and size the undo tablespace to ensure that we don't run out of space in the undo tablespace and our queries don't receive the dreaded ORA-01555 "Snapshot too old" error messages.

ORA-01555 errors occur when a transaction is unable to successfully read a before image of data to maintain read consistency. Read consistency ensures that all data returned by a query comes from the same point-in-time (query start time). If a block of data changes after the query has started, Oracle will read the before, or undo, image of the data to maintain a read consistent view.

To guarantee read consistency, Oracle returns the ORA-01555 error if it cannot retrieve the before image of the changed data block. This occurs because the undo block has been flushed out of the system to allow more current before images to be stored. The less undo space you have and the lower your UNDO_RETENTION period is, the more often this problem will occur.

The trick is to balance the size of the undo tablespace with the amount of undo retention you need to satisfy your long-running queries. But this is easier said than done. Database applications, by their nature, are very dynamic. It is hard to predict how much undo will be generated for a particular time period. Sizing the undo tablespace and setting the UNDO_RETENTION time period is certainly simpler for small databases that don't have a lot of activity. But the problem becomes much more complex on systems that have a high level of update activity and long-running queries.

Oracle10G automatically tunes the amount of undo data kept available. It is important to note that the automatic tuning process will take place even if the UNDO_RETENTION parameter is set. The database will use the value contained in the UNDO_RETENTION parameter as input to the tuning process. Oracle10G records the times of the longest-running queries and the amount of undo generated to automatically tune the UNDO_RETENTION parameter.

The value of the UNDO_RETENTION parameter also depends on the undo datafile's AUTOEXTEND setting:

• Undo datafile is autoextensible - The undo retention period is set to a time that is slightly longer than the longest-running query. The UNDO_RETENTION parameter must be set when using the autoextend option.
  
  
• Undo datafile is fixed - The undo retention period is set to obtain the maximum amount of undo retention allowed by the amount of free space available in the undo tablespace datafile. Oracle will ignore the UNDO_RETENTION initialization parameter setting unless retention guarantee is enabled. If retention guarantee is enabled, Oracle will not let the undo retention time period fall below the value specified in the UNDO_RETENTION parameter. If undo space becomes constrained, Oracle will let the transactions fail before it will flush before images out that are within the undo retention time period.

Checkpoints, Performance and Instance Recovery
Oracle's checkpoint mechanism ensures that data contained in the buffer cache is safely written to disk on a regular basis. During a checkpoint, the DBWR background process flushes dirty blocks contained in the buffer cache to the database datafiles and the CKPT background process updates the datafile headers with the checkpoint information.

The point-in-time identified by the last successful flush of the buffer cache is called the Checkpoint Position. Oracle knows that all of the data past the point in time identified by the Checkpoint Position does not need to be replayed during instance recovery. That data has already been flushed to the datafiles. If the database crashes and an instance recovery is required, the instance recovery process will begin at the Checkpoint Position.

If you would like to learn more about how instance recovery is performed, please refer to an article I wrote on the database synchronization process. Scroll through the article until you see the title 'Oracle Internals'.

There are a handful of parameters that manually control when a checkpoint is performed in Oracle10G:

• LOG_CHECKPOINT_TIMEOUT - Specified in seconds. This parameter ensures that the Checkpoint Position does not lag beyond the last write to the redo log (sometimes called the tail) by the value specified. For example, a LOG_CHECKPOINT_TIMEOUT value of 300 will tell Oracle to keep the Checkpoint Position no more than 300 seconds behind the last write to the redo log. Setting this parameter to 0 disables time-based checkpoints.
  
  
• LOG_CHECKPOINT_INTERVAL - Specified in system blocks, not Oracle blocks. It ensures that the Checkpoint Position does not lag beyond the last write to the redo log by the specified value. For example, a LOG_CHECKPOINT_VALUE of 500 will tell Oracle to keep the Checkpoint Position no more than 500 blocks behind the last write to the redo log. Setting this parameter to 0 disables block-based checkpoints.
  
  
• FAST_START_MTTR_TARGET - Specifies the amount of time, in seconds, that Oracle will take to perform instance recovery. The database will adjust the frequency of checkpoints to ensure that instance recovery will take no longer than the value specified. This parameter is the more optimal method of defining instance recovery time because it sets the actual amount of time the instance recovery will take. Oracle currently recommends that you use this parameter, as opposed to the previous two parameters to define the amount of time instance recovery takes.
  

A checkpoint is a relatively expensive process. Checkpoints that are taken too frequently have a tendency to "monopolize finite database resources" (one of my favorite terms). The key to optimal database performance is to balance fast instance recovery with optimal database performance.

10G Automatic Checkpoint Tuning
If you do not set FAST-START-MTTR-TARGET, or set it to a very large value, Oracle10g will provide automatic checkpoint tuning. The database will write out dirty blocks from the cache as fast as possible without negatively impacting database performance. The DBA is no longer required to set any of the aforementioned checkpoint parameters.

Automatic DB_FILE_MULTIBLOCK_READ_COUNT Tuning
The Oracle database improves the performance of tablescans by increasing the number of blocks read in a single database I/O operation. If your SQL statement is going to read all of the rows in a table, it makes sense to return as many blocks as you can in a single read. In releases prior to Oracle10G R2, administrators used the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter to tell Oracle how many block to retrieve in the single I/O operation.

But setting the DB_FILE_MULTIBLOCK_READ_COUNT parameter too high can affect access path selection. Full table scans use multi-block reads, so the cost of a full table scan depends on the number of multi-block reads required to read the entire table. The more blocks retrieved in a single multi-block I/O execution, the more favorable a tablescan looks to the optimizer.

In releases prior to Oracle10G R2, the permitted values for DB_FILE_MULTIBLOCK_READ_COUNT were platform-dependent. The most common settings ranged from 4 to 64 blocks per single multi-block I/O execution.

In Oracle 10G R2, Oracle defaults the DB_FILE_MULTIBLOCK_READ_COUNT to the maximum number of blocks that can be effectively read. Although this value is also platform-dependent, Oracle documentation states that it is 1 MB for most platforms. This 1 MB size allows much more data to be read in a single operation in 10GR2 than previous releases. In addition, this larger value does not make tablescans look more favorable to the optimizer. As a result, it is now recommended to not set the DB_FILE_MULTI_BLOCK_READ_COUNT parameter and let the database determine the number of blocks read in multi-block I/O operations.

Summary
As you can see, Oracle10G automates many of the features we used to have to wrestle with on a daily basis. The automated features reduce the amount of time we spend performing mundane administrative actvities and allows us to spend more time on higher ROI activities.

Thanks for reading,
Chris Foot