DB服务器CPU 使用率高问题 - 简单分析
一、问题描述
客户反馈数据库主机CPU使用率100%，load 负载很高，应用操作卡顿。
二、故障排查
1. 检查当前session等待情况
SQL> select username,module,event,sql_id from gv$session where status='ACTIVE' and username is not null order by event;
复制
分析：sql_id 为02f2031y734tb 的SQL语句，等待事件为latch: cache buffers chains。该等待事件通常是由大量逻辑读的SQL语句导致，统消耗量大量的CPU资源。
临时解决办法： 将ReportingServicesService.exe 程式执行的SQL先kill。
2. 查看AWR报告，CPU和逻辑读较高的SQL语句
分析：SQL_Id 02f2031y734tb 的SQL语句，单次执行逻辑读非常高，CPU资源消耗严重，定位为该SQL语句导致。
3. 查看SQL执行计划
询问开发人员，该SQL不是新写的，所以怀疑是执行计划变了，导致SQL执行效率下降，我们通过awrsqlrpt.sql脚本，选择时间长一点，获取02f2031y734tb 的SQL语句的执行计划。
plan1 单次执行时间：2310903ms 单词逻辑读 291175853
plan2 单次执行时间：22569ms 单词逻辑读1001246
2个执行计划差异较大，我们选择绑定执行计划的方式，来优化SQL语句。
3. 绑定执行计划
因为数据库版本是10.2.0.3，所以通过coe_load_sql_profile.sql脚本绑定执行计划。
SQL> @coe_load_sql_profile.sql
.....
.....
1992 NO_ACCESS(@"SEL$9918AA96" "CLONE"@"SEL$111")
1993 LEADING(@"SEL$9918AA96" "TC_SRX_FILE"@"SEL$107" "TC_SRY_FILE"@"SEL$107" "JS"@"SEL$108" "CLONE"@"SEL$111")
1994 USE_NL(@"SEL$9918AA96" "TC_SRY_FILE"@"SEL$107")
1995 USE_NL(@"SEL$9918AA96" "JS"@"SEL$108")
1996 USE_HASH(@"SEL$9918AA96" "CLONE"@"SEL$111")
1997 INDEX_RS_ASC(@"SEL$44DFBF36" "TC_SRX_FILE"@"SEL$100" ("TC_SRX_FILE"."TC_SRX02" "TC_SRX_FILE"."TC_SRX05"))
1998 INDEX_RS_ASC(@"SEL$44DFBF36" "TC_SRY_FILE"@"SEL$100" ("TC_SRY_FILE"."TC_SRY01" "TC_SRY_FILE"."TC_SRY02"))
1999 NO_ACCESS(@"SEL$44DFBF36" "JS"@"SEL$101")
2000 NO_ACCESS(@"SEL$44DFBF36" "CLONE"@"SEL$104")
2001 LEADING(@"SEL$44DFBF36" "TC_SRX_FILE"@"SEL$100" "TC_SRY_FILE"@"SEL$100" "JS"@"SEL$101" "CLONE"@"SEL$104")
DECLARE
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 13
ORA-06512: at line 27
复制结论：很遗憾，执行抛出ORA-06532: Subscript outside of limit 错误，绑定失败。
根据coe_load_sql_profile.sq脚本内容，可以看到抛出该错误，可以重建 type sys.sqlprof_attr 。 但是建议是仅在测试和一次性系统中可以修改，考虑我们是正式环境，为了避免修改后不知道的的潜在风险，我们暂时不修改。
-- 默认是VARRAY(2000)
SQL> desc sys.sqlprof_attr
sys.sqlprof_attr VARRAY(2000) OF VARCHAR2(500)
复制因为是 10g版本，也没有spm方式绑定执行计划，我们选择通过重新收集统计信息的方式，，清除shared pool中缓存的执行计划，重新解析看看执行计划会不会变好。
