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

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

--ASH

alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> @ashrpt
SQL> 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> @awrrpt
SQL> 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_name
TASK_NAME
--------------------------------------------------------------------------------
TASK_366
SQL> 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           0
Execute   1165      2.42       2.43          0       3352          7          12
Fetch     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           0
Execute   3630      0.81       0.81          1       1213          8           1
Fetch     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           0
Execute   1915      0.16       0.16          5       2294        322          91
Fetch     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           0
Execute   4131      0.41       0.51         15       7701        850         232
Fetch    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的性能消耗是最低的】

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