實用的資料庫檢查程式 (1) (轉)

實用的資料庫檢查程式 (1) (轉)[@more@]

:namespace prefix = o ns = "urn:schemas--com::office" /> 

功能:

1.  的基本資訊顯示(建庫時間,等)

2.  資料庫結構資訊-DATABASE MAP(CONTROL FILE, REDO LOG FILE, DATAFILE, ROLLBACK SEGMENT)

3.  資料庫所有狀態資訊

4.  資料庫級的 Block Buffer Hit Ratio

5.  SESSION級的DB Block Buffer Hit Ratio

6.  前一天的LOG SWITCH情況

7.  REDO LOG 空間需求查詢

8.  LOG Buffer 查詢

9.  log file switch效能查詢

10.  檢查不完全的CHECKPOINT

11.  Library Cache 的　效能查詢

12.  Dictionary Cache的　效能查詢

13.  查詢最資源的語句

14.  SESSION級的使用度

15.  SORT_AREA_SIZE的效能查詢

16.  SEQUENCE_CACHE_ENTRIES的效能查詢

17.  CHAINED ROW的查詢

18.  Rollback Segment Contention 檢查

19.  表空間碎片檢查

20.  ＬATCH contention 檢查

21.  TABLESPACE 用量檢查

22.  資料Ｉ／Ｏ檢查

23.  表和的碎片檢查

24.  表的ＨＷＭ檢查

使用方法舉例:

目前，作為一個，可以有很多工具來管理，維護和症查資料庫．這只是我平時蒐集的一些指令碼，可以作為DBA隨身攜帶的

在沒有其他視覺化的工具時，它可以用來對資料庫做一些基本的診斷．

１．開啟SQLPLUS , Connect System

２．@a:check_db.sql  (a:是本檔案的路徑)

３．完畢，結果儲存在C:LOCAL.TXT

４．LOCAL.TXT 不但有每個資料的說明，同時介紹一些解決相關問題的方法以供參考

Check_db.sql的內容：

SET echo off

spool c:local.txt

ttitle off

break on today

column today noprint new_value xdate

substr(to_char(sysdate,'fmMonth DD, YYYY HH:MI:SS P.M.'),1,35) today

from dual

/

column name noprint new_value xdbname

select name from v$database

/

set heading on

set feedback off

set linesize  250

set pagesize 200

rem ######################################################################################

rem    **** CHECK_DB_V2.1: Performance Tuning****

rem ######################################################################################

prompt *******************************************************************************

prompt  Database Check Information

prompt *******************************************************************************

ttitle left "DATABASE: "xdbname" (AS OF: "xdate")" sk2

select name , created , log_mode from v$database

/

prompt

prompt *******************************************************************************

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 0.0 database map  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

ttitle left "*** DataBase Map - Control File ***"

column "CONTROL FILE" format A40

select status , name "CONTROL FILE" from v$controlfile

/

ttitle off

ttitle left "*** DataBase Map - RedoLog File ***"

column "Log File" format A40

select f.member "Log File",l.group# ,l.thread# ,l.bytes ,l.status from v$log l, v$logfile f

where l.group# = f.group#

/

 ttitle off

ttitle left "*** DataBase Map - Data File ***"

column file_name format A40 

   

select file_name ,tablespace_name,bytes,blocks,status from dba_data_files order by tablespace_name , bytes desc 

/

ttitle off

ttitle left "*** DataBase Map - RollBack Seg ***"

SELECT N.NAME "ROLLBACK SEG NAME" , R.EXTENTS , r.rssize , R.OPTSIZE , HWMSIZE ,STATUS

FROM V$ROLLSTAT R,V$rollNAME N

WHERE  R.USN = N.USN

/

ttitle off

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 1.0 database statistic  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

set heading on

set termout on

TTitle left "*** Database: "xdbname", Database Statistic(As of : "xdate" ) ***" skip 1

column "Statistic Name" format A55

column value format 9,999,999,999,999,990

select n.statisti , n.name "Statistic Name", s.value

from v$statname n , v$sysstat s

where n.statistic# = s.statistic#

and value > 0

order by value desc

/

ttitle off

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 2.0 DB Block Buffer - Hit Ratio (Database Wise)  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

set heading on

set termout on

column "Physical Reads" format 9,999,999,999,999

column "Consistent Gets" format 9,999,999,999,999

column "DB Block Gets" format 9,999,999,999,999

column "Hit Ratio" format 999.99

TTitle left "*** Database: "xdbname", DB Block Buffers Hit Ratio (As of : "xdate" ) ***" skip 1-

left  "Percent = ((100*(1-(Physical Reads/(Consistent Gets + DB Block Gets))))"  skip 2

select pr.value "Physical Reads",

  cg.value "Consistent Gets",

  bg.value "DB Block Gets",

  round((1-(pr.value/(bg.value+cg.value)))*100,2) "Hit Ratio"

from  v$sysstat pr, v$sysstat bg, v$sysstat cg

where pr.name = 'physical reads'

and  bg.name = 'db block gets'

and  cg.name = 'consistent gets'

/

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 2.0 Investigation IF Percent is less than 70% , increase DB_BLOCK_BUFFERS

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

Ttitle Off

prompt

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 2.1  DB Block Buffer - Hit Ratio (Session Wise)  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

clear breaks

clear computes

break on report

compute sum of Consistent_Gets on report

compute sum of Block_Gets on report

compute sum of Physical_Reads on report

column "Hit Ratio %" format 999.99

column Username format A10

TTitle left "*** Database: "xdbname", Hit Ratio For User Sessions(As of : "xdate" ) ***" skip 1

select Username,

   OSUSER,

   Consistent_Gets,

   Block_Gets,

   Physical_Reads,

   100*( Consistent_Gets + Block_Gets - Physical_Reads)/

    ( Consistent_Gets + Block_Gets ) "Hit Ratio %"

from  V$SESSION,V$SESS_IO

where V$SESSION.SID = V$SESS_IO.SID

and  ( Consistent_Gets + Block_Gets )>0

and  username is not null

order by Username,"Hit Ratio %";

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 2.0/2.1 Investigation

prompt If you have 20 or more users and batch users cause less than 50%

prompt logical reads within your database , you should aim for a hit ratio

prompt of between 94% ~ 97%.

prompt If you have fewer than 20 users , the sharing of data among users depends

prompt heavily on the application , so you should aim for a hit ratio in the 89%~94%

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

prompt