熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

不讓redo log日志組處於高激活狀態

2013-11-13 15:35:50  來源: Oracle 

  平台:SunOS Generic_ sunu sparc SUNWUltraEnterprise
  
  數據庫:
  
  症狀:響應緩慢應用請求已經無法返回
  
  登陸數據庫發現redo日志組除current外都處於active狀態
  
  oracle:/oracle/oracle>sqlplus / as sysdba
  
  SQL*Plus: Release Production on Thu Jun ::
  
  (c) Copyright Oracle Corporation All rights reserved
  
  Connected to:
  Oraclei Enterprise Edition Release Production
  With the Partitioning and Java options
  PL/SQL Release Production
  SQL> select * from v$log;
  
    GROUP#  THREAD# SEQUENCE#   BYTES  MEMBERS ARC STATUS      FIRST_CHANGE# FIRST_TIM
  
                       NO ACTIVE       E+ JUN
                       NO ACTIVE       E+ JUN
                       NO ACTIVE       E+ JUN
                       NO CURRENT       E+ JUN
                       NO ACTIVE       E+ JUN
                       NO ACTIVE       E+ JUN
                      NO ACTIVE       E+ JUN
                      NO ACTIVE       E+ JUN
                      NO ACTIVE       E+ JUN
  
   rows selected
  
  SQL> /
  
    GROUP#  THREAD# SEQUENCE#   BYTES  MEMBERS ARC STATUS      FIRST_CHANGE# FIRST_TIM
  
                       NO ACTIVE       E+ JUN
                       NO ACTIVE       E+ JUN
                       NO ACTIVE       E+ JUN
                       NO CURRENT       E+ JUN
                       NO ACTIVE       E+ JUN
                       NO ACTIVE       E+ JUN
                      NO ACTIVE       E+ JUN
                      NO ACTIVE       E+ JUN
                      NO ACTIVE       E+ JUN
  
   rows selected
  
  如果日志都處於active狀態那麼顯然DBWR的寫已經無法跟上log switch觸發的檢查點
  
  接下來讓我們檢查一下DBWR的繁忙程度:
  
  SQL> !
  oracle:/oracle/oracle>ps ef|grep ora_
   oracle       Mar ?    : ora_smon_hysms
   oracle       Mar ?    : ora_dbw_hysms
   oracle      Mar ?    : ora_pmon_hysms
   oracle       Mar ?    : ora_lgwr_hysms
   oracle       Mar ?    : ora_snp_hysms
   oracle       Mar ?    : ora_snp_hysms
   oracle       Mar ?    : ora_snp_hysms
   oracle       Mar ?    : ora_ckpt_hysms
   oracle       Mar ?    : ora_snp_hysms
   oracle       Mar ?    : ora_snp_hysms
   oracle       Mar ?    : ora_d_hysms
   oracle       Mar ?    : ora_s_hysms
   oracle       Mar ?    : ora_reco_hysms
   oracle   :: pts/  : grep ora_
  
  DBWR的進程號是
  
  使用Top命令觀察一下:
  
  oracle:/oracle/oracle>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
   smspf       M M cpu/  : % oracle
    oracle        M M run  H % oracle
   oracle       M M sleep  H % oracle
    smspf        M  M sleep H % java
   smspf       M M cpu/ : % oracle
   smspf       K K sleep  H % fee_ftp_get
   oracle       K K cpu/  : % top
   smspf        M  M sleep : % java
   smspf        M K sleep  : % smif_status_rec
    smspf        M  M sleep  H % fee_file_in
   root        K K sleep  : % sshd
   smspf       M M sleep  : % oracle
    oracle       M M sleep : % oracle
   smspf       K K sleep  : % sgip_client_ltz
   smspf       M M sleep : % java
  
  我們注意到號進程消耗的CPU不過%顯然並不繁忙那麼瓶頸就很可能在IO上
  
  使用IOSTAT工具檢查IO狀況
  
  gqgai:/home/gqgai>iostat xn
            extended device statistics
    r/s  w/s  kr/s  kw/s wait actv wsvc_t asvc_t %w %b device
  
                    ctd
                   ctd
                   ctd
                 ctd
                    hurraysms:vold(pid)
            extended device statistics
    r/s  w/s  kr/s  kw/s wait actv wsvc_t asvc_t %w %b device
  
                    ctd
                    ctd
                    ctd
                ctd
                    hurraysms:vold(pid)
            extended device statistics
    r/s  w/s  kr/s  kw/s wait actv wsvc_t asvc_t %w %b device
  
                    ctd
                    ctd
                    ctd
                   ctd
                    hurraysms:vold(pid)
            extended device statistics
    r/s  w/s  kr/s  kw/s wait actv wsvc_t asvc_t %w %b device
  
                
From:http://tw.wingwit.com/Article/program/Oracle/201311/16983.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.