Hidden SQL memory management
In a paper titled ďAdvanced Management Of Working
Areas in Oracle 9i/10gĒ, author Joze Senegacnik notes internals for
Oracle PGA management in 10g release 2, including important hidden
parameters that govern the automatic SQL memory management within
the PGA regions.
(download password is ďnetworkĒ)
ďThere are several hidden initialization parameters that govern the
behaviour of automatic SQL memory management (QESMM). Some of them I
changed very intensively during my tests, but there are plenty of
them I havenít tested as I ran out of time and had to finish this
presentation. Obviously there is a huge research work left for the
future. Therefore I only comment some of the parameters whereas some
of them are explained in more details either here or elsewhere in
This parameter is used to enable/disable the calculation for v$pga_advice
view. The flags
donít contain ADVICE
and in the QESMM trace we can
lines like :
pga_advice: IMM off (adv=0, auto=1, min=0) => skipped
This parameter is used to overwrite the default size of the
PGA advice workarea history log. I havenít tested it.
If set to
TRUE then use the
cost functions. The parameter was not tested during my research.
- Maximum /minimum IO size (in KB) used by
auto mode. I havenít experimented with these two parameters either.
This parameter one can use to overwrite the memory manager
automatically computed bound. I performed very limited testing with
this parameter. When once set one canít easily reset its calculation
back to automatic computation. The only way I found is to reset it
is by changing the P_A_T parameter.
Provides controls on the memory manager. For values >= 32 the query
returns ORA-942 error because
one TKMM schema table is missing. Most likely TKMM stands for Trace
Kernel Memory Manager
are some functions available for advance tracing and for testing
different scenarios. My testing of this parameter was not intensive
and this was merely the only difference I noticed.
The value in KB of the instance freeable PGA memory to retain. I
havenít done any tests with this parameter.
This is a
new hidden parameter in Oracle10gR2 which defines the size of work
area for old (v1) insertion
The default value is 100MB. The
sort is marked as (v2)
This parameter defines the maximum
area size in
and defaults to 50% of _pga_max_size.
In Oracle 9i/10gR1 the maximum value is 0.1GB (100MB), while in
10gR2 it can go up to 0.25GB as we will
of the instance restarts I found that the _pga_max_size
and _smm_max_size had even bigger values with P_A_T set to 4GB.
This parameter defines the minimum
work area size in
mode. I have never changed it during my experiments.
This parameter defines the maximum work area size in auto mode
(global). In Oracle9i/10gR1 this parameter is used to constraint the
size of work area for parallel slaves for DOP > 6. In those versions
the default value for this parameter is 30% of P_A_T.
This parameter defines the work area retain size in SGA for shared
server sessions (0 for AUTO). During my experiments I havenít
changed this parameter as I was using a DEDICATED mode almost all of
This parameter is used to turn on/off tracing for SQL memory
manager. I found that different values produce different details.
The minimum value to produce a trace is 8 (at least in my tests that
was the case). During the experiments I found that levels 8 - 15
produce a very limited trace, while levels over 32 to 63 and 112-127
produce even more detailed trace. I used level 65535 to produce
traces in this paper.
The parameter defines the maximum size of the PGA memory for one
This parameter governs whether ISM (Intimate Shared Memory) can be
used for allocation of large extents. I havenít tested this
parameter, but Tanel Poder mentions it in his presentation about
memory management .
_pga_large_extent_size & _uga_cga_large_extent_size -
These parameters are used to set the size of the PGA large extent
size and UGA/CGA large extent size respectively for initial mmap()
allocation function .
_realfree_heap_max_size & _realfree_heap_mode & _realfree_heap_pagesize_hint
By setting these parameters one can influence the realfree heap
size, the realfree_heap_mode and can hint the realfree page size.
The last one defines the amount of memory which is allocated at a
time for PGA growth. I have performed no tests with these parameters
so I mention them here just for the readerís information.
memory for working areas can be allocated as heap 0 when the
parameter is set to TRUE. This means that the memory is allocated as
a separate heap and not as a sub-heap of the PGA.Ē