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

ADDM checks and analyzes the data obtained by AWR to identify possible problems in the Oracle database and provides optimization suggestions.

The method for obtaining ADDM is as follows:

@?/rdbms/admin/addmrpt.sql

The following is an example:

-- Step 1: create a test table drop table t cascade constraints purge; create table t as select * FROM dba_objects; -- Step 2: Snapshot exec dbms_workload_repository.create_snapshot (); -- Step 3: simulate DECLARE v_var number; begin for n IN 1 .. 10000 LOOP select count (*) into v_var from t; end loop; END;/--- Step 4: Snapshot exec dbms_workload_repository.create_snapshot () again; -- Step 5: create an optimization diagnosis task and execute -- (1) first obtain the ID of the two snapshots: select snap_id from (SELECT * FROM dba_his T_snapshot order by snap_id desc) where rownum <= 2; -- (2) create an optimization task and run: DECLARE task_name VARCHAR2 (30): = 'addm _ 02 '; task_desc VARCHAR2 (30): = 'addm Feature test'; task_id NUMBER; BEGIN dbms_advisor.create_task ('addm', task_id, task_name, task_desc, null); assign (task_name, 'Start _ SNAPSHOT ', 2033); dbms_advisor.set_task_parameter (task_name, 'end _ SNAPSHOT', 2034); dbms_advisor.se T_task_parameter (task_name, 'instance', 1); dbms_advisor.set_task_parameter (task_name, 'db _ id', 977587123); dbms_advisor.execute_task (task_name); END;/-- Step 6: view optimization suggestion result-the notification function dbms_advisor.get_task_report can get the optimization suggestion result. Set pagesize 0 set linesize 121 spool d: \ addm_rpt.htmlSET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000 COLUMN get_clob FORMAT a80SELECT partition ('addm _ 02 ', 'text', 'all') from dual; spool off

The generated ADDM is as follows:

The ADDM report of Task 'Task _ 000000' -------------------- analysis time period ---- AWR snapshot range is from 4125 to 1908. The analysis target-database 'test11g' (db id: 977587123) is ended from the morning of 16-2-14 08.19.56 on the afternoon of 16-2-14 10.00.37 ). The database version is 11.2.0.1.0. ADDM analyzes the instance test11g. The instance id is 1 and runs in LIANGJB-PC. Activity during the analysis period --------- the total database time is 26244 seconds. The average number of active sessions is. 53. Result summary ------ percentage of the activity's session proposal activity --------- ------ --- Number of lock waits in one row. 52 | 97.7622 top-level SQL statements. 52 | 96.742 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Search results and proposal -------- search result 1: The number of row lock Waits is affected by 52 active sessions, accounting for 97.76 of total activities. ------------------------------- The SQL statement is in the row lock wait state. Proposal 1: The application analysis estimates 39 activity sessions, accounting for 72.36 Of the total activity sessions. ------------------------------ The operation detected serious row contention in INDEX "LJB. GENDER_IDX" (Object ID: 110057. Use the specified blocking SQL statement to track the cause of row contention in the application logic. A database object with a correlation Object ID of 110057. The SQL statement with the principle SQL _ID "cafv93454t4jv" is blocked on the row lock. The SQL statement of the related object SQL _ID is cafv93454t4jv. Insert into t values ('M', 78, 'Young ', 'ttt') has the principle of ID 130 and serial number 423 (in instance 1) the session is the 98% blocking session that constitutes the optimization suggestion in this proposal. Proposal 2: application analysis estimates 14 activity sessions, accounting for 25.4 of total activity sessions. ----------------------------- The operation detected severe row contention in TABLE "LJB. T" (Object ID: 110056. Use the specified blocking SQL statement to track the cause of row contention in the application logic. A database object with a correlation Object ID of 110056. The SQL statement with the principle SQL _ID "aycghy7dbzja1" is blocked on the row lock. The SQL statement of the related object SQL _ID aycghy7dbzja1. The delete from t where gender = 'M' principle has the session ID 130 and the serial number 423 (in instance 1) as the 100% blocking session that constitutes the optimization recommendation in this proposal. Failure in search results: ------------ waiting class "application" consumes a lot of database time. 52 active sessions were affected, accounting for 97.76 of total active sessions. Result 2: The top-level SQL statements are affected by 52 active sessions, accounting for 96.74 Of the total activities. ------------------------------- It is found that SQL statements consume a lot of database time. These statements provide an excellent opportunity to improve performance. Proposal 1: The estimated revenue of SQL optimization is. 38 active sessions, accounting for 71.45 of total activities. ------------------------------ Operation research INSERT Statement (SQL _ID is "cafv93454t4jv") to determine whether it can improve performance. You can use the ASH report of this SQL _ID to supplement the information provided here. The SQL statement of the related object SQL _ID is cafv93454t4jv. Insert into t values ('M', 78, 'Young ', 'ttt, i/O and cluster wait time only account for 0% of the database time. Therefore, SQL optimization guidance is not applicable to this situation. Check SQL performance data to find possible improvement methods. The database time of this SQL statement is composed of the following parts: SQL Execution accounts for 100%, syntax analysis accounts for 0%, PL/SQL Execution accounts for 0%, and Java execution accounts for 0%. The SQL statement with the principle SQL _ID "cafv93454t4jv" is executed once, and the average execution time is 17640 seconds. Principle wait event "enq: TX-row lock contention" (waiting class "Application) 100% of the database time consumed (this database time is used to process SQL statements with SQL _ID "cafv93454t4jv ). Proposal 2: The estimated revenue of SQL optimization is. 13 activity sessions, accounting for 25.29 Of the total activity. ------------------------------ Operation research DELETE Statement (SQL _ID is "aycghy7dbzja1") to determine whether it can improve performance. You can use the ASH report of this SQL _ID to supplement the information provided here. The SQL statement of the related object SQL _ID aycghy7dbzja1. Delete from t where gender = 'M' principle SQL takes only 0% of the database time on CPU, I/O, and cluster wait. Therefore, SQL optimization guidance is not applicable to this situation. Check SQL performance data to find possible improvement methods. The database time of this SQL statement is composed of the following parts: SQL Execution accounts for 100%, syntax analysis accounts for 0%, PL/SQL Execution accounts for 0%, and Java execution accounts for 0%. The SQL statement with the principle SQL _ID "aycghy7dbzja1" is executed once, and the average execution time is 7917 seconds. Principle wait event "enq: TX-row lock contention" (waiting class "Application) 100% of the database time consumed (this database time is used to process SQL statements with SQL _ID "aycghy7dbzja1 ). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional information-various information-Wait class "Submit" does not consume a large amount of database time. The wait class "concurrency" does not consume a lot of database time. The wait class "configuration" does not consume a lot of database time. Wait class "network" does not consume a lot of database time. Wait class "user I/O" does not consume a lot of database time. The call of session connection and disconnection does not consume a lot of database time. The hard syntax analysis of SQL statements does not consume a lot of database time. During the 99% period of the analysis period, the database maintenance window is active.