|
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", [1] [2] [3] [4] [5] [6] 下一页 | 
