Use of ORACLE 10g addm diagnostic tool

1. ADDM provides an overall optimization solution. Based on AWR snapshots for a period of time, you can perform ADDM analysis to diagnose possible database bottlenecks during this period. It does not need to be installed, and the analysis results are automatically provided. statspack needs to be installed, and the generated analysis report needs to be analyzed by DBA.
1. the ADDM tool requires that the system parameter statistics_level be set to typical (recommended) or all, and the 10g system is typical by default.
SQL> ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;
2. Get the ID of two AWR snapshots first
SQL> select snap_id from (SELECT * FROM dba_hist_snapshot ORDER BY snap_id desc) where rownum <= 2;
SNAP_ID
----------
261
262
3. Create an optimization task and execute
SQL> @ D: \ oracle \ product \ 10.2.0 \ db_1 \ RDBMS \ ADMIN \ addmrpt. SQL
Enter the begin_snap value: 261 -- enter the value based on the preceding query results
Input end_snap value: 262
Enter the value of report_name: <enter uses the default file name>
Report written to D: \ Program Files \ addmrpt_0000261_262.txt. Check the file content.
In addition, in the RAC environment, you can execute addmrpti. SQL. The execution of this script requires more requirements for entering the DB ID and instance ID.
Ii. STA (SQL Tuning Advisor)
ADDM provides the diagnostic results and provides optimization suggestions. DBA can use the recommended STA statement optimization. To use the STA, make sure that the optimizer is in CBO mode.
(-- Query Optimizer mode:
Select * from v $ sys_optimizer_env;
-- Modify the mode (the default value of 10 Gb is all_rows ):
SQL> ALTER SESSION SET OPTIMIZER_MODE = all_rows;
The value of the mode must be between first_rows_1000, first_rows_100, first_rows_10, first_rows_1, first_rows, all_rows, choose, and rule .)
1. Create and execute optimization tasks
SQL>
DECLARE
My_task_name VARCHAR2 (30 );
My_sqltext CLOB;
BEGIN
My_sqltext: = 'select * from scott. t_tmp_billlog ';
-- Create a task
My_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (
SQL _text => my_sqltext,
User_name => 'Scott ',
Scope => 'computing ',
Time_limit => 60,
Task_name => 'test _ ADDM_task ',
Description => 'Task to tune a query on a specified product ');
-- Execute the task
Dbms_sqltune.Execute_tuning_task (task_name => 'test _ ADDM_task ');
END;
/
-- DBMS_SQLTUNE.CREATE_TUNING_TASK is a function used to create an optimization task.
FUNCTION create_tuning_task (
SQL _text IN CLOB, -- Statement to be optimized
Bind_list IN SQL _binds: = NULL,
User_name IN VARCHAR2: = NULL, -- the user through which the statement is executed
Scope IN VARCHAR2: = SCOPE_COMPREHENSIVE, -- Optimized range (limited or comprehensive)
Time_limit in number: = TIME_LIMIT_DEFAULT, -- Time Limit of the optimization process
Task_name IN VARCHAR2: = NULL, -- Optimized Task Name
Description IN VARCHAR2: = NULL)
RETURN VARCHAR2;
 
-- PROCEDURE execute_tuning_task (task_name IN VARCHAR2); -- it is the method for optimization.
2. Use the report_tuning_task function to view the optimization suggestions:
SQL> set serveroutput on;
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('test _ ADDM_task ') FROM DUAL;
3. To run the task again, delete the task:
-- PROCEDURE drop_tuning_task (task_name IN VARCHAR2 );
SQL> exec dbms_sqltune.drop_tuning_task (task_name => 'test _ ADDM_task ');
For more methods, see SYS. dbms_sqltune.

Author "srsunbing"