ITPub博客

首页 > 数据库 > Oracle > Oracle性能优化-数据库CPU使用率100%

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

原创 Oracle 作者:chenoracle 时间:2020-02-13 22:49:03 0 删除 编辑

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' ));

欢迎关注我的微信公众号"IT小Chen",共同学习,共同成长!!!


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2675522/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle 11g OCP、Oracle 11g OCM、OCMU 用户组成员,微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    424
  • 访问量
    888410