What is the recommended part of (sga_max_size+pga_aggregate_target) to total unix all physical memor

[xiongye_2]

What is the recommended part of (sga_max_size+pga_aggregate_target) to total unix all physical memory ? For example our aix system has 8G mem ,I think sga_max is 4.5G and pga_aggregate_target is 2G and other 1.5G give os , Is it a good start point memory allocation for oracle on OLTP system ? Thanks !!

[Lee Tae-kyung]

Hi,
Refer to this..
In fact, I have heard that The total memory of SGA shouldn't be more than half of the physical memory.
Have a nice day...

TaeKyung Lee
From Korea.

This report is from Oracle.

SCOPE & APPLICATION ------------------- This article detail information on 9i new init.ora parameters that affect performance. Top Oracle 9i init.ora parameters affect performance. -------------------------------------------------------- The parameters listed below are new 9i parameters that affect database performance and should be tuned properly to get the optimal database performance. -- cursor_sharing db_cache_size db_keep_cache_size db_recycle_cache_size db_16k_cache_size db_2k_cache_size db_32k_cache_size db_4k_cache_size db_8k_cache_size db_cache_advice pga_aggregate_target sga_max_size statistics_level workarea_size_policy -- - CURSOR_SHARING The parameter is not a 9i new parameter , but in 9i it can be set to new values. This parameter determines what kind of SQL statements can share the same cursors. Values: FORCE Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement. SIMILAR Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. EXACT Only allows statements with identical text to share the same cursor. Setting this parameter to FORCE or SIMILAR will cause similar SQL statement to be shared and will reduce the parsing overhead for parsing similar SQL statement that differs only in literal values. Setting this parameter also can have negative side effects, Replacing literals with bind variable will cause the optimizer to choose different execution plan that may make some queries perform slower than before , so you should be careful setting this parameter and you should do intensive testing to your application before setting it , also consult your application vendor before setting it. - DB_CHACHE SIZE DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE parameter). The value must be at least the size of one granule (smaller values are automatically rounded up to the granule size). A value of zero is illegal because zero is the size of the DEFAULT pool for the standard block size, which is the block size for the SYSTEM tablespace. This parameter will replace DB_BLOCK_BUFFERS parameter, which will be deprecated in future versions. Oracle recommends that you use DB_CACHE_SIZE instead. The value of this parameter will determine the size of the default buffer cache which also affect the buffer cache hit ratio Please refer to for further information on Tuning the Database Buffer Cache. - DB_KEEP_CACHE_SIZE & DB_RECYCLE_CACHE_SIZE These 2 parameters replace the BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE parameters that were used in Oracle 8i DB_KEEP_CACHE_SIZE & DB_RECYCLE_CACHE_SIZE specifies the size of the KEEP & RECYCLE buffer pool. The size of the buffers in the KEEP & RECYCLE buffer pool is the primary block size (the block size defined by the DB_BLOCK_SIZE parameter). - DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_16K_CACHE_SIZE, DB_32_CACHE_SIZE These are new parameters in 9i. In Oracle9i, the database buffer cache can be composed of 7 sub-caches. DB_nK_CACHE_SIZE specifies the size of the cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=4096, it is illegal to specify the parameter DB_4K_CACHE_SIZE (because the size for the 4 KB block cache is already specified by DB_CACHE_SIZE). So in oracle 9i we can create tablespaces with different block sizes and each tablespace will use the Sub-buffer cache that match it's block size. this will also mean that we can have OLTP and DSS applications sharing the same Database each application is using the proper Blocks size and also has it's own buffer cache . also this feature will make transporting tablespace between databases with different block size possible and easy to do - DB_CACHE_ADVICE DB_CACHE_ADVICE enables or disables statistics gathering used for predicting behavior with different cache sizes through the V$DB_CACHE_ADVICE performance view. This parameter will be replaced in oracle 9.2.0 with the STATISTIC_LEVEL parameter Values: OFF Advisory is turned off and the memory for the advisory is not allocated. READY Advisory is turned off but the memory for the advisory remains allocated. Allocating the memory before the advisory is actually turned on avoids the risk of an error when you switch the parameter to ON. If the parameter is switched to this state from ON, the contents of the view are preserved and the memory for the advisory is retained. If the parameter is switched to this state from OFF, you may get an error. ON Advisory is turned on. CPU and memory overheads are incurred. Attempting to set the parameter to this state when it is already in the OFF state may result in an error. Otherwise, the view (V$DB_CACHE_ADVICE) is reset and statistics are gathered to the newly refreshed view. If the parameter is in the READY state, you can set it to ON without any errors because the memory is already allocated. The view is reset and statistics are displayed in the newly refreshed view. For more information about cache advisory please refer to [NOTE:148511.1] "Oracle9i NF: Dynamic Buffer Cache Advisory" - PGA_AGGREGATE_TARGET PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance. You must set this parameter to enable the automatic sizing of SQL working areas used by memory-intensive SQL operators such as sort, group-by, hash-join, bitmap merge, and bitmap create. Oracle uses this parameter as a target for PGA memory. Use this parameter to determine the optimal size of each work area allocated in AUTO mode (in other words, when WORKAREA_SIZE_POLICY is set to AUTO. Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk. When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET. pga_aggregate_target Please Note that if you create a new 9i Database, the default value for this parameter will be 25 MB, which wil not be sufficient for most Production Databases. So start with an approximate value of say 50MB based on how much total memory is available to your instance. Then using the statspack reports and the Pga advisory views( as mentioend below) monitor the PGA usage and then accordingly increase or decrease the PGA_AGGREGATE_TARGET. Leaving this parameter at default value on a heavily loaded system might cause excessive swapping and may even lead to running the box out of memory if it is not properly tuned. This could severely impact performance. For more information about cache advisory please refer to the following notes [NOTE:147806.1] Oracle9i New Feature Automated SQL Execution Memory Management [NOTE:148346.1] Oracle9i Monitoring Automated SQL Execution Memory Management [NOTE:223730.1] Automatic PGA Memory Managment in 9i If PGA_AGGREGATE_TARGET is set in the init.ora, then SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_bitmap_AREA_SIZE are ignored. If PGA_AGGREGATE_TARGET is set in init.ora, then WORKAREA_SIZE_POLICY defaults to AUTO. - SGA_MAX_SIZE SGA_MAX_SIZE specifies the maximum size of SGA for the lifetime of the instance. In Oracle8i Database Administrators (DBAs) do not have any control over the SGA size once the instance is started. Oracle9i allows a DBA to modify the SGA size dynamically. This provides an SGA that will grow and shrink in response to a DBA command. This feature implements an infrastructure to allow the Oracle server to change the SGA configuration without shutting down the instance. The Oracle Server can modify the physical address space use to respond to the operating system's use of physical memory For more information about cache advisory please refer to the following note [NOTE:148495.1] Oracle9i New Feature Dynamic SGA - STATISTICS_LEVEL STATISTICS_LEVEL sets the statistics collection level of the database. Oracle9i Release 2 introduces a comprehensive set of advisories including shared pool sizing advisor, SQL Execution Memory (PGA) Memory Advisor and Recovery Cost Estimator. All the advisories in Oracle9i Release 2 including the Buffer Cache Advisor are controlled by a newly introduced parameter STATISTICS_LEVEL. The Parameter DB_CACHE_ADVICE has, therefore, been deprecated in Oracle9i Release 2. By default, the STATISTICS_LEVEL parameter is set to TYPICAL thereby enabling all the advisories. V$STATISTICS_LEVEL lists the status of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter. Each row of V$STATISTICS_LEVEL represents one of these statistics or advisories. For more details on the STATISTICS_LEVEL parameter, please refer to Oracle9i Release 2 Performance Tuning Guide and Reference. [NOTE:259394.1] Init.ora Parameter "STATISTICS_LEVEL" Reference Note - WORKAREA_SIZE_POLICY WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned. Values: AUTO Work areas used by memory-intensive operators are sized automatically, based on the PGA memory used by the system, the target PGA memory set in PGA_ AGGREGATE_TARGET, and the requirement of each individual operator. You can specify AUTO only when PGA_AGGREGATE_TARGET is defined. MANUAL The sizing of work areas is manual and based on the values of the *_AREA_ SIZE parameter corresponding to the operation (for example, a sort uses SORT_ AREA_SIZE). Specifying MANUAL may result in sub-optimal performance and poor PGA memory utilization.

[Sanjay Kumar Suri]

I have following info which may help:

Method 1

1. For Data Warehouse: PGA_AGGREGATE_TARGET = * 40%
2. For OLTP: PGA_AGGREGATE_TARGET = * 20%

Method 2

Use the following formula as an additional guide value and lower limit:
pga_aggregate_target >= sort_area_size+hash_area_size+ bitmap_merge_size+create_bitmap_area_size) * (#shadow processes / 10)

sga_max_size=
db_cache_size+db_keep_cache_size+db_recycle_cache_size+db_nk_cache_size+shared_pool_size+large_pool_size+java_pool_size+log_buffers+1m

sks

[SteveKirby]

You have some good answers already.

My common sense answer is:
Monitor memory usage at peak times on your database. Observe (through top or vmstat) what available physical memory there is.
Increase Oracle memory usage until all (or 95% anyway) of physical memory is utilized.

Steve