實用的資料庫檢查程式(3) (轉)

實用的資料庫檢查程式(3) (轉)[@more@]

prompt:namespace prefix = o ns = "urn:schemas--com::office" />

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 7.0 Sort Area Size   +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

ttitle left "*** Sort Area ***" Sk1

substr(name,1,25)  "Sort Area Name",

     substr(value,1,15) "Value"

from v$sysstat

where name like 'sort%'

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 7.0 Invesigation

prompt The lower the value of the sorts to disk ,

prompt the better the sort is perfong .

prompt SORT_AREA_SIZE can't be increase large enough to elimate sorts to disk

prompt The Sorts on a database are low-maintenance items

prompt Usually , SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE should be set to the same value

prompt The Optimal Value of SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE is 1M

prompt (For parallel query) . The larger value than 1M have not improved performance significantly

prompt check v$license and v$sort_usage

prompt From Revea:

prompt INITIAL/NEXT parameter of temporary table should have minimum size SORT_AREA_SIZE + 1 block

prompt SORT_AREA_RETAINED_SIZE set to half the SORT_AREA_SIZE

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 7.1 SEQUENCE_CACHE_ENTRIES

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

ttitle left "*** SEQUENCE_CACHE_ENTRIES ***" Skip 1

select count(*) "Number of Sequence" , SUM(CACHE_SIZE) "Cache Size Needed" from a_sequences

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 7.1 Invesigation

prompt SEQUENCE_CACHE_ENTRIES should set to 1000

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 9.0 Rollback Segment Contention  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

ttitle left "*** Rollback Segment Contention ***" Skip 1

column "Rollback Seg Name" format a20

column "Online?" format a10

column "Gets" format 9,999,999,990

column "Waits" format 9,999,999,990

column "% Ratio" format 999.99

select r.name "Rollback Seg Name" ,

  s.status "Online?",

  s.gets "Gets",

   s.waits "Waits",

   (waits/gets ) * 100 "% Ratio"

from v$rollstat s, v$rollname r

where s.usn = r.usn

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 9.0 Investigation

prompt If the radio is above 2% , create more rollback segment.

prompt The guline is :

prompt Transaction Number  *  Rollback Seg. Num

prompt  < 16    *  4

prompt  >= 16 & < 32  *  8

prompt  >= 32    *  Min(50,Transaction Numbers/4)

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 9.1 Rollback Segment Contention  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

ttitle left "*** Rollback Segment Contention ***" Skip 1

select class,count

from v$waitstat

where class like '%undo%'

Union

select name,value

from v$sysstat

where name = 'consistent gets'

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 9.1 Investigation

prompt the ratio of waits for any class should be less than 1% of the

prompt total number of requests

prompt If the ratio is greater than 1% , consider creating more rollback seg

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt How to estimate the transaction size

prompt before execute : select usn,writes from v$rollstat

prompt after execute  : select usn,writes from v$rollstat

prompt compare the result

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 10.0 Free Space Coalesced  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

ttitle left "*** Free Space Coalesced ***" Skip 1

select Tablespace_name , percent_blocks_coalesced

from dba_free_space_coalesced

order by percent_blocks_coalesced

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 10.0 Investigation

prompt The Ideal Percent Blocks Coalesced should be 100%

prompt Use "ALter Tablespace coalesce" to coalesce

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 11.0 Latch Contention  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

ttitle left "******** Latch Information ******" Skip 1

column "Redo Type" format A20

column gets format 9,999,990

column WILLING_TO_WAIT format 9,999,990

column misses format 9,999,990

column "IMMEDIATE" FORMAT 999.99

select Name "Redo Type", gets , misses,

  decode(gets,0,0,(100*(misses/(gets + misses)))) WILLING_TO_WAIT,

  sleeps,immediate_gets,immediate_misses,

  decode(immediate_gets,0,0,(100*(immediate_misses/(immediate_gets + immediate_misses)))) "IMMEDIATE"

from v$latch

where name like 'redo%'

order by name

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 11.0 Investigation

prompt If Willing_to_wait and Immediate is greater than 1%,

prompt increase Log_Simultaneous_copies to twice # of s,

prompt and decrease Log_Small_Entry_Max_Size in init.file

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 11.1 Latch Contention (Reveal Net)  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

ttitle left "******** Latch Information ******" Skip 1

SELECT a.name,

  100. * b.sleeps / b.gets ratio1,

  100. * b.immediate_misses / DECODE ( (b.immediate_misses + b.immediate_gets), 0, 1) ratio2

 FROM v$latchname a, v$latch b

 WHERE a.latch# = b.latch#

 AND b.sleeps > 0

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 11.1 Investigation

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 12.0 Tablespace Usage   +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

set pagesize 66

clear breaks

clear computes

column "Total Bytes" format 9,999,999,999,999

column " Blocks" format 9,999,999,999

column "Bytes Free" format 9,999,999,999,999

column "Bytes Used" format 9,999,999,999,999

column "% Free" format 9999.999

column "% Used" format 9999.999

break on report

compute sum of "Total Bytes" on report

compute sum of "SQL Blocks" on report

compute sum of "Bytes Free" on report

compute sum of "Bytes Used" on report

compute sum of "% Free" on report

compute sum of "% Used" on report

TTitle left "*** Database: "xdbname", Current Tablespace Usage (As of: "xdate") ***" skip 1

select substr(fs.FILE_ID,1,3) "ID#",

  fs.tablespace_name,

  df.bytes "Total Bytes",

  df.blocks "SQL Blocks",

  sum(fs.bytes) "Bytes Free",

  (100*((sum(fs.bytes))/df.bytes)) "% Free",

  df.bytes-sum(fs.bytes) "Bytes Used",

  100*((df.bytes-sum(fs.bytes))/df.bytes) "% Used"

from sys.dba_data_files df,sys.dba_free_space fs

where df.file_id(+) = fs.file_id

group by fs.file_id,fs.tablespace_name,df.bytes,df.blocks

order by fs.tablespace_name

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 12.0 Invesigation

prompt if a tablespace has all datafiles with % Used greater

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 13.0 Disk Activity   +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

column "File Name" format a35

column "File Total" format 999,999,999,990

set pagesize 33

ttitle "*** Database: "xdbname", Datafile Disk Activity (As of : "xdate") ***"

select substr(df.file#,1,2) "ID",

  rpad(name,35,'.') "File Name",

  rpad(substr(phyrds,1,10),10,'.') "Phy Reads",

  rpad(substr(phywrts,1,10),10,'.') "Phy Writes",

  rpad(substr(phyblkrd,1,10),10,'.') "Blk Reads",

  rpad(substr(phyblkwrt,1,10),10,'.') "Blk Writes",

  rpad(substr(readtim,1,9),9,'.') "Read Time",

  rpad(substr(writetim,1,10),10,'.') "Write Time",

  sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) "File Total"

from v$filestat fs,v$datafile df

where fs.file# = df.file#

group by df.file#,df.name,phyrds,phywrts,phyblkrd,

  phyblkwrt,readtim, writetim

order by sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) desc , df.name

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 13.0 Investigation

prompt To reduce disk contention , insure that datafiles

prompt with the greatest activity are not on the same disk .

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 14.0 Fragmentation Need  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

set heading on

set termout on

set pagesize 66

ttitle left "*** Database: "xdbname", DEFRAGMENTATION NEED AS OF: "xdate" ***"

select substr(de.owner,1,8) "Owner",

  substr(de.segment_type,1,8) "Seg Type",

  substr(de.segment_name,1,35) "Table Name (Segment)",

  substr(de.tablespace_name,1,20) "TableSpace Name",

  COUNT(*) "Frag Need",

  substr(df.name,1,40) "Datafile Name"

from sys.dba_extents de,v$datafile df

where de.owner <> 'SYS'

and de.file_id = df.file#

and de.segment_type = 'TABLE'

group by de.owner,de.segment_name,de.segment_type,de.tablespace_name,df.name

having count(*) > 1

order by count(*) desc

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 14.0 Investigation

prompt The more fragmented a segment is , the more I/O needed to read

prompt that info . Defragments this table regularly to insure extents

prompt ('Frag NEED') do not get much above 2.

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 15.0 prompt High Water Mark (HWM)   +

prompt + Perfo"ANALYZE TABLE

COMPUTE STATISTICS;" first  +

prompt + This Transcation is taken more res   +

prompt + (To analyze tables , you can use   +

prompt + DBMS_UTILITY.ANALYZE_SCHEMA('','COMPUTE') )  +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

ttitle left skip 1 -

left "************ High Water Mark *****************" skip 1

column Owner format A10

column Segment_name format A40

select a.Owner ,

   a.Segment_name ,

   a.blocks ,

   b.empty_blocks ,

   a.blocks - b.empty_blocks -1 "High Water Mark"

from  DBA_SEGMENTS a, DBA_TABLES b

where  a.Owner = b.Owner

and  a.Segment_name = b.table_name

order by a.owner , a.segment_name

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 15.0 Investigation

prompt To analyze the table statistics , you can also use

prompt DBMS_SPACE.Unused_space(,,

prompt   ,,,,

prompt  ,,,)

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

ttitle off

spool off

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10748419/viewspace-959643/，如需轉載，請註明出處，否則將追究法律責任。