实用的数据库检查程序 (1)

类别:数据库 点击:0 评论:0 推荐:

 

功能:

1.     数据库的基本信息显示(建库时间,模式等)

2.     数据库结构信息-DATABASE MAP(CONTROL FILE, REDO LOG FILE, DATAFILE, ROLLBACK SEGMENT)

3.     数据库所有状态信息

4.     数据库级的DB 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.  查找最资源的SQL语句

14.  SESSION级的CPU使用度

15.  SORT_AREA_SIZE的性能查询

16.  SEQUENCE_CACHE_ENTRIES的性能查询

17.  CHAINED ROW的查询

18.  Rollback Segment Contention 检查

19.  表空间碎片检查

20.  LATCH contention 检查

21.  TABLESPACE 用量检查

22.  数据文件I/O检查

23.  表和索引的碎片检查

24.  表的HWM检查

 

使用方法举例:

 

目前,作为一个DBA,可以有很多工具来管理,维护和症查数据库.这只是我平时搜集的一些脚本,可以作为DBA随身携带的小工具程序

在没有其他可视化的工具时,它可以用来对数据库做一些基本的诊断.

 

1.打开SQLPLUS , Connect System

2.@a:\check_db.sql   (a:\是本文件的路径)

3.执行完毕,结果储存在C:\LOCAL.TXT

4.LOCAL.TXT 不但有每个数据的说明,同时介绍一些解决相关问题的方法以供参考

 

Check_db.sql的内容:

 

SET echo off

spool c:\local.txt

ttitle off

break on today

column today noprint new_value xdate

select 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")" skip 2

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.statistic# , 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

本文地址:http://com.8s8s.com/it/it21199.htm