[Oracle]-Performance optimization tool (3)-ADDM

ADDM examines and analyzes the data that the AWR obtains to determine possible problems in the Oracle database and gives recommendations for optimization.

Here's how to get ADDM:

@?/rdbms/admin/addmrpt.sql

Below you can see an example:

--First step: Create a table for the Test drop TABLE t cascade constraints purge;create table T as SELECT * from dba_objects;--Second step: Snapshot exec Dbms_workloa D_repository.create_snapshot (); --Step three: Simulate the declare v_var number;    BEGIN for N in 1..10000 LOOP select COUNT (*) to v_var from T; END LOOP; end;/---Fourth step: re-snapshot exec dbms_workload_repository.create_snapshot (); --Fifth step: Create an Optimization diagnostic task and execute--(1) Get the Id:select snap_id from (SELECT * to Dba_hist_snapshot ORDER by snap_id desc) where Rown    Um <=2;--(2) creates the optimization task and executes: DECLARE task_name VARCHAR2 (): = ' addm_02 ';    Task_desc VARCHAR2 (+): = ' ADDM Feature Test '; TASK_ID number;    BEGIN dbms_advisor.create_task (' ADDM ', task_id, task_name, task_desc, NULL);    Dbms_advisor.set_task_parameter (Task_name, ' start_snapshot ', 2033);    Dbms_advisor.set_task_parameter (Task_name, ' end_snapshot ', 2034);    Dbms_advisor.set_task_parameter (Task_name, ' INSTANCE ', 1);    Dbms_advisor.set_task_parameter (Task_name, ' db_id ', 977587123); Dbms_advisor.execute_task (Task_name); end;/--Sixth step: View the Optimization recommendations results--the notification function Dbms_advisor.get_task_report can be optimized for recommendations. Set pagesize 0set linesize 121spool d:\addm_rpt.htmlSET LONG 1000000 pagesize 0 longchunksize 1000COLUMN Get_clob FORMAT A 80SELECT dbms_advisor.get_task_report (' addm_02 ', ' TEXT ', ' all ') from Dual;spool off

The resulting ADDM are as follows:

          The ADDM report for Task ' task _4125 '----------------------Analysis window----The AWR snapshot range from 1908 to 1952. Period from 1 June-February-14 08.19.56 morning start session in 1 June-February-14 10.00.37 PM End Analysis Target----database ' test11g ' (DB ID 977587123). Database version 11.2.0.1.0. ADDM performs an analysis of the instance test11g, which is numbered 1 and runs on liangjb-pc. Activity---------Total database time during the analysis window is 26,244 seconds. The average number of active sessions is. 53. Find Results Summary------describes the percentage of active session proposal activity------------------1 row lock waits. 52 | 97.7622 Top-level SQL statements. 52 | 96.742~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ finding results and recommendations--------Finding results 1: The number of row lock waits affected is. 52 active sessions, accounting for total activity 97.76\%.   -------------------------------found that the SQL statement is in line lock waiting.   Recommendation 1: Application analysis estimates the benefits of. 39 active sessions, which account for the 72.36\% of the total activity. --------------------------------operation at INDEX "LJB". Serious row contention was detected in Gender_idx "(Object ID 110057).      Tracks the cause of row contention in the application logic using the specified blocking SQL statement.   A database object with a correlation object ID of 110057. Principle sql_id SQL statement for "CAFV93454T4JV" at the row lockBe blocked.         The related object sql_id is a CAFV93454T4JV SQL statement.   INSERT into t values (' M ', +, ' young ', ' TTT ') principle the session with ID 130 and serial number 423 (in instance number 1) is the 98% blocking session that constitutes the optimization recommendation in this recommendation.   Recommendation 2: Application analysis estimates the benefits of. 14 active sessions, which account for the 25.4\% of the total activity. -------------------------------operation on TABLE "LJB. Critical row contention was detected in T "(Object ID 110056).      Tracks the cause of row contention in the application logic using the specified blocking SQL statement.   A database object with a correlation object ID of 110056.      The principle sql_id SQL statement for "AYCGHY7DBZJA1" is blocked at the row lock.         The related object sql_id is a AYCGHY7DBZJA1 SQL statement.   The delete from T WHERE gender= ' M ' principle has a session that has ID 130 and serial number 423 (in instance number 1) is the 100% blocking session that constitutes the optimization recommendation in this recommendation.      Symptoms that result in a lookup:------------wait for the class "application" to consume a large amount of database time. The affected is. 52 active sessions, which account for the 97.76\% of the total activity. Finding results 2: Top-level SQL statements are affected by. 52 active sessions, which account for the 96.74\% of the total activity. -------------------------------found that SQL statements consume a large amount of database time.   These statements provide a great opportunity to improve performance.   The proposed 1:sql optimization estimate yields a. 38 active sessions, which account for the 71.45\% of the total activity. The--------------------------------operation studies the INSERT statement (sql_id is "cafv93454t4jv") to determine whether performance can be improved. You can use this sql_id's ASH report to supplement the information given here。         The related object sql_id is a CAFV93454T4JV SQL statement. INSERT into t values (' M ', +, ' young ', ' TTT ') principle SQL spends only 0% of its database time on CPU, I/O, and cluster waits. Therefore, SQL tuning guidance does not apply to this scenario.   Review your SQL performance data to find possible improvements.   Principle this SQL database time is composed of the following parts: SQL execution accounted for 100%, syntax analysis accounted for 0%, PL/SQL execution accounted for 0%, Java execution accounted for 0%.   The principle sql_id the SQL statement for "CAFV93454T4JV" was performed 1 times, averaging 17,640 seconds per execution. The principle waits for the event "Enq:tx-row lock Contention" (in The Wait Class "application") consumes 100% of the database time (the database time is processed with sql_id "cafv93454t4jv   The time that was spent in the SQL statement.   The proposed 2:sql optimization estimate yields a. 13 active sessions, which account for the 25.29\% of the total activity. --------------------------------Operations Study The DELETE statement (sql_id is "AYCGHY7DBZJA1") to determine if performance can be improved.      You can use this sql_id's ASH report to supplement the information given here.         The related object sql_id is a AYCGHY7DBZJA1 SQL statement. Delete from T where gender= ' M ' principle SQL spends only 0% of its database time on CPU, I/O, and cluster waits. Therefore, SQL tuning guidance does not apply to this scenario.   Review your SQL performance data to find possible improvements.   Principle this SQL database time is composed of the following parts: SQL execution accounted for 100%, syntax analysis accounted for 0%, PL/SQL execution accounted for 0%, Java execution accounted for 0%. The sql_id principle is "aycghy7dbzja1" SQLThe statement executes 1 times, averaging 7,917 seconds per execution. The principle waits for the event "Enq:tx-row lock Contention" (in The Wait Class "application") consumes 100% of the database time (the database time is processed with sql_id "AYCGHY7DBZJA1 The time that was spent in the SQL statement. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ additional Information----various information----waiting class "Commit" does not consume a large amount of database time. Waiting for class "concurrency" does not consume a large amount of database time. Waiting for the class "configuration" does not consume a large amount of database time. Waiting for the Class "network" does not consume a large amount of database time. Waiting for the class "User I/O" does not consume a large amount of database time. Session connections and disconnected calls do not consume a large amount of database time. Hard parsing of SQL statements does not consume a large amount of database time. During 99% of the analysis period, the Maintenance window for the database is active.