The DBMS_ADDM package facilitates the use of Advisor functionality regarding the Automatic Database Diagnostic Monitor.
See Also:
Oracle Real Application Clusters Administration and Deployment Guide for more information about "Automatic Workload Repository in Oracle Real Application Clusters Environments"
Oracle Database Performance Tuning Guide for more information about "Automatic Performance Diagnostics"
This chapter contains the following topics:
Security Model
Table 17-1 DBMS_ADDM Package Subprograms
| Subprogram | Description |
|---|---|
|
Creates an ADDM task for analyzing in database analysis mode and executes it |
|
|
Creates an ADDM task for analyzing in instance analysis mode and executes it. |
|
|
Creates an ADDM task for analyzing a subset of instances in partial analysis mode and executes it |
|
|
Produces a Compare Period ADDM report comparing the performance of a capture to a replay |
|
|
Produces a Compare Period ADDM report for a database-wide performance comparison |
|
|
Produces a Compare Period ADDM report for an instance-level performance comparison |
|
|
Produces a Compare Period ADDM report comparing the performance of a replay to another replay |
|
|
Deletes an already created ADDM task (of any kind) |
|
|
Deletes a finding directive |
|
|
Deletes a parameter directive |
|
|
Deletes a segment directive |
|
|
Deletes a SQL directive |
|
|
Returns a string containing the SQL text of an ASH query identifying the rows in ASH with impact for the finding |
|
|
Retrieves the default text report of an executed ADDM task |
|
|
Creates a directive to limit reporting of a specific finding type. |
|
|
Creates a directive to prevent ADDM from creating actions to alter the value of a specific system parameter |
|
|
Creates a directive to prevent ADDM from creating actions to "run Segment Advisor" for specific segments |
|
|
Creates a directive to limit reporting of actions on specific SQL |
|
|
Produces a real-time report of ADDM activity |
This procedure creates an ADDM task for analyzing in database analysis mode and executes it.
DBMS_ADDM.ANALYZE_DB ( task_name IN OUT VARCHAR2, begin_snapshot IN NUMBER, end_snapshot IN NUMBER, db_id IN NUMBER := NULL);
Table 17-2 ANALYZE_DB Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the task to be created |
|
|
Number of the snapshot that starts the analysis period |
|
|
Number of the snapshot that ends the analysis period |
|
|
Database ID for the database you to analyze. By default, this is the database currently connected |
The name of the created task is returned in the task_name parameter. It may be different from the value that is given as input (only in cases that name is already used by another task).
To create an ADDM task in database analysis mode and execute it, with its name in variable tname:
var tname VARCHAR2(60); BEGIN :tname := 'my_database_analysis_mode_task'; DBMS_ADDM.ANALYZE_DB(:tname, 1, 2); END
To see a report:
SET LONG 100000 SET PAGESIZE 50000 SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
Note that the return type of a report is a CLOB, formatted to fit line size of 80.
This procedure creates an ADDM task for analyzing in instance analysis mode and executes it.
DBMS_ADDM.ANALYZE_INST ( task_name IN OUT VARCHAR2, begin_snapshot IN NUMBER, end_snapshot IN NUMBER, instance_number IN NUMBER := NULL, db_id IN NUMBER := NULL);
Table 17-3 ANALYZE_INST Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the task to be created |
|
|
Number of the snapshot that starts the analysis period |
|
|
Number of the snapshot that ends the analysis period |
|
|
Number of the instance to analyze. By default it is the instance currently connected |
|
|
Database ID for the database you to analyze. By default, this is the database currently connected |
The name of the created task is returned in the task_name parameter. It may be different from the value that is given as input (only in cases that name is already used by another task).
On single instance systems (when not using Oracle RAC) the resulting task is identical to using the ANALYZE_DB procedure.
To create an ADDM task in instance analysis mode and execute it, with its name in variable tname:
var tname VARCHAR2(60); BEGIN :tname := 'my_instance_analysis_mode_task'; DBMS_ADDM.ANALYZE_INST(:tname, 1, 2); END
To see a report:
SET LONG 100000 SET PAGESIZE 50000 SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
Note that the return type of a report is a CLOB, formatted to fit line size of 80.
This procedure creates an ADDM task for analyzing a subset of instances in partial analysis mode and executes it.
DBMS_ADDM.ANALYZE_PARTIAL ( task_name IN OUT VARCHAR2, instance_numbers IN VARCHAR2, begin_snapshot IN NUMBER, end_snapshot IN NUMBER, db_id IN NUMBER := NULL);
Table 17-4 ANALYZE_PARTIAL Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the task to be created |
|
|
Comma separated list of instance numbers to analyze |
|
|
Number of the snapshot that starts the analysis period |
|
|
Number of the snapshot that ends the analysis period |
|
|
Database ID for the database you to analyze. By default, this is the database currently connected |
The name of the created task is returned in the task_name parameter. It may be different from the value that is given as input (only in cases that name is already used by another task).
To create an ADDM task in partial analysis mode and execute it, with its name in variable tname:
var tname VARCHAR2(60); BEGIN :tname := 'my_partial_analysis_modetask'; DBMS_ADDM.ANALYZE_PARTIAL(:tname, '1,2,3', 1, 2); END
To see a report:
SET LONG 100000 SET PAGESIZE 50000 SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
Note that the return type of a report is a CLOB, formatted to fit line size of 80.
This function produces a Compare Period ADDM report comparing the performance of a capture to a replay. The AWR data must reside in the same database, but it can originate from different databases. The function generates a report in either XML or HTML(Active Report) format.
DBMS_ADDM.COMPARE_CAPTURE_REPLAY_REPORT ( replay_id IN NUMBER, report_type IN VARCHAR2 := 'HTML') RETURN CLOB;
Table 17-5 COMPARE_CAPTURE_REPLAY_REPORT Function Parameters
| Parameter | Description |
|---|---|
|
|
Replay ID to use as the base period. The base period is the baseline period to compare in order to determine improvement or regression. |
|
|
|
This function produces a Compare Period ADDM report comparing the performance of a database over two different time periods or the performance of two different databases over two different time periods.The AWR data must reside in the same database, but it can originate from different databases. The function generates a report in either XML or HTML(Active Report) format.
DBMS_ADDM.COMPARE_DATABASES ( base_dbid IN NUMBER := NULL, base_begin_snap_id IN NUMBER, base_end_snap_id IN NUMBER, comp_dbid IN NUMBER := NULL, comp_begin_snap_id IN NUMBER, comp_end_snap_id IN NUMBER, report_type IN VARCHAR2 := 'HTML') RETURN CLOB;
Table 17-6 COMPARE_DATABASES Function Parameters
| Parameter | Description |
|---|---|
|
|
Database id ( |
|
|
Begin AWR snapshot ID of the base period. |
|
|
End AWR snapshot ID of the base period. |
|
|
Database id ( |
|
|
Begin AWR snapshot ID of the comparison period |
|
|
End AWR snapshot ID of the comparison period |
|
|
|
This function produces a Compare Period ADDM report comparing the performance of a single instance over two different time periods or the performance of two different instances over two different time periods.The AWR data must reside in the same database, but it can originate from different databases. The function generates a report in either XML or HTML(Active Report) format.
DBMS_ADDM.COMPARE_INSTANCES ( base_dbid IN NUMBER := NULL, base_instance_id IN NUMBER base_begin_snap_id IN NUMBER, base_end_snap_id IN NUMBER, comp_dbid IN NUMBER := NULL, comp_instance_id IN NUMBER, comp_begin_snap_id IN NUMBER, comp_end_snap_id IN NUMBER, report_type IN VARCHAR2 := 'HTML') RETURN CLOB;
Table 17-7 COMPARE_INSTANCES Function Parameters
| Parameter | Description |
|---|---|
|
|
Database id ( |
|
|
Instance number of the database instance to include from the base period |
|
|
Begin AWR snapshot ID of the base period. |
|
|
End AWR snapshot ID of the base period. |
|
|
Database id ( |
|
|
Instance number of the database instance to include from the comparison period |
|
|
Begin AWR snapshot ID of the comparison period |
|
|
End AWR snapshot ID of the comparison period |
|
|
|
This function produces a Compare Period ADDM report comparing the performance of a replay to another replay. The AWR data must reside in the same database, but it can originate from different databases. The function generates a report in either XML or HTML(Active Report) format.
DBMS_ADDM.COMPARE_CAPTURE_REPLAY_REPORT ( replay_id1 IN NUMBER, replay_id2 IN NUMBER, report_type IN VARCHAR2 := 'HTML') RETURN CLOB;
Table 17-8 COMPARE_REPLAY_REPLAY_REPORT Function Parameters
| Parameter | Description |
|---|---|
|
|
Replay ID to use as the base period. The base period is the baseline period to compare in order to determine improvement or regression. |
|
|
Replay ID to use as the comparison period. The comparison period is the period to compare to the base period in order to determine improvement or regression. |
|
|
|
This procedure deletes an already created ADDM task (of any kind). For database analysis mode and partial analysis mode this deletes the local tasks associated with the main task.
This procedure deletes a parameter directive. This removes a specific system directive for parameters. Subsequent ADDM tasks are not affected by this directive.
The function returns a string containing the SQL text of an ASH query identifying the rows in ASH with impact for the finding. For most types of findings this identifies the exact rows in ASH corresponding to the finding. For some types of findings the query is an approximation and should not be used for exact identification of the finding's impact or the finding's specific activity.
This procedure creates a directive to limit reporting of a specific finding type. The directive can be created for a specific task (only when the task is in INITIAL status), or for all subsequently created ADDM tasks (such as a system directive).
DBMS_ADDM.INSERT_FINDING_DIRECTIVE ( task_name IN VARCHAR2, dir_name IN VARCHAR2, finding_name IN VARCHAR2, min_active_sessions IN NUMBER := 0, min_perc_impact IN NUMBER := 0);
Table 17-16 INSERT_FINDING_DIRECTIVE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the task this directive applies to. If the value is |
|
|
Name of the directive. All directives must be given unique names. |
|
|
Name of an ADDM finding to which this directive applies. All valid findings names appear in the |
|
|
Minimal number of active sessions for the finding. If a finding has less than this number, it is filtered from the ADDM result. |
|
|
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. |
A new ADDM task is created to analyze a local instance. However, it has special treatment for 'Undersized SGA' findings. The result of GET_REPORT shows only an 'Undersized SGA' finding if the finding is responsible for at least 2 average active sessions during the analysis period, and this constitutes at least 10% of the total database time during that period.
var tname VARCHAR2(60); BEGIN DBMS_ADDM.INSERT_FINDING_DIRECTIVE( NULL, 'Undersized SGA directive', 'Undersized SGA', 2, 10); :tname := 'my_instance_analysis_mode_task'; DBMS_ADDM.ANALYZE_INST(:tname, 1, 2); END;
To see a report containing 'Undersized SGA' findings regardless of the directive:
SELECT DBMS_ADVISOR.GET_TASK_REPORT(:tname, 'TEXT', 'ALL') FROM DUAL;
This procedure creates a directive to prevent ADDM from creating actions to alter the value of a specific system parameter. The directive can be created for a specific task (only when the task is in INITIAL status), or for all subsequently created ADDM tasks (such as a system directive).
DBMS_ADDM.INSERT_PARAMETER_DIRECTIVE ( task_name IN VARCHAR2, dir_name IN VARCHAR2, parameter_name IN VARCHAR2);
Table 17-17 INSERT_PARAMETER_DIRECTIVE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the task this directive applies to. If the value is |
|
|
Name of the directive. All directives must be given unique names. |
|
|
Specifies the parameter to use. Valid parameter names appear in |
A new ADDM task is created to analyze a local instance. However, it has special treatment for all actions that recommend modifying the parameter 'sga_target'. The result of GET_REPORT does not show these actions.
var tname varchar2(60); BEGIN DBMS_ADDM.INSERT_PARAMETER_DIRECTIVE( NULL, 'my Parameter directive', 'sga_target'); :tname := 'my_instance_analysis_mode_task'; DBMS_ADDM.ANALYZE_INST(:tname, 1, 2); END;
To see a report containing all actions regardless of the directive:
SELECT DBMS_ADVISOR.GET_TASK_REPORT(:tname, 'TEXT', 'ALL') FROM DUAL;
This procedure creates a directive to prevent ADDM from creating actions to "run Segment Advisor" for specific segments. The directive can be created for a specific task (only when the task is in INITIAL status), or for all subsequently created ADDM tasks (such as a system directive).
DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE ( task_name IN VARCHAR2, dir_name IN VARCHAR2, owner_name IN VARCHAR2, object_name IN VARCHAR2 := NULL, sub_object_name IN VARCHAR2 := NULL);
DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE ( task_name IN VARCHAR2, dir_name IN VARCHAR2, object_number IN NUMBER);
Table 17-18 INSERT_SEGMENT_DIRECTIVE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the task this directive applies to. If the value is |
|
|
Name of the directive. All directives must be given unique names. |
|
|
Specifies the owner of the segment/s to be filtered. A wildcard is allowed in the same syntax used for "like" constraints. |
|
|
Name of the main object to be filtered. Again, wildcards are allowed. The default value of |
|
|
Name of the part of the main object to be filtered. This could be a partition name, or even sub partitions (separated by a '.'). Again, wildcards are allowed. The default value of |
|
|
Object number of the |
A new ADDM task is created to analyze a local instance. However, it has special treatment for all segments that belong to user SCOTT. The result of GET_REPORT does not show actions for running Segment advisor for segments that belong to SCOTT.
var tname VARCHAR2(60);
BEGIN
DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(NULL,
'my Segment directive',
'SCOTT');
:tname := 'my_instance_analysis_mode_task';
DBMS_ADDM.ANALYZE_INST(:tname, 1, 2);
END;
To see a report containing all actions regardless of the directive:
SELECT DBMS_ADVISOR.GET_TASK_REPORT(:tname, 'TEXT', 'ALL') FROM DUAL;
This procedure creates a directive to limit reporting of actions on specific SQL. The directive can be created for a specific task (only when the task is in INITIAL status), or for all subsequently created ADDM tasks (such as a system directive).
DBMS_ADDM.INSERT_SQL_DIRECTIVE ( task_name IN VARCHAR2, dir_name IN VARCHAR2, sql_id IN VARCHAR2, min_active_sessions IN NUMBER := 0, min_response_time IN NUMBER := 0);
Table 17-19 INSERT_SQL_DIRECTIVE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the task this directive applies to. If the value is |
|
|
Name of the directive. All directives must be given unique names. |
|
|
Identifies which SQL statement to filter. A valid value contains exactly 13 characters from '0' to '9' and 'a' to 'z'. |
|
|
Minimal number of active sessions for the SQL. If a SQL action has less than this number, it is filtered from the ADDM result. |
|
|
Minimal value for response time of the SQL (in microseconds). If the SQL had lower response time, it is filtered from the ADDM result. |
A new ADDM task is created to analyze a local instance. However, it has special treatment for SQL with id 'abcd123456789'. The result of GET_REPORT shows only actions for that SQL (actions to tune the SQL, or to investigate application using it) if the SQL is responsible for at least 2 average active sessions during the analysis period, and the average response time was at least 1 second.
var tname VARCHAR2(60);
BEGIN
DBMS_ADDM.INSERT_SQL_DIRECTIVE(
NULL,
'my SQL directive',
'abcd123456789',
2,
1000000);
:tname := 'my_instance_analysis_mode_task';
DBMS_ADDM.ANALYZE_INST(:tname, 1, 2);
END;
To see a report containing all actions regardless of the directive:
SELECT DBMS_ADVISOR.GET_TASK_REPORT(:tname, 'TEXT', 'ALL') FROM DUAL;