關於sort/hash區域大小的設定

關於sort/hash區域大小的設定

--  The following query against v$sesstat will show information about sorts across the entire database.
SELECT name, value
  FROM v$sysstat
  WHERE name like 'sort%'
UNION
SELECT 'disk sort percent', TRUNC(a.value/(a.value+b.value)*100,2)
  FROM v$sysstat a, v$sysstat b
  WHERE a.name = 'sorts (disk)'
  AND b.name = 'sorts (memory)'
UNION
SELECT 'rows per sort', TRUNC(c.value/(a.value+b.value))
  FROM v$sysstat a, v$sysstat b, v$sysstat c
  WHERE a.name = 'sorts (disk)'
  AND b.name = 'sorts (memory)'
  AND c.name = 'sorts (rows)'
/

-- If 'disk sort percent'>5% pls increase 'sort_area_size'.

 
-- The following query produces information about temporary segment utilization 
SELECT s.sid, u.segtype, blocks
  FROM v$sort_usage u, v$session s
  WHERE u.session_addr = s.saddr

/

-- If there are many 'hash' record occurs in 'segtype',increate hash_area_size

 -- I am testing ....