Life after 10g Enterprise Manager Grid Control and its advisors

In my opinion history will show that, life of an Oracle DBA is divided into two stages; before and after 10g Enterprise Manager Grid Control :) I still remember some old days after paying to Oracle every possible extra licensing you may pay, everybody were paying lots of extra money to some bunch of other companies’ tools just to manage Oracle effectively. But somehow, someday Oracle decided to end its customers’ pain; Grid Control, Automatic Storage Management(ASM) and SQL Developer are examples of this strategy, so those were now the “good” old days just for some companies I guess.

Everybody even presidents needs, loves advisors. It is the same with Oracle, after 10g and with 11g Oracle is continuing to invest on its advisories. Memory, SQL Access and Tuning, Segment advisors have important parts in our lives now. But with this post I planned to discuss on some less popular advisors(I believe) like Undo, Redo and Automatic Checkpoint Tuning Advisors with 10g and also some 11g delights like Partitioning and Data Recovery advisors.

The Undo Advisor assists in correctly sizing the undo tablespace. The Undo Advisor can also be used to set the low threshold value of the undo retention period for any Oracle Flashback requirements. And to access the Undo Advisor in the Database Control you may follow; Administration > Undo Management > Undo Advisor

Also after 10g Rollback Monitoring improved; when a transaction rolls back, the event is recorded in the view V$SESSION_LONGOPS, if the process takes more than six seconds. This view enables you to estimate when the monitored rollback process will finish.

SELECT TIME_REMAINING, SOFAR/TOTALWORK*100 PCT
FROM V$SESSION_LONGOPS
WHERE SID = 9
AND OPNAME =’Transaction Rollback’

Redo Log Tuning Advisory and Automatic Checkpoint Tuning are also new features introduced with 10G. The size of the redo log files can influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes. Generally, larger redo log files provide better performance, however it must balanced out with the expected recovery time, as rule of thumb switching logs at most once every fifteen-twenty minutes. Undersized log files increase checkpoint activity and increase CPU usage.

Checkpoint frequency is affected by several factors, including log file size and the setting of the FAST_START_MTTR_TARGET initialization parameter. If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time, Oracle automatically tries to checkpoint as frequently as necessary. Under this condition, the size of the log files should be large enough to avoid additional checkpointing due to under sized log files.

The redo logfile sizing advisory is specified by column optimal_logfile_size of v$instance_recovery. This feature require setting the parameter “fast_start_mttr_target” for the advisory to take effect and populate the column optimal_logfile_size.

You can try this with different settings of “FAST_START_MTTR_TARGET” but –
* If FAST_START_MTTR_TARGET is set to such a small value that it is impossible to do a recovery within its time frame then target_mttr field of v$instance_recovery contains the effective MTTR target which is larger than FAST_START_MTTR_TARGET.
* If FAST_START_MTTR_TARGET is set to such a high value that even in worst case entire buffer cache is dirty) recovery would not take that long, then target_mrrt field contains the estimated mttr in worst-case scnerios.

Code Listing 117 – Redo Log Tuning Advisory Demo

And to access the Redo Logfile Size Advisor you may follow; Administration > Storage > Redo Log Groups > Sizing Advice

10g also supports automatic checkpoint tuning. But by default, this feature is not enabled, because FAST_START_MTTR_TARGET has a default value of 0. It is an advancement over the MTTR related parameter introduced in earlier versions. The idea is to use the periods of low I/O usage to advance checkpoints and therefore improve availability.

To enable automatic checkpoint tuning, unset FAST_START_MTTR_TARGET or set it to a nonzero value(This is measured in seconds). If you set this parameter to zero this feature will be disabled. When you enable fast-start checkpointing, remove or disable(set to 0) the following initialization parameters:
– LOG_CHECKPOINT_INTERVAL
– LOG_CHECKPOINT_TIMEOUT
– FAST_START_IO_TARGET
Enabling fast-start checkpointing can be done statically using the initialization files or dynamically using –

SQL> alter system set FAST_START_MTTR_TARGET=10;

Enabling fast-start checkpointing increases the average number of writes per transaction that DBWn issues for a given workload. However, if the system is not already near or at its maximum I/O capacity, then fast-start checkpointing has a negligible impact on performance. View V$MTTR_TARGET_ADVICE will give information on the additional I/O’s on various values of FAST_START_MTTR_TARGET. But if –
– FAST_START_MTTR_TARGET is set to a low value – Fast-start checkpointing is more aggressive. The average number of writes per transaction that DBWn issues is higher in order to keep the thread checkpoint sufficiently advanced to meet the requested MTTR.
– FAST_START_MTTR_TARGET is set to a high value – Fast-start checkpointing in less aggressive, and the average number of writes per transaction that DBWn issues is lower.
– FAST_START_MTTR_TARGET is unset – automatic checkpoint tuning is in effect. Average number of writes per transaction is reduced but at the same time MTTR is highest.

So what about future releases and Oracle’s advisory strategy, I advise :) you to check 11g Manageability presentation’s fifth slide for some hints. After 11g a new advisor called Partition Advisor which analyzes the data access patterns and suggests the decision to choose the partitioning scheme and the partitioning column(s) will be assisting us especially during development and testing stages. By the way before 10g if you drop a partitioned table Oracle removed all the partitions at once, so time and resource consuming process. After 10g Release 2 when you drop a partitioned table, partitions are dropped one by one, maybe you already felt the change :)

Data Recovery Advisor will be another friend which automatically diagnose data failures and recommend repairs. You can repair failures manually or request that they be repaired automatically. Of course Enterprise Manager includes interfaces for these new advisors with 11g.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Refences Used :
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)
Note:265831.1 – Automatic Checkpoint Tuning in 10g
Note:274264.1 – REDO LOGS SIZING ADVISORY
Note 180894.1 – “V$INSTANCE_RECOVERY”
Note 151062.1 – Init.ora Parameter “FAST_START_MTTR_TARGET” Reference Note
Note 30754.1 – Init.ora Parameter “LOG_CHECKPOINT_INTERVAL” Reference Note
Note 30755.1 – Init.ora Parameter “LOG_CHECKPOINT_TIMEOUT” Reference Note
Note 68931.1 – Init.ora Parameter “FAST_START_IO_TARGET” Reference Note
http://psoug.org/reference/dbms_advisor.html