Analyzing Sorting
select 'Sorts in Memory' "Ration",
ROUND(
(SELECT SUM(value) FROM v$SYSSTAT WHERE name = 'sorts (memory)') /
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name IN ('sorts (memory)', 'sorts (disk)'))* 100,2)
|| '%' "Percentage"
FROM DUAL;
-- find SQL statements with high sorting amounts
Select * from (
Select sorts, executions, sql_text
from v$sqlarea
order by sorts desc)
where rownum < 10 ;
--heaviest impact
select * from (
select sorts, executions, sql_text
from v$sqlarea order by executions*sorts desc)
where rownum <10;