如何解決CPU過度消耗問題

　　問題描述:

　　開發人員報告系統運行緩慢影響用戶訪問

　　登陸數據庫主機

　　使用vmstat檢查發現CPU資源已經耗盡大量任務位於運行隊列:

 bash$ vmstat
 procs     memory         page          disk        faults     cpu
 r b w  swap  free  re  mf pi po fr de sr s s s sd   in   sy   cs us sy id
                     
               
               
               
           
             
               
         
           
               
               
               
             
               
         
       
             
           

　　使用Top命令

　　觀察進程CPU耗用發現沒有明顯過高CPU使用的進程

$ top

last pid: ;  load averages:       ::
processes: sleeping running zombie on cpu
CPU states: % idle% user % kernel% iowait %swap
Memory: M real M free M swap in use M swap free

   PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
  oraclei         M M run      :  % oracle
  oraclei         M M sleep    :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M cpu/    :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M run      :  % oracle
  oraclei         M M run      :  % oracle

　　檢查進程數量

bash$ ps ef|grep ora|wc l
    
bash$ ps ef|grep ora|wc l
    
bash$ ps ef|grep ora|wc l
    
bash$ ps ef|grep ora|wc l
    
bash$ ps ef|grep ora|wc l
    
bash$ ps ef|grep ora|wc l
    

　　發現系統存在大量Oracle進程大約在左右而正常情況下Oracle連接數應該在左右

　　檢查數據庫

　　查詢v$session_wait獲取各進程等待事件

SQL> select sideventpptext from v$session_wait;

       SID EVENT                                  P PTEXT
       latch free                     E+ address
         pmon timer                            duration
         rdbms ipc message                     timeout
         rdbms ipc message                     timeout
        rdbms ipc message                   timeout
         rdbms ipc message                  timeout
         rdbms ipc message                     timeout
       rdbms ipc message                    timeout
       rdbms ipc message                    timeout
       rdbms ipc message                   timeout
       rdbms ipc message                    timeout

       SID EVENT                                  P PTEXT

       rdbms ipc message                    timeout
         buffer busy waits                      file#
        buffer busy waits                      file#
       buffer busy waits                      file#
       buffer busy waits                      file#
       buffer busy waits                      file#
       buffer busy waits                      file#
       buffer busy waits                      file#
       buffer busy waits                      file#
       buffer busy waits                      file#
       buffer busy waits                      file#

       SID EVENT                                  P PTEXT

       buffer busy waits                      file#
        buffer busy waits                      file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#

       SID EVENT                                  P PTEXT

        db file sequential read                file#
        db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#

       SID EVENT                                  P PTEXT

       db file sequential read                file#
       db file sequential read                file#
        db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#

       SID EVENT                                  P PTEXT

       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#

       SID EVENT                                  P PTEXT

       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#

       SID EVENT                                  P PTEXT

       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#

       SID EVENT                                  P PTEXT

       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
       db file sequential read                file#
        db file sequential read                file#

       SID EVENT                                  P PTEXT

        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
       db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#
        db file sequential read                file#

       SID EVENT                                  P PTEXT

        db file sequential read                file#
        db file sequential read                file#
        db file scattered read                 file#
        db file scattered read                 file#
       db file scattered read                 file#
       db file scattered read                 file#
       db file scattered read                 file#
       db file scattered read                 file#
       db file scattered read                 file#
       db file scattered read                 file#
       db file scattered read                 file#

       SID EVENT                                  P PTEXT

       db file scattered read                file#
       db file scattered read                file#
       db file scattered read                file#
       db file scattered read                file#
        db file scattered read                file#
       db file scattered read                file#
       db file scattered read                file#
       db file scattered read                file#
       db file scattered read                file#
        db file scattered read                file#
         smon timer                          sleep time

       SID EVENT                                  P PTEXT

        SQL*Net message to client      driver id
       SQL*Net message to client      driver id

       SQL*Net more data from client  driver id
       SQL*Net more data from client  driver id

rows selected

　　發現存在大量db file scattered read及db file sequential read等待

　　捕獲相關SQL

　　這裡用到了我的以下腳本getsqlbysidsql:

SELECT   sql_text
    FROM v$sqltext a
   WHERE ahash_value = (SELECT sql_hash_value
                           FROM v$session b
                          WHERE bSID = &sid)
ORDER BY piece ASC
/

SQL> @getsql
Enter value for sid:
old   : where bsid=&sid
new   : where bsid=

SQL_TEXT

select ivctitleinuminfoguid  from  hs_info i where iintenab
ledflag =   and iintpublishstate =   and idatpublishdate <=
sysdate  and inumcatalogguid = order by idatpublishdate d
esc inumorder desc

SQL> /
Enter value for sid:
old   : where bsid=&sid
new   : where bsid=

SQL_TEXT

select ivctitleinuminfoguid  from  hs_info i where iintenab
ledflag =   and iintpublishstate = and idatpublishdate <=
sysdate  and inumcatalogguid = order by idatpublishdate des
c inumorder desc

SQL> /
Enter value for sid:
old   : where bsid=&sid
new   : where bsid=

SQL_TEXT

select ivctitleinuminfoguidfrom  hs_info i where iintenab
ledflag =   and iintpublishstate =   and idatpublishdate <=
sysdate  and inumcatalogguid = order by idatpublishdate des
c inumorder desc

　　對幾個全表掃描進程跟蹤以後得到以上SQL語句

　　以上語句如果良好編碼應該使用綁定變量但是現在這個不是我們關心的

　　使用該應用用戶連接檢查其執行計劃:

SQL> set autotrace trace explain
SQL> select ivctitleinuminfoguid 
    from  hs_info i where iintenabledflag =  
    and iintpublishstate =   and idatpublishdate <=sysdate 
    and inumcatalogguid =
    order by idatpublishdate desc inumorder desc  ;

Execution Plan

       SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
     SORT (ORDER BY) (Cost= Card= Bytes=)
     TABLE ACCESS (FULL) OF HS_INFO (Cost= Card= Bytes=)

SQL> select count(*) from hs_info;

  COUNT(*)

   

　　該表這裡有萬記錄全表掃描已經不再適合

　　檢查該表存在以下索引:

SQL> select index_nameindex_type  from user_indexes where table_name=HS_INFO;

INDEX_NAME                     INDEX_TYPE

HSIDX_INFO                    FUNCTIONBASED NORMAL
HSIDX_INFO_SEARCHKEY           DOMAIN
PK_HS_INFO                     NORMAL

　　檢查索引鍵值:

SQL> select index_namecolumn_name from user_ind_columns  where table_name =HS_INFO;

INDEX_NAME                     COLUMN_NAME

HSIDX_INFO                    NUMORDER
HSIDX_INFO                    SYS_NC$
HSIDX_INFO_SEARCHKEY           VCINDEXWORDS
PK_HS_INFO                     NUMINFOGUID

SQL> desc hs_info
 Name                                  Null?    Type
 
 NUMINFOGUID                         NOT NULL NUMBER()
 NUMCATALOGGUID                      NOT NULL NUMBER()
 INTTEXTTYPE                         NOT NULL NUMBER()
 VCTITLE                            NOT NULL VARCHAR()
 VCAUTHOR                                    VARCHAR()
 NUMPREVINFOGUID                              NUMBER()
 NUMNEXTINFOGUID                              NUMBER()
 NUMORDER                            NOT NULL NUMBER()
 DATPUBLISHDATE                      NOT NULL DATE
 INTPUBLISHSTATE                     NOT NULL NUMBER()
 VCPUBLISHERID                               VARCHAR()
 VCINDEXWORDS                                VARCHAR()
 VCWAPPREVPATH                               VARCHAR()
 VCWEBPREVPATH                               VARCHAR()
 VCWAPPREVPATH                              VARCHAR()
 NUMVISITED                          NOT NULL NUMBER()
 INTENABLEDFLAG                      NOT NULL NUMBER()
 DATCREATETIME                       NOT NULL DATE
 DATMODIFYTIME                       NOT NULL DATE
 VCNOTES                                     VARCHAR()
 INTINFOTYPE                         NOT NULL NUMBER()
 VCPRIZEFLAG                                 VARCHAR()
 VCDESC                                      VARCHAR()

　　決定創建新的索引以消除全表掃描

SQL> create index hs_info_NUMCATALOGGUID on hs_info(NUMCATALOGGUID);

Index created

SQL> set autotrace trace explain
SQL> select ivctitleinuminfoguid 
    from  hs_info i where iintenabledflag =  
    and iintpublishstate =   and idatpublishdate <=sysdate 
    and inumcatalogguid =
    order by idatpublishdate desc inumorder desc ;

Execution Plan

   SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
SORT (ORDER BY) (Cost= Card= Bytes=)
TABLE ACCESS (BY INDEX ROWID) OF HS_INFO (Cost= Card= Bytes=)
INDEX (RANGE SCAN)OFHS_INFO_NUMCATALOGGUID(NONUNIQUE)(Cost= Card=)

　　觀察系統狀況

　　原大量等待消失

SQL> select sideventpptext from v$session_wait where event not like SQL%;

       SID EVENT                                  P PTEXT

         pmon timer                            duration
         rdbms ipc message                     timeout
         rdbms ipc message                     timeout
         rdbms ipc message                  timeout
        rdbms ipc message                    timeout
       rdbms ipc message                    timeout
       rdbms ipc message                   timeout
       rdbms ipc message                    timeout
        rdbms ipc message                    timeout
        rdbms ipc message                   timeout
         rdbms ipc message                     timeout

       SID EVENT                                  P PTEXT

       db file sequential read                file#
       db file sequential read                file#
        db file scattered read                 file#
         smon timer                            sleep time

rows selected

　　持續觀察的CPU使用情況
 
bash$ vmstat
 procs     memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr s s s sd   in   sy   cs us sy id
             
             
         
         
         
         
         
     
                   
               
       
               
         
             
                 
以上為創建索引之前部分
以下為創建索引之後部分CPU使用率恢復正常
 procs     memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr s s s sd   in   sy   cs us sy id
         
         
         
         
         
         
           
           
                   
                     
               
               
                     
                 
             
                 
                 
                 
                 

　　至此此問題得以解決

From:http://tw.wingwit.com/Article/program/SQL/201311/16417.html