关于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 ....
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-21577/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/6906/viewspace-21577/