Oracle性能优化- 数据库服务器 CPU 使用率 100%

问题：
凌晨1 点 40 分，监控发来告警邮件，数据库服务器 CPU 使用率超过 98% ， 达到100% ，持续1 个多小时恢复正常；


环境说明：
DB:Oracle 11.2.0.1.0
OS:Windows Server 2012
问题分析：
收集CPU% 使用率超过 98% 时间段的 AWR 报告；

逻辑读比平时高出很多

等待事件都是I/O 类型

CPU 使用率很高

查看TOP SQL 发现两条严重消耗性能的 SQL ；




查看耗时SQL 执行计划

select * from table ( dbms_xplan.display_cursor ( 'gcvs9nw6b9d8m' ));

select * from table ( dbms_xplan.display_awr ( 'gcvs9nw6b9d8m' ));

SQL 存在绑定变量，查出绑定变量，进行问题重现；

select b.name , b.datatype_string , b.value_string , b.last_captured
from dba_hist_sqlbind b
where b.sql_id = 'gcvs9nw6b9d8m'
and to_char ( last_captured , 'yyyymmdd' ) = '20181204'
order by 4 , 1 ;

绑定变量值带入SQL 中，执行耗时 111 秒；

SELECT av.FID FACCOUNTID
FROM t_BD_AccountView AV
LEFT OUTER JOIN ( SELECT acct.FID FACCOUNTID ,
sum ( ve.FLocalAmount * ve.FEntryDC ) FDEBITLOCAL ,
sum ( ve.FLocalAmount * ( 1 - ve.FEntryDC )) FCREDITLOCAL
FROM T_GL_Voucher VCH
INNER JOIN T_GL_VoucherEntry VE
ON vch.FID = ve.FBillID
INNER JOIN t_BD_AccountView ACCT
ON (( ve.FAccountID = acct.FID AND
acct.Fcompanyid = 'ocIAAAAATtTM567U' ) AND
acct.FAccounttableId = 'ocIAAAAAfQsXaY5t' )
INNER JOIN T_BD_AccountType ACCTTYPE
ON acct.FAccountTypeID = acctType.FID
WHERE (( vch.Fcompanyid = 'ocIAAAAATtTM567U' AND
vch.fperiodid = 'ocIAAAAAx1SCOIxM' ) AND
vch.FbizStatus = 5 )
GROUP BY acct.fid ) A
ON a.FAccountID = av.FID
LEFT OUTER JOIN T_GL_AccountBalance_5L B
ON (( b.FOrgUnitID = 'ocIAAAAATtTM567U' AND b.FPeriod = '201810' ) AND
b.FAccountID = av.FID )
WHERE ((( av.Fcompanyid = 'ocIAAAAATtTM567U' AND
av.FAccounttableID = 'ocIAAAAAfQsXaY5t' ) AND av.FIsLeaf = 1 ) AND
(( NVL ( b.FDebitLocal , 0 ) <> NVL ( a.FDebitLocal , 0 )) OR
( NVL ( b.FCreditLocal , 0 ) <> NVL ( a.FCreditLocal , 0 ))));

--- 查看正在执行 SQL 和执行时间

select v.last_call_et ,
v.username ,
v.machine ,
v.program ,
v.module ,
v.sid ,
sql.sql_text ,
sql.sql_fulltext ,
sql.sql_id ,
sql.disk_reads ,
v.event
from v$session v , v$sql sql
where v.sql_address = sql.address
and v.last_call_et > 0
and v.status = 'ACTIVE'
and v.username is not null ;

查看SQL执行计划
select * from table ( dbms_xplan.display_cursor ( '736262x3f3zrh' ));


查看表相关信息

select count (*) from T_GL_VoucherEntry ; *---352763*
select count (*) from t_BD_AccountView ; *---534724* 
select count (*) from T_GL_VoucherEntry a , t_BD_AccountView b where a.FAccountID = b.FID ; *---352757* 
select count (*) from t_BD_AccountView where Fcompanyid = 'ocIAAAAATtTM567U' ; *---1373*

谓词列选择性较高，适合创建索引

select count (*), Fcompanyid from t_BD_AccountView group by Fcompanyid order by 1 desc ;

解决方案：
对 t_BD_AccountView 表 Fcompanyid 字段 创建索引；

select * from user_ind_columns where table_name = 'T_BD_ACCOUNTVIEW' ;
create index i_t_BD_AccountView_Fcompanyid on t_BD_AccountView ( Fcompanyid );

再次执行SQL，耗时0.249秒，速度提升400倍

SELECT av.FID FACCOUNTID
FROM t_BD_AccountView AV
LEFT OUTER JOIN ( SELECT acct.FID FACCOUNTID ,
sum ( ve.FLocalAmount * ve.FEntryDC ) FDEBITLOCAL ,
sum ( ve.FLocalAmount * ( 1 - ve.FEntryDC )) FCREDITLOCAL
FROM T_GL_Voucher VCH
INNER JOIN T_GL_VoucherEntry VE
ON vch.FID = ve.FBillID
INNER JOIN t_BD_AccountView ACCT
ON (( ve.FAccountID = acct.FID AND
acct.Fcompanyid = 'ocIAAAAATtTM567U' ) AND
acct.FAccounttableId = 'ocIAAAAAfQsXaY5t' )
INNER JOIN T_BD_AccountType ACCTTYPE
ON acct.FAccountTypeID = acctType.FID
WHERE (( vch.Fcompanyid = 'ocIAAAAATtTM567U' AND
vch.fperiodid = 'ocIAAAAAx1SCOIxM' ) AND
vch.FbizStatus = 5 )
GROUP BY acct.fid ) A
ON a.FAccountID = av.FID
LEFT OUTER JOIN T_GL_AccountBalance_5L B
ON (( b.FOrgUnitID = 'ocIAAAAATtTM567U' AND b.FPeriod = '201810' ) AND
b.FAccountID = av.FID )
WHERE ((( av.Fcompanyid = 'ocIAAAAATtTM567U' AND
av.FAccounttableID = 'ocIAAAAAfQsXaY5t' ) AND av.FIsLeaf = 1 ) AND
(( NVL ( b.FDebitLocal , 0 ) <> NVL ( a.FDebitLocal , 0 )) OR
( NVL ( b.FCreditLocal , 0 ) <> NVL ( a.FCreditLocal , 0 ))));

select * from table ( dbms_xplan.display_cursor ( '96tw5wp0kk1z5' ));

作者：chenoracle