| nbsp;
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_ 上一页 [1] [2] [3] [4] [5] [6] 下一页 | 
