You can run ADDM using the DBMS_ADDM package. This package allows you to run ADDM in the different available modes, to view repots, insert directives to findings, and delete directives for findings.
To run the DBMS_ADDM package, you must have the ADVISOR privilege.
Note: You can also use Oracle Enterprise Manager to run ADDM.
To analyze all instances of Oracle RAC databases, run ADDM in the database mode. To enable the database mode, you should use the DBMS_ADDM.ANALYZE_DB procedure.
www.transcender.com
54
Syntax
DBMS_ADDM.ANALYZE_DB(task_name, begin_snapshot, end_snapshot, db_id);
The task_name parameter specifies the name of the analysis task to be created. The begin_snapshot and end_snapshot parameters specify the range of snapshots to be analyzed or the time period to be analyzed. The db_id parameter is the database identifier of the database to be analyzed. The default value of this parameter is the database identifier of the local database.
To analyze a single instance of the database, you should run ADDM in the instance mode. To enable ADDM in the instance mode, use the DBMS_ADDM.ANALYZE_INST procedure.
Syntax
DBMS_ADDM.ANALYZE_INST(task_name, begin_snapshot, end_snapshot, instance_number, db_id);
The instance_number parameter specifies the instance to be analyzed. If unspecified, the default value is the current instance to which you are connected. All the other parameters are the same as for the DBMS_ADDM.ANALYZE_DB procedure.
If you want to analyze only some instances of a database, you can run ADDM in partial mode. In partial mode, ADDM analyzes only the specified instances. Instances to be analyzed are indicated by their associated instance_number parameters. To initialize ADDM in partial mode, run the
DBMS_ADDM.ANALYZE_PARTIAL procedure.
Syntax
DBMS_ADDM.ANALYZE_PARTIAL(task_name, instance_numbers, begin_snapshot, end_snapshot, db_id);
The instance_numbers parameter specifies the instances to be analyzed. The parameter is specified as a list of instance numbers, separated by commas.
To display the findings of ADDM, you can use the DBMS_ADDM.GET_REPORT function. The return type of the function is CLOB, formatted to fit a line size of 80.
Syntax
DBMS_ADDM.GET_REPORT (task_name, RETURN CLOB);
For example, to display reports despite any directives for the task task1, you would use the following statement:
SELECT DBMS_ADVISOR.GET_TASK_REPORT('task1', 'TEXT', 'ALL') FROM DUAL;
www.transcender.com
55
Inserting Directives
To limit reporting of specific types of findings, you can create directives and apply them to ADDM tasks.
Note: A directive can be created for a specific task or for all ADDM tasks globally. The directive is applied to all ADDM tasks created after the directive is specified. It does not affect pre-existing tasks. Such directives are called system directives. Directives can suppress ADDM findings related to specific parameters, SQL statements, or segments.
You can create a finding directive using the INSERT_FINDING_DIRECTIVE procedure.
Syntax
DBMS_ADDM.INSERT_FINDING_DIRECTIVE (task_name, dir_name, finding_name, min_active_sessions, min_perc_impact);
The task_name and finding_name parameters specify the name of the task and the ADDM finding with which the directive is assocaiated, respectively. The dir_name parameter specifes a unique name for the directive.
The min_active_sessions and min_perc_impact parameters define the criteria for the ADDM finding to be a part of ADDM results. If the minimum number of active sessions is less than the value specified in the min_active_sessions parameter, the ADDM finding will not be included in the ADDM results. The min_perc_impact parameter specifies the mimum time that a particular finding should have taken when compared to the total time. If the minimum percentage of time taken by the finding is less than the percentage specified in the min_perc_impact parameter, the finding will not be included in the overall ADDM analysis. For example, if you have set the min_perc_impact parameter to 10 and the database time is 10 hours, then any ADDM finding that takes less than one hour, which is 10 percent of 10 hours, will not be included in the ADDM analysis report.
If you want to stop ADDM from suggesting actions regarding a specific system parameter, you can create parameter directives. Parameter directives are created using the
DBMS_ADDM.INSERT_PARAMETER_DIRECTIVE procedure. After you create a parameter directive, all suggestions containing actions associated with the specified parameter will be omitted from the report.
Syntax
DBMS_ADDM.INSERT_PARAMETER_DIRECTIVE (task_name, dir_name, parameter_name);
The parameter_name parameter specifies the parameter for which suggestions will not be reported.
If you do not want ADDM to suggest running the Segment Advisor as a solution, you can create a segment directive. This directive is created using the DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE procedure. You can use this directive to suppress the findings related to specific users, partitions, or segments.
www.transcender.com
56
Syntax
DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE (task_name, dir_name, owner_name, object_name, sub_object_name);
dir_name specifies a unique name of the directive to be created.
owner_name specifies the name of the user who owns the segment on which you are performing the filter action.
object_name specifies the particular object that will be filtered.
sub_object_name specifies the name of the sub-object, such as a partition or sub-partition within the object being filtered.
object_number specifies the unique ID number to identify the specific object or sub-object to being filtered.
When you execute the INSERT_SEGMENT_DIRECTIVE procedure, you need to ensure that the task has been reset to its initial state; otherwise, the code will fail.
If you do not want ADDM to display any findings for a specific SQL statement, you should create a SQL directive. To create a SQL directive, you can use the DBMS_ADDM.INSERT_SQL_DIRECTIVE procedure.
Syntax
DBMS_ADDM.INSERT_SQL_DIRECTIVE (task_name, dir_name, sql_id, min_active_sessions, min_response_time);
Most of the parameters used in the INSERT_SQL_DIRECTIVE procedure have been covered in the procedures discussed earlier, except for the sql_id parameter and the min_response_time
parameter. The sql_id parameter specifies the unique SQL ID number to identify the SQL statement to be filtered. The min_response_time parameter specifies the minimum response time for the SQL statement required for it to be included in the ADDM analysis. This time is measured in microseconds.
Deleting ADDM Tasks and Directives
To delete a task created for running ADDM in instance mode, you should use the DBMS_ADDM.DELETE procedure. For tasks created to run ADDM in database or partial mode, this procedure deletes the local tasks associated with the main task.
www.transcender.com
57
Syntax
DBMS_ADDM.DELETE (task_name);
To delete a finding directive, use the DBMS_ADDM.DELETE_FINDING_DIRECTIVE procedure:
DBMS_ADDM.DELETE_FINDING_DIRECTIVE(task_name, dir_name);
To delete a parameter directive, use the DBMS_ADDM.DELETE_PARAMETER_DIRECTIVE procedure. It removes system directive for parameters. Subsequently created ADDM tasks are not affected by the directive.
DBMS_ADDM.DELETE_PARAMETER_DIRECTIVE (task_name, dir_name);
To delete a segment directive, use the DBMS_ADDM. DELETE_SEGMENT_DIRECTIVE procedure:
DBMS_ADDM.DELETE_SEGMENT_DIRECTIVE (task_name, dir_name);
To delete a SQL directive, use the DBMS_ADDM.DELETE_SQL_DIRECTIVE procedure:
DBMS_ADDM.DELETE_SQL_DIRECTIVE (task_name, dir_name);