oracle数据库CPU过高问题定位、分析（总结）

一、为ASH、AWR、ADDM、SPA分别开启会话，并使用会话跟踪：

--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;SQL> select dbms_addm.get_report('TASK_366') from dual;SQL> alter session set events '10046 trace name context off';

--SPA

SQL> alter session set events '10046 trace name context forever,level 12';SQL> begin  dbms_sqltune.create_sqlset(  sqlset_name=>'cpu_2',  description => 'High cpu read tuning set');  end;  /  2    3    4    5    6  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,58,NULL,NULL,'cpu_time',NULL,NULL,NULL,10)) x;  --  dbms_sqltune.load_sqlset(sqlset_name=>'cpu_2',populate_  2    3    4    5    6    7  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;SQL> select DBMS_SQLPA.report_analysis_task(:sts_task,'HTML','ALL','ALL') from dual;SQL> alter session set events '10046 trace name context off';

二、通过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

三、查看解析结果

--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.09       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         14          0           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         74          0           0Execute   1915      0.16       0.16          5       2294        322          91Fetch     4988      0.04       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

四、分析结果

通过上述结果可以发现，

query(buffer)的使用消耗优先排列为：ash=>addm=>spa=>awr

disk（磁盘）的使用消耗优先排列为：addm=>ash=>spa=>addm

cpu的使用消耗优先排列为：addm=>spa=>awr=>ash

生产环境定位问题时，可以将上述结果作为参考，避免CPU过高时仍使用cpu消耗过大的工具；【实验中ash的取时范围为1小时，生产环境中往往使用ash皆是查看几分钟的区间，因此ash的性能消耗是最低的】

五、关于性能视图与会话跟踪，性能消耗相对更低，但可阅读性相比上述工具略差，可根据个人习惯进行选择