CSDN博客

img black_snail

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

发表于2003/7/22 7:58:00  769人阅读

prompt

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

prompt + 7.0 Sort Area Size                                                     +

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

prompt

 

ttitle left "*** Sort Area ***" Skip 1

 

Select 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 performing .

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 RevealNet:

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 DBA_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 guideline 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 <Name> 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 CPUs,

prompt and decrease Log_Small_Entry_Max_Size in init.ora 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 "SQL 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 + Perform "ANALYZE TABLE <TABLE NAME> COMPUTE STATISTICS;" first         +

prompt + This Transcation is taken more resources <Be carefully to use it!>     +

prompt + (To analyze tables , you can use                                       +

prompt + DBMS_UTILITY.ANALYZE_SCHEMA('<USER>','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(<'OWNER'>,<'OBJECT_NAME'>,<'OBJECT_TYPE'>

prompt      ,<Total Blocks>,<Total Bytes>,<Unused Blocks>,<Unused Bytes>

prompt            ,<Number Varible5>,<Number Varible6>,<Number Varible7>)

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

ttitle off

spool off

0 0

相关博文

我的热门文章

img
取 消
img