实用的数据库检查程序(2)
来源:百度文库 编辑:神马文学网 时间:2024/04/28 13:02:27
[日期:2005-07-08]来源:CSDN 作者:[字体:大中小]
Ttitle Off
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.0 Log Switch In the Last Day +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
TTitle left "*** Database: "xdbname", How Offen the Log Switch(As of : "xdate" ) ***" skip 1
column archive_name format A40
column "Time" format A25
select to_char(TO_DATE(Time,‘MM/DD/RR HH24:MI:SS‘),‘DD-MON-RRRR HH24:MI:SS‘) "Time",
ARCHIVE_NAME
from v$log_history
where TO_DATE(Time,‘MM/DD/RR HH24:MI:SS‘) > sysdate - 1
order by TO_DATE(Time,‘MM/DD/RR HH24:MI:SS‘) desc ;
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.0 Invesigation
prompt Standard:
prompt During periods of high activity , log switches are occurring every 20 minutes
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.1 Log Buffer - redo log space requests : The Value +
prompt + should be relative small prompt Server is waiting for +
prompt + disk space to be allocate for redo log entries +
prompt + Space is created by performing a log switch +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Log Buffers - redo log space requests ***" Skip 1
select substr(name,1,25) "Log Buffers",
substr(value,1,15) "VALUE (Near 0?)"
from v$sysstat
where name = ‘redo log space requests‘
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.1 Investigation
prompt If the value is not near 0 , increase LOG BUFFER.
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.2 Log Buffer - log buffer space +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Log Buffers - Log Buffer Space Waits***" Skip 1
select sid , event , seconds_in_wait , state
from v$session_wait
where event = ‘log buffer space‘
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.2 Invesigation
prompt There should be no log buffer space waits
prompt Making the log buffer bigger if it is small
prompt Moving the log files to faster disks such as striped disks
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.3 Log Buffer - Redo Buffer Allocation Retries +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Log Buffers - Redo Buffer Allocation Retries ***" Skip 1
column name print
select name , value
from v$sysstat
where name in (‘redo buffer allocation retries‘,‘redo entries‘)
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.3 Investigation
prompt Redo Buffer Allocation Retries should be near 0
prompt the number should be less than 1% of Redo Entries
prompt Increase the size of the redo log buffer (LOG BUFFER)
prompt improve the checkpointing or archiving process
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.4 Log Buffer - Log File Switch Completion +
prompt + Identify the log file switch waits because of log switches +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Log Buffers - Log File Swith Completion***" Skip 1
select event , total_waits,time_waited,average_wait
from v$system_event
where event like ‘log file switch completion%‘
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.4 Investigation
prompt Increase the size of the redo log files
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.5 Log Buffer - CHECKPOINT Incomplete +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Log Buffers - CheckPoint Incompletion***" Skip 1
select event , total_waits,time_waited,average_wait
from v$system_event
where event like ‘log file switch (check%‘
or event like ‘log file switch (arch%‘
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.5 Investigation
prompt check the frequence of check points and set the appropriate values
prompt for LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT
prompt check the size and number of redo log groups
prompt confirm that the archive device is not full
prompt add redo log groups
prompt increase the number of buffers to archive and reducing the size of buffers by setting
prompt LOG_ARCHIVE_BUFFERS and LOG_ARCHIVE_BUFFER_SIZE
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 4.0 Share Pool Size - Gets and Misses (Library Cache) +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
column "Executions" format 9,999,999,990
column "Cache Misses Executing" format 9,999,999,990
column "Data Dictionary Gets" format 9,999,999,999
column "Get Misses" format 9,999,999,999
column "% Ratio" format 999.99
ttitle left skip 1 -
left "*** Shared Pool Size (Execution Misses) ***" skip 1
select sum(pins) "Executions",
sum(reloads) "Cache Misses Executing",
(sum(reloads)/sum(pins)*100) "% Ratio"
from v$librarycache
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 4.0 Investigation
prompt If % Ratio is above 1% , increase SHARE_POOL_SIZE.
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 4.1 Share Pool Size - Gets and Misses (Data Dictionary) +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*************** Shared Pool Size (Dictionary Gets) ***********" skip 1
select sum(gets) "Data Dictionary Gets",
sum(getmisses) "Get Misses",
100*(sum(getmisses)/sum(gets)) "Ratio"
from v$rowcache
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 4.1 Investigation
prompt If % Ratio is above 12% , increase SHARED_POOL_SIZE.
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 5.0 Check Which SQL is the most cost SQL +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle off
ttitle left "*** V$SQL Check ***" Skip 1
column SQL_TEXT Format A50
select Sql_TEXT,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS/100,DISK_READS/100
FROM V$sql V,(SELECT SUM(BUFFER_GETS) TOT_GETS FROM V$SQL) S
WHERE BUFFER_GETS > TOT_GETS * 0.1
ORDER BY BUFFER_GETS DESC
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 5.0 Invesigation
prompt Selected SQL is the most cost SQL (>10% of total gets)
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 6.0 How Much CPU is used for each session +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** CPU Usage ***" Skip 1
column USERNAME FORMAT A10
column MACHINE FORMAT A15
column OSUSER FORMAT A15
column TERMINAL FORMAT A15
column PROGRAM FORMAT A20
select s.sid,v.SERIAL#,v.USERNAME,v.OSUSER,v.MACHINE,v.TERMINAL,v.PROGRAM,s.value "CPU Used"
from v$sesstat s,v$statname n , v$session v
where s.statistic#=n.statistic# and n.name=‘CPU used by this session‘
and s.sid = v.sid
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 6.0 Invesigation
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
Ttitle Off
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.0 Log Switch In the Last Day +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
TTitle left "*** Database: "xdbname", How Offen the Log Switch(As of : "xdate" ) ***" skip 1
column archive_name format A40
column "Time" format A25
select to_char(TO_DATE(Time,‘MM/DD/RR HH24:MI:SS‘),‘DD-MON-RRRR HH24:MI:SS‘) "Time",
ARCHIVE_NAME
from v$log_history
where TO_DATE(Time,‘MM/DD/RR HH24:MI:SS‘) > sysdate - 1
order by TO_DATE(Time,‘MM/DD/RR HH24:MI:SS‘) desc ;
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.0 Invesigation
prompt Standard:
prompt During periods of high activity , log switches are occurring every 20 minutes
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.1 Log Buffer - redo log space requests : The Value +
prompt + should be relative small prompt Server is waiting for +
prompt + disk space to be allocate for redo log entries +
prompt + Space is created by performing a log switch +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Log Buffers - redo log space requests ***" Skip 1
select substr(name,1,25) "Log Buffers",
substr(value,1,15) "VALUE (Near 0?)"
from v$sysstat
where name = ‘redo log space requests‘
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.1 Investigation
prompt If the value is not near 0 , increase LOG BUFFER.
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.2 Log Buffer - log buffer space +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Log Buffers - Log Buffer Space Waits***" Skip 1
select sid , event , seconds_in_wait , state
from v$session_wait
where event = ‘log buffer space‘
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.2 Invesigation
prompt There should be no log buffer space waits
prompt Making the log buffer bigger if it is small
prompt Moving the log files to faster disks such as striped disks
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.3 Log Buffer - Redo Buffer Allocation Retries +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Log Buffers - Redo Buffer Allocation Retries ***" Skip 1
column name print
select name , value
from v$sysstat
where name in (‘redo buffer allocation retries‘,‘redo entries‘)
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.3 Investigation
prompt Redo Buffer Allocation Retries should be near 0
prompt the number should be less than 1% of Redo Entries
prompt Increase the size of the redo log buffer (LOG BUFFER)
prompt improve the checkpointing or archiving process
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.4 Log Buffer - Log File Switch Completion +
prompt + Identify the log file switch waits because of log switches +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Log Buffers - Log File Swith Completion***" Skip 1
select event , total_waits,time_waited,average_wait
from v$system_event
where event like ‘log file switch completion%‘
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.4 Investigation
prompt Increase the size of the redo log files
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 3.5 Log Buffer - CHECKPOINT Incomplete +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Log Buffers - CheckPoint Incompletion***" Skip 1
select event , total_waits,time_waited,average_wait
from v$system_event
where event like ‘log file switch (check%‘
or event like ‘log file switch (arch%‘
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 3.5 Investigation
prompt check the frequence of check points and set the appropriate values
prompt for LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT
prompt check the size and number of redo log groups
prompt confirm that the archive device is not full
prompt add redo log groups
prompt increase the number of buffers to archive and reducing the size of buffers by setting
prompt LOG_ARCHIVE_BUFFERS and LOG_ARCHIVE_BUFFER_SIZE
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 4.0 Share Pool Size - Gets and Misses (Library Cache) +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
column "Executions" format 9,999,999,990
column "Cache Misses Executing" format 9,999,999,990
column "Data Dictionary Gets" format 9,999,999,999
column "Get Misses" format 9,999,999,999
column "% Ratio" format 999.99
ttitle left skip 1 -
left "*** Shared Pool Size (Execution Misses) ***" skip 1
select sum(pins) "Executions",
sum(reloads) "Cache Misses Executing",
(sum(reloads)/sum(pins)*100) "% Ratio"
from v$librarycache
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 4.0 Investigation
prompt If % Ratio is above 1% , increase SHARE_POOL_SIZE.
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 4.1 Share Pool Size - Gets and Misses (Data Dictionary) +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*************** Shared Pool Size (Dictionary Gets) ***********" skip 1
select sum(gets) "Data Dictionary Gets",
sum(getmisses) "Get Misses",
100*(sum(getmisses)/sum(gets)) "Ratio"
from v$rowcache
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 4.1 Investigation
prompt If % Ratio is above 12% , increase SHARED_POOL_SIZE.
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 5.0 Check Which SQL is the most cost SQL +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle off
ttitle left "*** V$SQL Check ***" Skip 1
column SQL_TEXT Format A50
select Sql_TEXT,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS/100,DISK_READS/100
FROM V$sql V,(SELECT SUM(BUFFER_GETS) TOT_GETS FROM V$SQL) S
WHERE BUFFER_GETS > TOT_GETS * 0.1
ORDER BY BUFFER_GETS DESC
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 5.0 Invesigation
prompt Selected SQL is the most cost SQL (>10% of total gets)
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 6.0 How Much CPU is used for each session +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** CPU Usage ***" Skip 1
column USERNAME FORMAT A10
column MACHINE FORMAT A15
column OSUSER FORMAT A15
column TERMINAL FORMAT A15
column PROGRAM FORMAT A20
select s.sid,v.SERIAL#,v.USERNAME,v.OSUSER,v.MACHINE,v.TERMINAL,v.PROGRAM,s.value "CPU Used"
from v$sesstat s,v$statname n , v$session v
where s.statistic#=n.statistic# and n.name=‘CPU used by this session‘
and s.sid = v.sid
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 6.0 Invesigation
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
实用的数据库检查程序(2)
实用的数据库检查程序(2)
实用的数据库检查程序 (1)
实用的数据库检查程序(3)
实用的数据库检查程序(3)
安全生产检查的工作程序
Informix 数据库一致性检查
检查Oracle数据库中不合理的sql语句
如何用程序的方式来建立 FireBird 数据库???
一个连接两个不同MYSQL数据库的PHP程序
把IP表存入SQL数据库里的程序
用概率论的方法实现理想化程序 - 批量插入数据库
Informix 数据库一致性检查 - Unix爱好者家园
Delphi开发单机瘦数据库程序要点
注意检查程序也有时效性
一个实用的Delphi屏幕截图程序的设计
一个很实用的清除电脑垃圾的小程序
请问VB能否在程序运行过程中将变量的值赋给数据库的字段? - VB6论坛 - 编程论坛
Cassandra Hector分布式数据库入门到高效实用实例 - doliu6的专栏 - CSDN博客
VB实用小程序123
快快检查你的卧室吧2
DB2数据库部分日常实用操作
MySQL 数据库常用命令 超级实用版分享
数据库在网格计算中的角色 - CSAI.cn程序开发