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

2025-04-15 技术教程

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

--ASH

altersessionsetevents'10046tracenamecontextforever,level12';Sessionaltered.SQL>@ashrptSQL>altersessionsetevents'10046tracenamecontextoff';

--AWR

SQL>altersessionsetevents'10046tracenamecontextforever,level12';Sessionaltered.SQL>@awrrptSQL>altersessionsetevents'10046tracenamecontextoff';

--ADDM

SQL>altersessionsetevents'10046tracenamecontextforever,level12';Sessionaltered.SQL>vartask_namevarchar2(30);SQL>execDBMS_ADDM.ANALYZE_DB(:task_name,57,58);PL/SQLproceduresuccessfullycompleted.SQL>print:task_nameTASK_NAME--------------------------------------------------------------------------------TASK_366SQL>setlong1000000pagesize0;SQL>selectdbms_addm.get_report('TASK_366')fromdual;SQL>altersessionsetevents'10046tracenamecontextoff';

--SPA

SQL>altersessionsetevents'10046tracenamecontextforever,level12';SQL>begindbms_sqltune.create_sqlset(sqlset_name=>'cpu_2',description=>'Highcpureadtuningset');end;/23456PL/SQLproceduresuccessfullycompleted.SQL>declarebase_curdbms_sqltune.sqlset_cursor;beginopenbase_curforselectvalue(x)fromtable(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_234567cursor=>base_cur);end;/89PL/SQLproceduresuccessfullycompleted.SQL>variablests_taskVARCHAR2(64);SQL>EXEC:sts_task:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name=>'cpu_2',order_by=>'cpu_time',description=>'processworkloadorderedbycpu_time');PL/SQLproceduresuccessfullycompleted.SQL>EXECDBMS_SQLPA.execute_analysis_task(task_name=>:sts_task,execution_type=>'CONVERTSQLSET',execution_params=>dbms_advisor.arglist('TIME_LIMIT','1800'));PL/SQLproceduresuccessfullycompleted.SQL>setlong1000000pagesize0;SQL>selectDBMS_SQLPA.report_analysis_task(:sts_task,'HTML','ALL','ALL')fromdual;SQL>altersessionsetevents'10046tracenamecontextoff';

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

callcountcpuelapseddiskquerycurrentrows-----------------------------------------------------------------------Parse10410.390.39025200Execute11652.422.4303352712Fetch24260.090.078146881261848-----------------------------------------------------------------------total46322.902.908182921331860

--AWR

callcountcpuelapseddiskquerycurrentrows-----------------------------------------------------------------------Parse13860.190.1901400Execute36300.810.811121381Fetch91770.300.4119511845734217597-----------------------------------------------------------------------total141931.311.4219611968434297598

--ADDM

callcountcpuelapseddiskquerycurrentrows-----------------------------------------------------------------------Parse5550.030.0307400Execute19150.160.165229432291Fetch49880.040.0301003234861-----------------------------------------------------------------------total74580.240.235124003254952

--SPA

callcountcpuelapseddiskquerycurrentrows-----------------------------------------------------------------------Parse13110.110.12050700Execute41310.410.51157701850232Fetch107410.130.161632068410646-----------------------------------------------------------------------total161830.660.80314027685410878

四、分析结果

通过上述结果可以发现，

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

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

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

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

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