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倍