PGA Work Area Histogram by Memory Size
- From: "Sam Bootsma" <sbootsma@xxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <'oracle-l@xxxxxxxxxxxxx'>
- Date: Wed, 15 Feb 2006 08:25:08 -0500
Hello, see questions below ....
select low_optimal_size/1024 low_kb, (high_optimal_size+1)/1024 high_kb,
optimal_executions, onepass_executions, multipasses_executions
from v$sql_workarea_histogram
where total_executions != 0;
LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
MULTIPASSES_EXECUTIONS
---------- ---------- ------------------ ------------------
----------------------
8 16 64621975 0
0
16 32 106265 0
0
32 64 116750 0
0
64 128 52384 20
0
128 256 7537 0
0
256 512 98963 30
0
512 1024 92970 0
0
1024 2048 8208 18
0
2048 4096 7442 4
0
4096 8192 4547 64
0
8192 16384 1221 401
2
16384 32768 82 214
0
32768 65536 0 100
0
65536 131072 2 18
0
131072 262144 3 80
0
524288 1048576 0 66
0
8388608 16777216 0 0
2
I run the query above (and other PGA related queries) once per day.
After the last execution, I noticed two sorts that allocated between 8
GB and 16 GB of memory - but this still was not enough to sort the data
in one pass. It required multiple passes over the data to perform the
sort. The Oracle documentation makes it clear that a multi-pass sort is
very undesirable.
I remember reading in the Oracle documentation that a 1 GB piece of data
can sort in one-pass mode using just 22 MB of memory. Here I have a
piece of data (or two pieces) that requires multiple passes and it has
between 8 GB and 16 GB of memory at its disposal. This makes me wonder
what humungous piece of data needs multi-pass mode when it has between 8
and 16 GB of memory at its disposal! I figure that if this runs during
the day, it could kill the system, and it certainly puts a heavy load on
it whether it runs during the day or at night.
A few questions:
1. Do I properly understand this? Is this as serious as I think it is?
2. Is there a way to capture the SQL that is causing a multi-pass sort
as it is happening? If so, does anybody have a script to do this (or
the performance views to look at).
3. I see a multi-pass sort that used a memory bucket size between 8 MB
and 16 MB. I wonder why it did not use more memory and do the sort in
one-pass mode. The only thing I can think of is that memory was tight
at the time, and Oracle only wanted to allocate a small amount of memory
at the time. Does this make sense?
Thanks!
Sam.
Sam Bootsma
Oracle DBA
George Brown College
sbootsma@xxxxxxxxxxxxx <mailto:sbootsma@xxxxxxxxxxxxx>
416-415-5000 x4933
Other related posts:
- » PGA Work Area Histogram by Memory Size
