Oracle效能優化-資料庫CPU使用率100%

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/，如需轉載，請註明出處，否則將追究法律責任。