Call now: 252-767-6166  
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 









Oracle UNIX Administration Viewing RAM Memory

Oracle UNIX/Linux Tips by Burleson Consulting

Viewing RAM memory usage for specific SQL statements

Oracle9i now has the ability to display RAM memory usage along with execution plan information.  To get this information you need to gather the address of the desired SQL statement from the v$sql view.  For example, if you have a query that operates against the NEW_CUSTOMER table, you can run the following query to get the address:

   sql_text like ?%NEW_CUSTOMER?;


1 row selected.

Now that we have the address, we can plug it into the following script to get the execution plan details and the PGA memory usage for the SQL statement.

   object_name                        name,
   trunc(bytes/1024/1024)             "input(MB)",
   trunc(last_memory_used/1024)       last_mem,
   trunc(estimated_optimal_size/1024) opt_mem,
   trunc(estimated_onepass_size/1024) onepass_mem,
   decode(optimal_executions, null, null,
          multipasses_exections)      "O/1/M"
   v$sql_plan     p,
   v$sql_workarea w

Here is the listing from this script.

------------ -------- ---- --------- -------- ---------- ---------- ----
SELECT STATE                                                                 
SORT         GROUP BY           4582        8         16         16 26/0/0
HASH JOIN    SEMI               4582     5976       5194       2187 16/0/0
TABLE ACCESS FULL     ORDERS      51                                     
TABLE ACCESS FUL      LINEITEM  1000                

Here we see the details about the execution plan along with specific memory usage details. This is an exciting new advance in Oracle9i and gives the Oracle DBA the ability to have a very high level of detail about the internal execution of any SQL statement.

Moving toward a self-tuning Oracle9i database

With these new dynamic SGA features in Oracle9i, we are moving toward an architecture where the Oracle DBA can monitor UNIX RAM memory usage and re-configure the SGA and PGA regions according to existing usage patterns.

Oracle offers a degree of self-tuning capability with the new pga_aggregate_target parameter.  By allowing Oracle9i to manage RAM memory demands according to the demands of each task, Oracle9i has been able to use sophisticated algorithms to improve the speed of RAM intensive tasks such as hash joins and large sorts.

However, the Oracle DBA is now able to dynamically de-allocate RAM memory from one area and re-allocate the RAM to another area of the SGA.

Changing RAM configuration with UNIX scripts

In a UNIX environment it is very easy to schedule a task to change the RAM memory configuration when the processing needs change.  For example, many Oracle database operate in OLTP mode during normal work hours, while at night the database services memory-intensive batch reports.

As we have noted, an OLTP database should have a large value for db_cache_size while memory-intensive batch tasks require additional RAM in the pga_aggregate_target.

The UNIX scripts below can be used to re-configure the SGA between OLTP and DSS without stopping the instance.  In this example, we assume that we have an isolated Oracle server with 8 gigabytes of RAM.  We also assume that we reserve 20% of RAM for UNIX overhead, leaving a total of 6 gigabytes for Oracle and Oracle connections.  These scripts are for HP/UX or Solaris, and accept the $ORACLE_SID as an argument.

The dss_config.ksh script will be run at 6:00 PM each evening to re-configure Oracle for the memory-intensive batch tasks that run each night.


# First, we must set the environment . . . .

ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export PATH

$ORACLE_HOME/bin/sqlplus ?s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size=1500m;
alter system set shared_pool_size=500m;
alter system set pga_aggregate_target=4000m;

The oltp_config.ksh script will be run at 6:00 AM each morning to re-configure Oracle for the OLTP usage during the day.


# First, we must set the environment . . . .

ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export PATH

$ORACLE_HOME/bin/sqlplus ?s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size=4000m;
alter system set shared_pool_size=500m;
alter system set pga_aggregate_target=1500m;

Note: You can also use the dbms_job package to schedule these types of reconfiguration events.

Now that we see a generic way to change the Oracle configuration it should be clear that we can develop a mechanism to constantly monitor the processing demands on Oracle and issue the alter system commands according to existing database demands.

Approaches to self-tuning Oracle databases

Until Oracle9i evolves into a complete self-tuning architecture, the Oracle DBA is responsible for adjusting the RAM memory configuration according to the types of connections.  In general, we can use queries against the v$ structures and STATSPACK to locate those times when Oracle connections change their processing characteristics. We see three types of approaches to automated tuning:

* Normal scheduled re-configuration ? A bi-modal instance that performs OLTP and DSS during regular hours will benefit from a scheduled task to re-configure the SGA and PGA.

* Trend-based dynamic reconfiguration ? You can use STATSPACK to predict those times when the processing characteristics change and use the dbms_job package to fire ad-hoc SGA and PGA changes.

* Dynamic reconfiguration ? Just as Oracle9i dynamically re-distributes RAM memory for tasks within the pga_aggregate_target region, the Oracle DBA can write scripts that steal RAM from an underutilized area and re-allocate these RAM pages to another RAM area.

Rules for changing memory sizes

There are three conditions that effect the decision to re-size the Oracle RAM regions, one for the data buffer cache, another for the shared pool and the third for PGA memory usage.

* db_cache_size ? We may want to add RAM to the data buffer cache when the data buffer hit ratio falls below a pre-defined threshold.

* shared_pool_size ? A high value for any of the library cache miss rations may signal the need to allocate more memory to the shared pool.

* pga_aggregate_target ? When we see high values for multi-pass executions, we may want to increase the available PGA memory.

Let?s take a close look at each of these conditions.

Adjusting the pga_aggregate_target parameter

We may want to dynamically change the pga_aggregate_target parameter when any one of the following conditions are true:

* Whenever the value of the v$sysstat statistic ?estimated PGA memory for one-pass? exceeds pga_aggregate_target, then we want to increase pga_aggregate_target.

* Whenever the value of the v$sysstat statistic ?workarea executions ? multipass?  is greater than 1%, the database may benefit from additional RAM memory.           

* You may over-allocate PGA memory, and you may consider reducing the value of pga_aggregate_target whenever the value of the v$sysstat row ?workarea executions ? optimal? consistently measures 100%.

Changing the shared_pool_size parameter

We all know from Oracle8, that Oracle offers several queries for determining when the Oracle shared pool is too small. The library cache miss ratio tells the DBA whether or not to add space to the shared pool, and it represents the ratio of the sum of library cache reloads to the sum of pins.

In general, if the library cache ratio is over 1, you should consider adding to the shared_pool_size. Library cache misses occur during the parsing and preparation of the execution plans for SQL statements. The compilation of a SQL statement consists of two phases: the parse phase and the execute phase. When the time comes to parse a SQL statement, Oracle first checks to see if the parsed representation of the statement already exists in the library cache. If not, Oracle will allocate a shared SQL area within the library cache and then parse the SQL statement. At execution time, Oracle checks to see if a parsed representation of the SQL statement already exists in the library cache. If not, Oracle will reparse and execute the statement.

The following STATSPACK script will compute the library cache miss ratio. Note that the script sums all of the values for the individual components within the library cache and provides an instance-wide view of the health of the library cache.

set lines 80;
set pages 999;

column mydate heading 'Yr.  Mo Dy  Hr.' format a16
column c1 heading "execs"    format 9,999,999
column c2 heading "Cache Misses|While Executing"    format 9,999,999
column c3 heading "Library Cache|Miss Ratio"     format 999.99999

break on mydate skip 2;

   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   sum(new.pins-old.pins)                c1,
   sum(new.reloads-old.reloads)          c2,
   sum(new.pins-old.pins)                library_cache_miss_ratio
   stats$librarycache old,
   stats$librarycache new,
   stats$snapshot     sn
   new.snap_id = sn.snap_id
   old.snap_id = new.snap_id-1
   old.namespace = new.namespace
group by
   to_char(snap_time,'yyyy-mm-dd HH24')

Here is the output. The report above can easily be customized to alert the DBA during times when there are excessive executions or library cache misses.

                               Cache Misses
Yr.  Mo Dy  Hr.       execs While Executing LIBRARY_CACHE_MISS_RATIO
---------------- ---------- --------------- ------------------------
2001-12-11 10        10,338               3                   .00029
2001-12-12 10       182,477             134                   .00073
2001-12-14 10       190,707             202                   .00106
2001-12-16 10         2,803              11                   .00392

Once this report identifies a time period where there may be a problem, STATSPACK provides the ability to run detailed reports to show the behavior of the objects within the library cache. In the above example, we see a clear RAM shortage in the shared pool between 10:00 AM and 11:00 AM each day.  In this case we could dynamically re-configure the shared pool with additional RAM memory from the db_cache_size during this period.


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


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