Prompt
PROMPT
Prompt 7.0 Sort Area Size
PROMPT
Prompt
TTILE LEFT "*** sort isa ***" 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 Investness
Prompt The Lower The Value of The Sorts To Disk,
Prompt the better the sort ispeforming.
Prompt sort_area_size can't be increase large to disk 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 shouth 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_USAGEPROMPT 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
TTILE OFF
Prompt
PROMPT
Prompt 7.1 SEQUENCE_CACHE_ENTRIES
PROMPT
Prompt
TTILE 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 Investness
Prompt sequence_cache_entries shop set to 1000
Prompt <<<<<<<<<<<<<<<<<< Note: >>>>>>>>>>>>>>>>>>>>>> PROMPT
TTILE OFF
Prompt
PROMPT
PROMPT 9.0 ROLLBACK Segment Contention
PROMPT
Prompt
TTILE 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
TTILE OFF
Prompt
PROMPT
PROMPT 9.1 ROLLBACK Segment Contention
PROMPT
Prompt
TTILE 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 Theprompt 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
TTILE OFF
Prompt
PROMPT
PROMPT 10.0 Free Space Co. Coalesced
PROMPT Prompt
TTILE 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 Co. Coalesced Should Be 100%
Prompt USE "Alter TableSpace
Prompt <<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Prompt
TTILE OFF
Prompt
PROMPT
PROMPT 11.0 LATCH CONTENTION
PROMPT 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 INIT.ORA FILE
Prompt <<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Prompt
TTILE OFF
Prompt
PROMPT Prompt 11.1 Latch Contention (Reveal Net)
PROMPT
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
TTILE OFF
Prompt
PROMPT Prompt 12.0 TableSpace USAGE
PROMPT
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 * ((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.tablespa_name, df.bytes, df.blocks
Order by fs.tablespace_name
/
Prompt
Prompt <<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Prompt 12.0 Investness
Prompt if a TableSpace Has All DataFiles with% Used Greater
Prompt <<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Prompt
TTILE OFF
Prompt
PROMPT
PROMPT 13.0 Disk Activity
PROMPT
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 is not on the same disk. / THE SAME DIS.
Prompt <<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Prompt
TTILE OFF
Prompt
PROMPT PROMPT 14.0 Fragmentation NEED
PROMPT
Prompt
Set heading on
Set Termout on
Set PageSize 66
TTITLE LEFT "*** Database:" xdbname ", defragmentation new 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 recularly to insure extents
PROMPT ('Frag NEED') Do Not Get Much Above 2.
Prompt <<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Prompt
TTILE OFF
Prompt
PROMPT
PROMPT 15.0 Prompt High Water Mark (HWM)
Prompt Perform "Analyze Table
