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
