当前位置:首页 > 开发 > 数据库 > 正文

Oracle 11g ADDM Enhancements(原创)

发表于: 2013-10-21   作者:czmmiao   来源:转载   浏览次数:
摘要: Overview ADDM Enhancements The Automatic Database Diagnostic Monitor, which Oracle introduced in Oracle Database 10g, analyzes the AWR data, diagnoses the root causes for performance problems, and m

Overview ADDM Enhancements

The Automatic Database Diagnostic Monitor, which Oracle introduced in Oracle Database 10g, analyzes the AWR data, diagnoses the root causes for performance problems, and makes recommendations for fixing those problems. The database performs an ADDM analysis on a pair of AWR snapshots, which determine the time period for the ADDM analysis. In Oracle Database 11g, the ADDM has the following new features:

  • ADDM for Real Application Clusters
  • New DBMS_ADDM package
  • Naming Advisor Findings and Directives
  • New ADDM views

ADDM for Real Application Clusters

In Oracle Database 11g, you can deploy the ADDM to perform a cluster-wide performance analysis. In addition to analyzing a single instance, you can now use the ADDM to analyze an entire Oracle Real Application Cluster (RAC). The traditional single instance–wide analysis you’re familiar with from Oracle Database 10g is called Instance ADDM and the cluster-wide mode is called Database ADDM. The cluster-wide mode is a special mode of the ADDM, in which the tool reports on the performance of the entire cluster in addition to the individual instances in the cluster. When operating in an Oracle RAC environment, you can deploy ADDM in the following three analysis modes.

  • Database ADDM    Analyze all instances of the RAC
  • Instance ADDM    Analyzes a particular instance (equivalent to the Oracle Database 10g ADDM analysis)
  • Partial ADDM    Analyzes a subset of the instances in the RAC

Run the ADDM in the Database analysis mode if you’re using an Oracle RAC system, to analyze performance of all instances in the database. The Database ADDM accesses the  AWR data of all instances in the system and identifies critical performance problems for an entire RAC cluster. As with the single-instance ADDM that you’re familiar with from Oracle Database 10g, Database ADDM runs automatically by default when a new AWR snapshot is taken by the database. In this mode, the ADDM will add the DB time for all instances in the RAC to come up with the DB time for the database. The Database analysis mode presents the problems and recommendations for each instance in a single report, instead of your having to peruse multiple reports for the same information. In the Database mode (Database ADDM), the ADDM accesses the AWR data generated by all the instances in a RAC system to analyze the throughput performance of the entire cluster instead of any single instance in the cluster. Database ADDM performs an analysis of the following entries:

  • Global resources such as global locks and global I/O usage
  • High-load SQL
  • Contention across the instances
  • Global cache interconnect traffic
  • Network latency issues
  • Skew in instance response times

The ADDM will aggregate any findings across instances if they affect the entire database. If a finding pertains to a global resource such as I/O, that finding will be deemed as a global finding affecting multiple resources. On the other hand, if a finding pertains to a local resource such as a CPU-bound instance, it results in just a local finding for a single instance.

By default, Database ADDM analysis is performed automatically after each AWR snapshot. If you want, you can run the ADDM in the partial analysis mode by having the ADDM analyze only a subset of the instances in the cluster. Database ADDM is mainly targeted for use by DBAs so they can test the cluster performance as a whole, whereas Instance ADDM is more useful for application development to test application or system changes.

Automatic database diagnostic monitoring is enabled by default. You can control automatic database diagnostic monitoring by setting the control_management_ pack_access parameter, which has the default value diagnostic+tuning. You must specify either the value diagnostic or the value diagnostic+tuning (default value) to enable the ADDM. If you set the value to none, you disable the ADDM. Of course, you must also ensure that the initialization parameter statistics_level is set to either typical or all (but not basic) to enable automatic database diagnostic monitoring.

New DBMS_ADDM Package
Oracle Database 11g introduces the DBMS_ADDM package to facilitate the managing of the ADDM. You can use the DBMS_ADDM package to create an ADDM task and view the results. The following list offers a brief description of the important procedures and functions of the DBMS_ADDM package:

  • ANALYZE_DB    Creates a global ADDM task
  • ANALYZE_INST    Creates an instance ADDM task
  • ANALYZE_PARTIAL    Creates an ADDM task to analyze a set of instances
  • DELETE    Deletes an ADDM task
  • GET_REPORT    Gets a text report of an ADDM task

The following example shows how to create and execute a database ADDM task for an Oracle RAC configuration:

 SQL> begin
  2  :tname := 'Test ADDM Run1';
  3  dbms_addm.analyze_db(:tname,1664,1665);
  4* end;
In the example, I use the ANALYZE_DB procedure to create a global ADDM task that pertains to all instances in an Oracle RAC configuration. The numbers 1664 and 1665 are specified as values for the begin_snapshot and end_snapshot parameters for the ADDM analysis.

To get the ADDM report, use the DBMS_ADDM.GET_REPORT function, as shown here:

SET LONG 100000
SET PAGESIZE 50000
  1* select dbms_addm.get_report(:tname) from dual;
DBMS_ADDM.GET_REPORT(:TNAME)
---------------------------------------------------------------
        ADDM Report for Task 'Test ADDM Run3'
        -------------------------------------
AWR snapshot range from 1664 to 1665.
Time period starts at 10-NOV-07 03.00.04 PM
Time period ends at 10-NOV-07 04.00.12 PM
Analysis Target
---------------
Database 'ORCL2' with DB ID 611115374.
Database version 11.1.0.6.0.
ADDM performed an analysis of instance orcl2,
numbered 1 and hosted at localhost.localdomain.
...
Naming Advisor Findings and Directives

Oracle Database 11g classifies and names all ADDM advisor findings. The database stores the ADDM findings in the DBA_ADVISOR_FINDINGS and the USER_ ADVISOR_FINDINGS views. The classification of ADDM findings enables you to query the DBA_ADVISOR_FINDINGS view to find which findings occur most frequently in the database. You can query the new DBA_ADVISOR_FINDING_ NAMES view to see all the finding names, as shown here:

SQL> select finding_name from dba_advisor_finding_names;
FINDING_NAME
------------------------------
normal, successful completion
"Administrative" Wait Class
"Application" Wait Class
"Cluster" Wait Class
"Concurrency" Wait Class
"
...80 rows selected.
In Oracle Database 11g, you can create an ADDM task by inserting a finding directive to limit or filter the findings. The DBMS_ADDM package contains several “directive” procedures to add specific directives to create directives of various kinds,such as INSERT_FINDING_ DIRECTIVE procedure.This procedure creates a directive to limit reporting of a specific finding type. The directive can be created for a specific task , or for all subsequently created ADDM tasks (such as a system directive).

For example, the following code shows how to use the INSERT_FINDING_ DIRECTIVE procedure to stipulate that the ADDM report show an “Undersized SGA” finding only if it meets two specific conditions: The first condition specifies that the finding must be responsible for at least two average active sessions during the analysis period (MIN_ACTIVE_SESSIONS), and the second condition specifies that the finding must cover at least 10 percent of the total database time during the same period (MIN_PERC_IMPACT).

For min_active_sessions and min_perc_impact ,please refer to following explanation.

  • min_active_sessions: Minimal number of active sessions for the finding. If a finding has less than this number, it is filtered from the ADDM result.
  • min_perc_impact: Minimal number for the "percent impact" of the finding relative to total database time in the analysis period. If the finding's impact is less than this number, it is filtered from the ADDM result.

SQL> var tname varch2(60);
SQL> begin
     dbms_addm.insert_finding_directive(NULL,
     'SGA Directive',
     Undersized SGA',
     2,
     10);
    :tname := 'Test ADDM Task';
     dbms_addm.analyze_inst(:tname,1634,1635);
     end;
     /
In addition to the INSERT_FINDING_DIRECTIVE illustrated here, which helps you create a directive to limit the reporting of a specific finding type, you can also use the following ADDM directives:
INSERT_SQL_DIRECTIVE: Creates a directive to limit reporting of actions on specific SQL statements
INSERT_SEGMENT_DIRECTIVE: Creates a directive to prevent the ADDM from creating actions to run the Segment Advisor on certain segments

INSERT_PARAMETER_DIRECTIVE: Creates a directive to prevent the ADDM from creating actions that alter the value of a specific system parameter
You can delete any of the four INSERT_* procedures by replacing the INSERT with DELETE at the beginning of the procedure name. For example, you can execute the DELETE_FINDING_DIRECTIVE procedure to delete a finding directive you created through the INSERT_FINDING_DIRECTIVE procedure.

Note:the above procedure can only be invoked when the task is in INITIAL status

New ADDM Views
Oracle Database 11g introduces the following new ADDM views:

  • DBA_ADDM_TASKS  Shows all executed ADDM tasks
  • DBA_ADDM_INSTANCES  Shows instance-level information for all completed ADDM tasks
  • DBA_ADDM_FINDINGS  An extension of the corresponding advisor view
  • DBA_ADVISOR_FINDING_NAMES  Provides a list of all registered finding names

Each of the four views shown here also has a corresponding USER_* view associated with it. In addition, the DBA_ADVISOR_FINDINGS, DBA_ADVISOR_ RECOMMENDATIONS, and DBA_ADVISOR_ACTIONS views have a new column named FILTERED, which shows if a row in the view was filtered out by a directive. If the FILTERED column shows a value of Y, it means that row was filtered out by a directive or directives. A value of N means the row wasn’t filtered.

 

参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》
《Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 (11.2)》
  http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65061

本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

Oracle 11g ADDM Enhancements(原创)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
一、自动工作负载库(Automatic Workload Repository,AWR) 自动工作负载库(Automatic Workload Repos
Overview In Oracle Database 10g, Oracle introduced the new Transparent Data Encryption (TDE)
Automatic Diagnostic Repository The automatic diagnostic repository tracks all problems and i
原文地址:http://www.2cto.com/database/201208/150620.html 一、Oracle 下载 注意Oracle分成两个文
oracle 11g 装好以后好久木有动了,今天找健健来一起研究,真是好。 先把oracle服务打开,这家伙太
一、Oracle 下载 注意Oracle分成两个文件,下载完后,将两个文件解压到同一目录下即可。 路径名称中
Overview of Table Clusters A table cluster is a group of tables that share common columns and
一、硬件检测 1、内存检测 oracle11g要求最低1GB内存 命令: grep MemTotal /proc/meninfo 2、交换
数据库和client在不同的机器之上。 在安装之前,在安装Oracle数据库的server上导航到以下的文件夹。
数据库和client在不同的机器之上。 在安装之前,在安装Oracle数据库的server上导航到以下的文件夹。
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号