答案:
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
上一个:实用的数据库检查程序(3)
下一个:实用的数据库检查程序 (1)