实用的数据库检查程序 (1)
来源:百度文库 编辑:神马文学网 时间:2024/04/27 13:44:01
[日期:2005-07-08]来源:CSDN 作者:[字体:大中小]
功能:
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
功能:
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
实用的数据库检查程序 (1)
实用的数据库检查程序(3)
实用的数据库检查程序(2)
实用的数据库检查程序(2)
实用的数据库检查程序(3)
安全生产检查的工作程序
Informix 数据库一致性检查
检查Oracle数据库中不合理的sql语句
如何用程序的方式来建立 FireBird 数据库???
一个连接两个不同MYSQL数据库的PHP程序
把IP表存入SQL数据库里的程序
用概率论的方法实现理想化程序 - 批量插入数据库
Informix 数据库一致性检查 - Unix爱好者家园
Delphi开发单机瘦数据库程序要点
感染性疾病的检查【1】
注意检查程序也有时效性
一个实用的Delphi屏幕截图程序的设计
一个很实用的清除电脑垃圾的小程序
房地产开发的程序1
数据库1
请问VB能否在程序运行过程中将变量的值赋给数据库的字段? - VB6论坛 - 编程论坛
Cassandra Hector分布式数据库入门到高效实用实例 - doliu6的专栏 - CSDN博客
VB实用小程序123
DB2数据库部分日常实用操作