Oracle dbms_advisor ADDM Oracle Tips by Burleson Consulting

Advisor Central in OEM

The Advisor Central screen displays the three advisory areas of Enterprise Manager: ADDM, the SQL Tuning Advisor and the Segment Advisor as shown in Figure 19.37.  This OEM information is externalized via the dbms_advisor package and the dba_advisor_tasks view.

Under the Advisors section of this screen, hyperlinks to the main Advisory areas are shown in Figure 19.38:

Each of the hyperlinks provides important advisory functions, yet each one addresses very different areas of Oracle tuning noted as follows:

ADDM: The Automatic Database Diagnostic Monitor provides intelligent recommendations regarding Oracle changes that improves performance and throughput.

SQL Tuning Advisor: This component prepares SQL tuning sets and SQL profiles for tuning sub-optimal SQL statements.

SQL Access Advisor: This component displays execution plans for SQL statements and recommends changes to the SQL data access paths.

Automatic Memory Manager: This component implements the dynamic SGA features in Oracle10g in which any of the following SGA areas can be resized, depending on the database load.  If the DBA is not using the Automatic Memory Management (AMM) features to automatically adjust the SGA pools, the Memory Advisor should be used to provide the necessary recommendations for resizing the following SGA pools:

§         Data Buffer Pool Advisory: This component implements the v$db_cache_advice view and the dba_hist_db_cache_advice view.  Whenever Oracle detects a shortage of RAM data buffers, Oracle may borrow RAM frames from other regions to allocate to the data buffers.

§         Program Global Area (PGA) Advisory: This component implements the v$pga_target_advice utility and is externalized in the dba_hist_pgastat view.  The PGA monitors disk sorts hash joins and determines the optimal setting for the PGA RAM region.

§         Shared Pool Advisory: This component adjusts the shared pool using the v$shared_pool_advice and the new dba_hist_shared_pool_advice view.

§         Automatic Segment Advisor X "Segment Advisor"  (ASM): This component advises on segment conditions including changes to data file and tablespace characteristics.

These advisory functions are shown in detail later in this chapter, but it is important to note that OEM is an open-source tool, and all of the advisory information is externalized in a series of dba_advisor views, such as the following:

§         dba_advisor_actions

§         dba_advisor_commands

§         dba_advisor_definitions

§         dba_advisor_def_parameters

§         dba_advisor_directives

§         dba_advisor_findings

§         dba_advisor_journal

§         dba_advisor_log

§         dba_advisor_objects

§         dba_advisor_object_types

§         dba_advisor_parameters

§         dba_advisor_rationale

§         dba_advisor_recommendations

§         dba_advisor_tasks

§         dba_advisor_templates

§         dba_advisor_usage

To see the internals of the Automatic Segment Advisor, additional dictionary views are available. In the list below, sqla represents the Access advisor views, while sqlw represents the Workload tasks:

§         dba_advisor_sqla_rec_sum

§         dba_advisor_sqla_wk_map

§         dba_advisor_sqla_wk_stmts

§         dba_advisor_sqlw_colvol

§         dba_advisor_sqlw_journal

§         dba_advisor_sqlw_parameters

§         dba_advisor_sqlw_stmts

§         dba_advisor_sqlw_sum

§         dba_advisor_sqlw_tables

§         dba_advisor_sqlw_tabvol

§         dba_advisor_sqlw_templates

Now, it is time to move on to an exploration of the ADDM.  A review of the ADDM screens is started by clicking on the ADDM hyperlink.  This reveals the ADDM Database Activity and ADDM Performance Analysis screens.

Conclusion

Oracle has made a major investment in the development of OEM with the goal of creating a one-stop interface for the myriad of Oracle administration duties.  The tuning tools are littered throughout the dozens of complex and confusing screens.

On the other hand, third-party tools such as Ion and DBFlash provide a task-specific tool that provides the experienced DBA with a scalpel for use in surgically finding and correcting poorly performing SQL.

§         Intelligent Approach: By utilizing the Oracle wait interface and specific Oracle statistics and tying this information back to their source SQL statements, tools such as DBFlash allow for correction of the specific problems in an Oracle database SQL portfolio, eliminating the tuning of apparently bad SQL which, in fact, is not a problem at all.  In contrast, Oracle’s SQL Access advisor makes global recommendations only on indexes and materialized views X "materialized views"  while the SQL Tuning advisor makes specific recommendations; however, these recommendations may be made with limited intelligence.  The SQL Access Advisor is a limited tool, and it can only recommend simple solutions such as new indexes and materialized views, ignoring the dozens of other SQL tuning options.

§         Less overhead:  While AWR, through use of the MMON background process, is more efficient at gathering statistics than the use of the Oracle job interface was for STATSPACK, it still has more performance impact than some third-party tools.

§         Less expensive: Oracle ASH is a component of Oracle Enterprise Manager X "Oracle Enterprise Manager"  Diagnostic Pack and the Oracle Tuning Pack.  These are extra cost features, and they must be licensed separately. The costs of these tuning packs can be prohibitively expensive for some shops.

§         Tightly Focused: The OEM screens are not focused on performance management, but rather the entire database, or databases being monitored and their overall status. The OEM user can presented with a bewildering array of options.

§         Fast problem identification: In comparison to OEM, problem SQL can be quickly found by tools like Ion and DBFlash within a few mouse clicks.  The OEM interface and SQL Analyzer the DBA may face an ordeal of scheduled analysis and correction jobs. The OEM interface practices an extreme amount of hand holding, while reassuring to the inexperienced DBA, which can be annoying to the more experienced DBA. The DBFlash interface assumes an experienced DBA is at the helm.

§         Fast tuning Solutions: Savvy DBAs know that long-term workload tests do not help tune most SQL.  Oracle claims that many Oracle SQL statements will change execution plans as the workload changes.  While this is true for a small number of shops, the vast majority of Oracle shops will find that there is one, and only one, optimal execution plan for any SQL statement.

This chapter has focused on the Oracle10g Enterprise Manager tuning components and on how OEM displays AWR and ASH data in a visual form.

While the OEM performance screens are built-in to the OEM console, many Oracle professionals are not aware that using these screens may require additional Oracle licenses.  Third-party tools that bypass the AWR and ASH views can sometimes provide a more cost-effective solution.

Finally, it is noteworthy that many senior DBA’s eschew GUI tools and use customized scripts to expose Oracle performance issues.

The next chapter will explore techniques for tuning Oracle Cluster Systems and investigate Real Application Clusters (RAC) and tuning for Oracle10g grid systems.

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts: