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

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