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
August 16, 2007
Using Automatic Database Diagnostic Monitor Manually
For Oracle after 10g the statistical data needed for accurate diagnosis of a problem is saved in the Automatic Workload Repository(AWR). The Automatic Database Diagnostic Monitor(ADDM) analyzes the AWR data on a regular basis, then locates the root causes of performance problems, provides recommendations for correcting any problems and identifies non-problem areas of the system. Because AWR is a repository of historical performance data, ADDM can be used to analyze performance issues after the event.
This decision tree Oracle developed in years under ADDM improves root-cause analysis and after this amount of research and development you pay additional cost for this feature even you are using Enterprise Edition.
The goal of the analysis is to reduce a single throughput metric called DB time. DB time is the cumulative time spent by the database server in processing user requests. It includes wait time and CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
The background process Manageability Monitor Process(MMON) schedules the automatic running of the ADDM. You only need to make sure that the initialization parameters STATISTICS_LEVEL is set to TYPICAL or ALL, in order for the AWR to gather its cache of performance statistics. MMON schedules the ADDM to run every time the AWR collects its most recent snapshot. To view the ADDM’s findings:
* Use the OEM Database Control, the primary interface for diagnostic monitoring is the Oracle Enterprise Manager Database Control.
* Run the Oracle-provided script addmrpt.sql, very similar to statspack or awr reports.
* Run DBMS_ADVISOR APIs
Note: The DBMS_ADVISOR package requires the ADVISOR privilege.
Code Listing 112 – Manual ADDM Demo
ADDM analysis finding consists of the following four components:
* The definition of the problem itself
* The root cause of the performance problem
* Recommendation(s) to fix the problem
* The rationale for the proposed recommendations
Example ADDM Report
FINDING 1: 31% impact (7798 seconds)
————————————
SQL statements were not shared due to the usage of literals. This resulted in additional hard parses which were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 31% benefit (7798 seconds)
ACTION: Investigate application logic for possible use of bind variables
instead of literals. Alternatively, you may set the parameter “cursor_sharing” to “force”.
RATIONALE: SQL statements with PLAN_HASH_VALUE 3106087033 were found to be using literals. Look in V$SQL for examples of such SQL statements.
In order to determining optimal I/O performance, ADDM uses the default value of the parameter DBIO_EXPECTED, which is 10 milliseconds.
SELECT PARAMETER_VALUE
FROM DBA_ADVISOR_DEF_PARAMETERS
WHERE ADVISOR_NAME=’ADDM’
AND PARAMETER_NAME=’DBIO_EXPECTED’
Today’s hardwares can be significantly different, so you may set the parameter value one time for all subsequent ADDM executions of course for better advices. Since this is not an init.ora parameter, the parameter can be changed again with DBMS_ADVISOR supplied package;
DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(‘ADDM’,'DBIO_EXPECTED’, 8000);
There are two important views you will be checking for the results of the ADDM analysis:
1. DBA_ADVISOR_RECOMMENDATIONS: This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each run. The recommendations should be looked at in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column gives the benefit to the system you can expect after the recommendation is carried out.
2. DBA_ADVISOR_FINDINGS: This view displays all the findings and symptoms that the diagnostic monitor encountered along with the specific recommendation.
Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Refences Used :
Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g at oracle-base.com
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Chapter 6 Automatic Performance Diagnostics
