Shulou(Shulou.com)06/01 Report--

First, open a session for ASH, AWR, ADDM and SPA, respectively, and use session tracking:

-- ASH

Alter session set events' 10046 trace name context forever,level 12 session altered.SQL > @ ashrptSQL > alter session set events' 10046 trace name context off'

-- AWR

SQL > alter session set events' 10046 trace name context forever,level 12 session altered.SQL > @ awrrptSQL > alter session set events' 10046 trace name context off'

-- ADDM

SQL > alter session set events' 10046 trace name context forever,level 12 session altered.SQL > var task_name varchar2 (30); SQL > exec DBMS_ADDM.ANALYZE_DB (: task_name,57,58) PL/SQL procedure successfully completed.SQL > print: task_nameTASK_NAME----TASK_366SQL > set long 1000000 pagesize 0 from dual > select dbms_addm.get_report ('TASK_366') SQL SQL > alter session set events' 10046 trace name context off'

-- SPA

SQL > alter session set events' 10046 trace name context forever,level 12 words > begin dbms_sqltune.create_sqlset (sqlset_name= > 'cpu_2', description = >' High cpu read tuning set'); end; / 23 456 PL/SQL procedure successfully completed.SQL > declare base_cur dbms_sqltune.sqlset_cursor; begin open base_cur for select value (x) from table (DBMS_SQLTUNE.select_workload_repository (57 and 58) -- dbms_sqltune.load_sqlset (sqlset_name= > 'cpu_2',populate_ 23 4567 cursor = > base_cur); end; / 8 9 PL/SQL procedure successfully completed.SQL > variable sts_task VARCHAR2 (64); SQL > EXEC: sts_task: = DBMS_SQLPA.CREATE_ANALYSIS_TASK (sqlset_name= >' cpu_2',order_by= > 'cpu_time',description= >' process workload ordered by cpu_time') PL/SQL procedure successfully completed.SQL > EXEC DBMS_SQLPA.execute_analysis_task (task_name= >: sts_task,execution_type= > 'CONVERT SQLSET',execution_params= > dbms_advisor.arglist (' TIME_LIMIT','1800')); PL/SQL procedure successfully completed.SQL > set long 1000000 pagesize 0 position SQL > select DBMS_SQLPA.report_analysis_task (: sts_task,'HTML','ALL','ALL') from dual;SQL > alter session set events' 10046 trace name context off'

Second, parse the trace file through tkprof

Tkprof orcl_ora_21955.trc / home/oracle/ash.txt

Tkprof orcl_ora_22077.trc / home/oracle/awr.txt

Tkprof orcl_ora_22087.trc / home/oracle/addm.txt

Tkprof orcl_ora_22092.trc / home/oracle/spa.txt

3. View the resolution results

-- ASH

Call count cpu elapsed disk query current rows--Parse 1041 0.39 0.39 0 252 0 0Execute 1165 2.42 2.43 0 3352 7 12Fetch 2426 0.07 81 4688 126 1848- -total 4632 2.90 2.90 81 8292 133 1860

-- AWR

Call count cpu elapsed disk query current rows--Parse 1386 0.19 0.19 0 140 0Execute 3630 0.81 0.81 1 1213 8 1Fetch 9177 0.30 0.41 195 118457 3421 7597- -total 14193 1.31 1.42 196 119684 3429 7598

-- ADDM

Call count cpu elapsed disk query current rows--Parse 555 0.03 0.03 0 740 0Execute 1915 0.16 0.16 5 2294 322 91Fetch 4988 0.03 0 10032 3 4861- -total 7458 0.24 0.23 5 12400 325 4952

-- SPA

Call count cpu elapsed disk query current rows--Parse 1311 0.11 0.12 0 507 0 0Execute 4131 0.41 0.51 15 7701 850 232Fetch 10741 0.13 0.16 16 32068 4 10646- -total 16183 0.66 0.80 31 40276 854 10878

IV. Analysis results

Through the above results, it can be found that

The priority of consumption of query (buffer) is ash= > addm= > spa= > awr.

The consumption priority of disk (disk) is addm= > ash= > spa= > addm.

The priority of consumption of cpu is: addm= > spa= > awr= > ash.

When locating problems in the production environment, you can use the above results as a reference to avoid using tools that consume too much cpu when the CPU is too high. [in the experiment, the time range of ash is 1 hour, and ash is often used in the production environment to view the interval of a few minutes, so the performance consumption of ash is the lowest]

Fifth, with regard to performance view and session tracking, the performance consumption is relatively low, but the readability is slightly worse than the above tools, so you can choose according to your personal habits.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.