Sql workarea histograms
From Oramosswiki
--
-- Change History
--
-- Date Author Description
-- =========== ================= ================================================
-- 22-JAN-2010 Jeff Moss Created
--
COLUMN pga_size HEADING "PGA|Size" FORMAT A50
COLUMN optimal_executions HEADING "Optimal|Executions" FORMAT 999,999,990
COLUMN onepass_executions HEADING "One Pass|Executions" FORMAT 999,999,990
COLUMN multipasses_executions HEADING "Multi Pass|Executions" FORMAT 999,999,990
COLUMN total_executions HEADING "Total|Executions" FORMAT 999,999,990
SELECT CASE WHEN low_optimal_size < (1024*1024)
THEN TO_CHAR(low_optimal_size / 1024, '999999')||'Kb <= PGA < '||
(high_optimal_size + 1) / 1024 || 'Kb'
ELSE TO_CHAR(low_optimal_size / (1024*1024), '999999')||'Mb <= PGA < '||
(high_optimal_size + 1) / (1024*1024)||'Mb'
END pga_size
, optimal_executions
, onepass_executions
, multipasses_executions
, total_executions
FROM v$sql_workarea_histogram
WHERE total_executions <> 0
ORDER BY low_optimal_size
/
Which gives results like this:
PGA Optimal One Pass Multi Pass Total
Size Executions Executions Executions Executions
-------------------------------------------------- ------------ ------------ ------------ ----------
2Kb <= PGA < 4Kb 67,872,740 0 0 67,872,740
32Kb <= PGA < 64Kb 2 0 0 2
64Kb <= PGA < 128Kb 751,020 8 0 751,028
128Kb <= PGA < 256Kb 753,051 2 0 753,053
256Kb <= PGA < 512Kb 1,474,568 0 0 1,474,568
512Kb <= PGA < 1024Kb 7,901,936 31 0 7,901,967
1Mb <= PGA < 2Mb 1,233,051 10 0 1,233,061
2Mb <= PGA < 4Mb 779,170 241 0 779,411
4Mb <= PGA < 8Mb 714,333 2,409 0 716,742
8Mb <= PGA < 16Mb 642,626 2,803 0 645,429
16Mb <= PGA < 32Mb 621,583 6,018 0 627,601
32Mb <= PGA < 64Mb 708,622 21,194 20 729,836
64Mb <= PGA < 128Mb 545,959 109,363 12,275 667,597
128Mb <= PGA < 256Mb 88,400 125,943 40,081 254,424
256Mb <= PGA < 512Mb 51,365 74,088 26,172 151,625
512Mb <= PGA < 1024Mb 46,355 57,609 24,524 128,488
1024Mb <= PGA < 2048Mb 11,717 43,732 12,593 68,042
2048Mb <= PGA < 4096Mb 2,211 9,621 1,967 13,799
4096Mb <= PGA < 8192Mb 651 1,732 229 2,612
8192Mb <= PGA < 16384Mb 18 16 34 68
32768Mb <= PGA < 65536Mb 0 0 16 16