Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle dynamic memory management tips

Oracle Database Tips by Donald BurlesonDecember 10, 2015

See these important notes scripts for detecting AMM resize operations.

When I first wrote my book "Creating a self tuning Oracle database" (hence revised in "Oracle Tuning: The Definitive Reference"), I noted that the future of Oracle is a proactive method whereby the major Oracle SGA RAM regions (shared pool, buffer caches) can be adjusted in anticipation of changes to processing needs.

When Oracle9i first allowed "alter system" commands to morph the SGA, Oracle 10g introduced Automatic Memory Management, a reactive tool to re-size the RAM regions. 

Note:  When using AMM by setting sga_target and sga_memory_max, the values for the 'traditional? pool parameters (db_cache_size, shared_pool_size, &c) are not ignored.  Rather, they will specify the minimum size that Oracle will always maintain for each sub-area in the SGA.

While AMM is fine for smaller systems, there are reports of AMM causing performance problems:

In sum, most shops continue to use self-created memory management tools because of the performance hits and the reactive nature of AMM, which does not anticipate upcoming changes in possessing, predictions which can now be made by analyzing STATSPACK and AWR data.

However, in a highly active database, dynamic memory reconfiguration can cause sporadic performance issues.

A case study in dynamic memory management

The IBM P590 AIX Mainframe

I was at a busy OLTP shop recently that had a one terabyte database with a ten gigabyte data buffer cache, running an IBM P590 with 18 CPU's and 128 gig RAM, split into logical partitions (LPAR's) for competing applications.

With only 1% of the data space available for caching, this database was heavily I/O bound, a typical workload profile for a well-tuned large OLTP environment.  Note that this database carries almost 100,000 reads per second, and over 4,000 user calls and about 300 transactions per second, with over 50% of wait times of sequential reads:

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:    11,504M      Std Block Size:         8K
           Shared Pool Size:       592M          Log Buffer:     1,280K
 
Load Profile                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:          1,816,614.40              6,098.30
              Logical reads:             89,851.90                301.63
              Block changes:              7,266.68                 24.39
             Physical reads:              3,227.66                 10.84
            Physical writes:              1,233.78                  4.14
                 User calls:              4,044.55                 13.58
                     Parses:                154.38                  0.52
                Hard parses:                  3.64                  0.01
                      Sorts:                330.61                  1.11
                     Logons:                  0.22                  0.00
                   Executes:              2,885.80                  9.69
               Transactions:                297.89
 
 . . .
 
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read                           568,948       4,375    66.15
CPU time                                                        1,983    29.99
db file scattered read                            106,287          65      .99
log file sync                                       7,053          50      .75
log buffer space                                    1,717          47      .71

Problems in paradise

The DBA related that he starting to get panicked phone calls from their end-users and he noted a huge rush against their library cache caused by a flood incoming SQL requests.

At the time, a 10 minute snapshot revealed that this system was experiencing 36,000 logical reads per seconds and it was hung on library cache load locks and pin events, taking over 80% of system elapsed time.

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:    11,504M      Std Block Size:         8K
           Shared Pool Size:       400M          Log Buffer:     1,280K
 
Load Profile                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            196,617.69                632.75
              Logical reads:             36,311.98                116.86
              Block changes:              1,114.83                  3.59
             Physical reads:              2,164.71                  6.97
            Physical writes:                958.11                  3.08
 
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
library cache load lock                             9,708      13,194    49.20
library cache pin                                   8,084       9,425    35.14
CPU time                                                        1,538     5.73
kksfbc child completion                           152,610       1,495     5.57
db file sequential read                           190,283         738     2.75

In this case, the DBA decided to increase his shared_pool_size, a act which was not necessarily the root cause of this problem, which I suspect was a flood of non-reusable SQL statements from an ad-hoc reporting tool. 

Another STATSPACK snapshot revealed Oracle in the process of adding RAM to the shared pool:

Note that the database continued to process the load, but we see a rare time-5 timed event "background parameter adjustment", as the "alter system set shared_pool_size=592m" command reconfigured the SGA:

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:    11,504M      Std Block Size:         8K
           Shared Pool Size:       592M          Log Buffer:     1,280K
 
Load Profile                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            417,109.35              1,414.69
              Logical reads:            156,148.30                529.60
              Block changes:              2,122.03                  7.20
             Physical reads:              6,358.78                 21.57
            Physical writes:              1,041.54                  3.53
                 User calls:              2,765.77                  9.38
                     Parses:                164.26                  0.56
                Hard parses:                  2.59                  0.01
                      Sorts:                184.62                  0.63
                     Logons:                  0.21                  0.00
                   Executes:              1,704.78                  5.78
               Transactions:                294.84
 
 
 . . .
 
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                        2,733    44.85
db file sequential read                           677,443       2,494    40.93
background parameter adjustment                    58,882         580     9.51
db file scattered read                            174,315         153     2.50
SQL*Net message from dblink                           113          53      .87

While I've not yet analyzed the details of the DBA's analysis of this problem, it provides a fascinating insight into the behavior of a large OLTP system when the SGA of a busy OLTP system is altered.

Warnings about AMM

Quest Software's Guy Harrison has this warning about using the AMM:

"When you use MTS and AMM (or ASMM) together, PL/SQL programs that try to create large collections can effectively consume all available server memory with disastrous consequences . .

AMM allocates virtually all memory on the system to the large pool in order to accommodate the PL/SQL memory request.  First it consumes the buffer cache, then it reduces the PGA_AGGREGATE_TARGET - all the way to zero!"

Here are my related noted on Oracle dynamic memory management:

If you like Oracle tuning, you might enjoy my 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 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.