实用的数据库检查程序(3)
来源:百度文库 编辑:神马文学网 时间:2024/06/06 23:14:56
[日期:2005-07-08]来源:CSDN 作者:[字体:大中小]
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 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
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 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
实用的数据库检查程序(3)
实用的数据库检查程序(3)
实用的数据库检查程序 (1)
实用的数据库检查程序(2)
实用的数据库检查程序(2)
安全生产检查的工作程序
Informix 数据库一致性检查
检查Oracle数据库中不合理的sql语句
如何用程序的方式来建立 FireBird 数据库???
一个连接两个不同MYSQL数据库的PHP程序
把IP表存入SQL数据库里的程序
用概率论的方法实现理想化程序 - 批量插入数据库
Informix 数据库一致性检查 - Unix爱好者家园
Delphi开发单机瘦数据库程序要点
注意检查程序也有时效性
一个实用的Delphi屏幕截图程序的设计
一个很实用的清除电脑垃圾的小程序
请问VB能否在程序运行过程中将变量的值赋给数据库的字段? - VB6论坛 - 编程论坛
Cassandra Hector分布式数据库入门到高效实用实例 - doliu6的专栏 - CSDN博客
VB实用小程序123
DB2数据库部分日常实用操作
MySQL 数据库常用命令 超级实用版分享
数据库在网格计算中的角色 - CSAI.cn程序开发
VC++下使用ADO编写数据库程序概述