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 SGA Sizing for Windows

Oracle Database Tips by Donald Burleson


Question: 

I'm consolidating 15 Oracle instances onto a single server and I need advice on the proper way to size my SGA for each instance.

Answer:

In a shared environment, it's important to observe the optimal sizes for each shared_pool_size (v$shared_pool_advice) and the optimal data buffer size (using v$db_cache_advice). I have complete Oracle SGA sizing details in my book "Oracle Tuning: The Definitive Reference".

Make sure to tread these detailed notes on SGA sizing.

2007 PGA Update:  Oracle technology is constantly changing, so don't miss my new notes on updates to Oracle PGA behavior.  Also see these important notes on over-riding the Oracle PGA defaults.

In 10g, you may consider using Automatic Memory Management (AMM) and set the sga_max_size parameter to give the total SGA allocation.

Wasting expensive RAM resources is a bad thing and the goal of the Oracle DBA is to maximize the SGA region with intelligent SGA sizing techniques.

If you only have Oracle on the server, start by reserving 10% of RAM for Linux or 20% or RAM for Windows.  With whatever RAM is left-over:

  • SGA Sizing - Optimize the instance by determining the optimal size for db_cache_size, shared_pool_size, etc.
     

  • PGA Sizing - Determine the optimal total RAM for PGA regions (pga_aggregate_target) to minimize disk sorts and maximize hash joins

 

Sizing your SGA

The SGA sizing tasks include optimizing settings for sga_max_size (not recommended for large databases where manual allocation is more effective) and the various configuration parameters for db_cache_size, db_xk_cache_size, shared_pool_size, large_pool_size, and other memory objects give the dba better control of system global area (SGA) component sizing.  Some parameters to consider in SGA sizing include:

  • sga_max_size -- This parameter sets the hard limit up to which sga_target can dynamically adjust sizes. Usually, sga_max_size and sga_target will be the same value, but there may be times when you want to have the capability to adjust for peak loads. By setting this parameter higher than sga_target, you allow dynamic adjustment of the sga_target parameter.
     

  • sga_target -- This parameter is new in Oracle Database 10g and reflects the total size of memory footprint a SGA can consume. It includes in its boundaries the fixed SGA and other internal allocations, the (redo) log buffers, the shared pool, Java pool, streams pool, buffer cache, keep/recycle caches, and if they are specified, the non-standard block size caches.

SGA Sizing on a dedicated server

For dedicated Oracle servers, the maximum total RAM SGA size can be computed as follows:

       OS Reserved RAM   - This is RAM required to run the OS kernel and system functions, 20% of total RAM for MS-Windows, and 10% of total RAM for UNIX/Linux

       Oracle Database Connections RAM   - Each Oracle connection requires OS RAM regions for sorting and hash joins.  (This does not apply when using the Oracle multi-threaded server or pga_aggregate_target .) The maximum amount of RAM required for a session is as follows:

2 megabytes RAM session overhead 

+ sort_area_size                                     

+ hash_area_size


        Oracle SGA Sizing for RAM  - This is determined by the Oracle parameter settings.  The total is easily found by either the show sga   command or the value of the sga_max_size parameter.

We should subtract 20 percent from the total available RAM to allow for MS-Windows overhead.  Windows uses RAM resources even when idle, and the 20 percent deduction is necessary to get the real free RAM on an idle server.  Once the amount of RAM on the server is known, we will be in a position to size the Oracle SGA for optimal RAM usage.

Oracle RAM on MS-Windows

To see how much RAM you have on your MS-Windows server, you can go to start --> settings  > control panel --> system, and click on the "general" tab (refer to Figure 1). Here we see that this server has 1,250 megabytes of RAM.


 

The MS-windows system display screen.

Now that we know how to tell the size of our MS-Windows RAM and the size of the SGA, we have to consider the RAM usage for Oracle connections.

Monitoring Server Resources in MS-Windows

In MS-Windows we can use the performance manager screen to observe the resource consumption of the Oracle Windows server (refer to Figure 2). The performance manager is hidden deep inside the Windows menus, but can be found by following start > settings > control panel > administrative tools > performance.

The MS-Windows server performance monitor.

The MS-Windows performance monitor plots three metrics:

  • Green (CPU) - This is the percentage of CPU resources consumed
  • Yellow (RAM) - This is the number of RAM pages per seconds used
  • Blue (DISK) - This is the disk I/O queue length percentage
Let's take a closer look at the MS-Windows performance monitor. Figure 2 is a time-based snapshot of an Oracle databases resource consumption at startup time. These lines form signatures (known usage patterns) that reveals some interesting patterns inside Oracle:

1. RAM Usage The yellow line is RAM usage, and we see the first spike in the RAM when the SGA is allocated and a short spike in RAM as the database is mounted.

2. DISK Usage The blue line is the disk I/O, and we see the disk I/O activity peg at the point where we mount the database. This is because Oracle must touch every data file header to read the system change number (SCN).

3. CPU Usage The green line is CPU and it is interesting to note that the CPU never goes above 50% during Oracle database startup.

Conclusion

For PGA sizing, we need to know the high water mark (HWM) of Oracle connections and the average RAM needs to minimize disk sorts and maximize hash joins.

The high water mark of connected Oracle sessions can be determined in several ways.  One popular method uses Oracle login and logoff system-level triggers to record sessions in a statistics table. 

Another method uses Oracle STATSPACK to display the values from the stats$sysstat   table, or the v$resource_limit view (only after release 8.1.7, because of a bug). 

In sum, SGA sizing is relatively straightforward push-pull of the competing RAM needs of the internal SGA pools (buffer, library cache), the external RAM for PGA, and the OS overhead.

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & 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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.