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

原创 2003年07月22日 07:58:00

prompt<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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

数据库实验指导书+实验报告(2015)

预备实验(选做) 实验名称:SQL Server 2005的安装与系统设置   实验目的和要求:掌握SQL Server 2005的安装和配置方法,熟悉SQL Server的系统环境,主要是对“...
  • snsdzjlz
  • snsdzjlz
  • 2015年07月11日 20:54
  • 3217

数据库链接出错,请检查数据库路径是否正确--这个问题搞了一下午

因为客户网站要升级,但在升级的时候出现,数据库链接出错,请检查数据库路径是否正确--这个问题搞了一下午,说来我都不相信,我虽然不是高手,虽然很长时间不怎么写程序了,但还是很自信的,但没想到这个问题,具...
  • jjzaihaozhe
  • jjzaihaozhe
  • 2016年05月20日 11:56
  • 3725

SQL实现查找最近一周、一个月、三个月的数据

最近在做一个Web项目,
  • FX_SKY
  • FX_SKY
  • 2014年07月03日 21:21
  • 5828

Pop3邮件检查程序(25kb)

  • 2006年02月23日 09:05
  • 25KB
  • 下载

这是一个电子邮件检查程序,输入pop3的服务器地址,用户名及口令

  • 2006年02月23日 09:05
  • 27KB
  • 下载

Pop3邮件检查程序

  • 2002年12月08日 00:00
  • 25KB
  • 下载

这是一个Java的拼写检查程序

  • 2011年04月02日 20:52
  • 15KB
  • 下载

健康检查程序

/**  * Bean健康检查接口 */ public interface IBeanHealthChecker {     /**      * 检查checker内部定义bean   ...
  • yiyiNo
  • yiyiNo
  • 2014年10月24日 14:48
  • 472

检查程序对动态库的依赖

转载请注明出处:http://blog.csdn.net/horkychen 测试程序时会出现找不到指定的库或库函数的错误,表示某个依赖库不存在或版本不正确。如果使用的是动态加载的动态库所依赖的包不...
  • HorkyChen
  • HorkyChen
  • 2011年11月02日 00:16
  • 7184

shell awk 检查程序是否执行

1.Linux中怎样判断某一进程是不是运行? 2.Linux中怎样倒排序查看,服务器各类进程数? 3.Linux中怎样利用awk检查程序是否执行? 通过shell结合awk可以很好判断某一进...
  • dub_lys
  • dub_lys
  • 2016年02月16日 14:42
  • 303
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:实用的数据库检查程序(3)
举报原因:
原因补充:

(最多只允许输入30个字)