Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Linda Webb

Got breaking Oracle news?    Burleson Consulting Oracle News

Click here for more Oracle News Headlines


Hidden SQL memory management parameters revealed

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 the paper. 

_smm_advice_enabled - This parameter is used to enable/disable the calculation for v$pga_advice view. The flags donít contain ADVICE flag and in the QESMM trace we can see lines like :

pga_advice: IMM off (adv=0, auto=1, min=0) => skipped

_smm_advice_log_size -  This parameter is used to overwrite the default size of the PGA advice workarea history log. I havenít tested it.

_smm_auto_cost_enabled -  If set to TRUE then use the AUTO size policy cost functions. The parameter was not tested during my research.

_smm_auto_max_io_size & _smm_auto_min_io_size - Maximum /minimum  IO size (in KB) used by sort/hash-join in auto mode. I havenít experimented with these two parameters either.

_smm_bound - 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.

_smm_control - 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 and there 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.

_smm_freeable_ - The value in KB of the instance freeable PGA memory to retain. I havenít done any tests with this parameter.

_smm_isort_cap - This is a new hidden parameter in Oracle10gR2 which defines the size of work area for old (v1) insertion sort. The default value is 100MB. The new sort is marked as (v2) sort.

_smm_max_size  -   This parameter defines the maximum work area size in auto mode (serial) 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 see later. After one 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.

_smm_min_size - This parameter defines the minimum work area size in auto mode. I have never changed it during my experiments.

_smm_px_max_size - 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.

_smm_retain_size - 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 the time.

_smm_trace - 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.

_pga_max_size - The parameter defines the maximum size of the PGA memory for one process.

_use_ism_for_pga - 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 [11].                                   

_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 [11].

_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.

_use_realfree_heap  - The 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.Ē


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational