Wednesday, March 17, 2010
Memory Parameters in oracle9i
1. Oracle9i introduced new parameter PGA_AGGREGATE_TARGET to tune portion of the memory in PGA. This new parameter dynamically adjust sort_area_size, hash_area_size, create_bitmap_area_size and bitmap_merge_area_size. The WORK_AREA_SIZE_POLICY should be set to AUTO to enable this feature.
2. Prior to oracle9i, shared pool size was static parameter. DBA has to restart the instance if shared pool size parameter needs to be changed. But in oracle9i introduced a new capability to change the shared_pool_size parameter dynamically without restarting the instance. But this parameter size should be less than or equal to SGA_MAX_SIZE.
3. Oracle9i introduced new approach to determine how the buffer cache is being using and how much memory has to be added or released from the buffer cache for optimal performance. Here are the steps to enable to feature.
a) Set the DB_CACHE_ADVICE parameter to ON. It gather statistics on the buffer cache by setting the value of this parameter. This is dynamic paramter and can be changed by using ALTER SYSTEM command.
b) Display the statistics gathered by querying the new dynamic performance view, V$DB_CACHE_ADVICE. This view contains information about the physical reads for the different cache sizes.
select id, name, block_size,buffers_for_estimate, estd_physical_reads, estd_physical_read_factor from v$db_cache_advice
c) We can change the buffer cache size based on the observation in step b.
The source of this article is click
Here are other related link to this topic.
Oracle10g Memory feature
Oracle11g Memory feature
Oracle Memory components
Memory Parameter in Oracle10g
Oracle10g introduced memory management feature to make DBA's life much easy. Automatic shared memory management(ASMM) is another self management enhancement in oracle10g. In previous release of oracle, we had to manually configure the shared pool size, java pool size, large pool size and data base buffer cache. It was often challenge to optimally configure these components because sizing them too small could cause memory errors and sizing them too large could lead to waste of memory.
In oracle10g, you need to specify only the SGA_TARGET parameter, which specifies the total size of the SGA. Individual components of the SGA are automatically allocated by the database based on the workload and history information. so during the normal online operations,the buffer cache and java pool may be bigger. During the batch window, the database can automatically increase the large pool and reduce the buffer cache.
When ASMM is enabled, then the following memory pools are automatically sized:
1 .Buffer cache (DB_CACHE_SIZE)
2. Shared pool (SHARED_POOL_SIZE)
3. Large pool (LARGE_POOL_SIZE)
4. Java pool (JAVA_POOL_SIZE)
If above automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.
The following pools are manually sized components and are not affected by ASMM.
1. DB_KEEP_CACHE_SIZE
2. DB_RECYCLE_CACHE_SIZE
3. DB_nK_CACHE_SIZE (non-default block size)
3. STREAMS_POOL_SIZE
4. LOG_BUFFER
For instance, SGA_TARGET is 400MB, LOG_BUFFER is 1M, DB_KEEP_CACHE_SIZE is 50M, then the memory available for automatically configured components is 349MB.
Questions and Answers :
1. How does ASMM work? ASMM automatically allocate the memory based on the work load and type of DB activities. We do not need to size the SGA parameters. The sga_target parameter will not dynamically manage all the sub component of the SGA memory. Some of the memory areas need to be sized by DBA as i explained above.
2. How do we switch the DB from non-ASMM to ASMM? Switching to ASSM can be done by changing SGA_TARGET parameter to non-zero value. STATISTICS_LEVEL should be TYPICAL or ALL. We need to allocate proper memory size for SGA_TARGET parameter. But this should not be greater then SGA_MAX_SIZE. Again, we need to reset the automatically tunable memory parameter to zero or minimum values. If we set minimum values for automatically tuned parameters, then oracle always maintain the minimum values all the time.
Here my DB is running in Non-ASMM. Let us switch this to ASMM.
SQL> select name,value from v$parameter where
2 name in('shared_pool_size','large_pool_size','java_pool_size','db_cache_size','sga_target');
NAME VALUE
------------------------------ ------------------------------
shared_pool_size 163577856
large_pool_size 4194304
java_pool_size 4194304
db_cache_size 432013312
sga_target 0
SQL> alter system set sga_target=1000M scope=both;
System altered.
SQL> alter system set shared_pool_size=500M scope=both;
System altered.
SQL> show sga
Total System Global Area 1048576000 bytes
Fixed Size 1252976 bytes
Variable Size 553648528 bytes
Database Buffers 486539264 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> select target_size from v$sga_resize_ops
2 where component='shared pool';
TARGET_SIZE
-----------
528482304
SQL>
Now SGA parameters will be automatically tuned based on the workload and DB activities. But it always retain the minimum values, since we set shared_pool_size, large_pool_size, java_pool_size, db_cache_size set to non-zero values.
3. How do we disable ASMM? Disabling ASSM can be done by chaning SGA_TARGET parameter to zero value. But we need to set the below parameter with proper sizing when we disable ASMM
1. shared_pool_size2. large_pool_size
3. java_pool_size
4. db_cache_size
SQL> alter system set sga_target=0 scope=both;
System altered.
4. When do we think that, ASMM should be disabled in oracle10g? We can disable ASMM if there is significant amount of memory resizing activity that cause the performance issue in database. This frequent memory resizing might happen when the environment is mixed of DSS and OLTP environment.
5. What is the best environment to enable the ASMM feature? Based on my knowledge, i would recommend to go for ASMM feature when DB is running on OLTP mode during the day time and DB is running on DSS more during the night time. In this scenario, ASMM feature will resize the memory based on the DB activities. DBA does not need to resize the memory for day time and night time differently.
Here are the relevant link to this topic.
Oracle9i Memory feature
Oracle11g Memory feature
Oracle Memory components
Tuesday, March 16, 2010
Memory Parameter in Oracle11g
I would like to write how this memory parameter works in oracle11g. How the memory is configured on each sub component when memory_target parameter is set to non zero?
Oracle11g introduced two memory parameters(memory_target, memory_max_target). oracle has become smart as in exchanging memory between SGA and PGA. This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.
Question and Answers :
1. What is the advantage of memory_target parameter? I do not see any reason to not to use this memory management parameter. If we set this parameter, then we do not need to size all other sub component of this parameter. Sub component parameters are like SGA_TARGET, PGA_AGGREGATE_TARGET etc. These memory area can be managed dynamically.
2. What is the best place to leverage this feature? If your database running in mixed load, Oracle will adjust all your memory setting accordingly to DB load. Let us say, your database is running on OLTP mode in the day time, and running batch job during the night time. DBA can not adjust the different component of the memory according to Database activity. This memory_target parameter balance the memory between shared_pool_size, redolog_buffer, large_pool_size, java_pool_size etc. This is ideal scenario to use this feature.
3. How do we enable AMM feature ? This feature can be enabled by setting memory_target and max_memory_target to non-zero values. LOCK_SGA parameter should be set to FALSE to enable this feature.
4. How do we disable AMM feature ? This feature can be disabled by setting zero for memory_target parameter. Please remember, sga_target, pga_aggregate_target parameter should be sized according to DB activities.
5. Do we need to check the Shared memory in server before implementing AMM? Yes. We need to check the current size of your shared memory file system before we set the AMM feature. On linux, we need to use the below command to check the shared memory.
[oracle@rate9812~]$ df -k /dev/shm
Filesystem 1k-blocks Used Available Use% Mounted on tmpfs
2023256 1065000 958256 53% /dev/shm
[oracle@rate9812~]$
Here the shared memory is around 2GB. So the memory target can be more than 2GB. If it is more than 2GB, the we get the below error.
ORA-00845: MEMORY_TARGET not supported on this system
6. Do we have any new data dictionary views to monitor AMM? Yes. we do have new views. Here are some. These views helps to monitor the Memory utilization.
V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE
6. How does this feature differ from Oracle10g memory feature? This parameter covers both SGA and PGA. In Oracle10g, we use individual parameter for SGA and PGA. Now it is not required to define or size the memory for SGA and PGA seperately.
7. Do we need to take care of any memory parameters after AMM feature is enabled? Log buffer and the non-default block size caches not part of dynamic memory allocation. These memory should be taken care manually.
The new data dictionary V$MEMORY_DYNAMIC_COMPONENTS will give the list of components which is taken care by database when we enable AMM feature.
Here are relevant topic to this article.
Oracle9i Memory feature
Oracle10g Memory feature
Oracle Memory components
Tuesday, March 9, 2010
Oracle Memory Management
First let me explain about each memory component in oracle. Oracle Memory is divided into two portion. One is System Global Area(SGA) and another one is Program Global Area(PGA). Let us now discuss about SGA now. Then we will talk about PGA in the later portion of this article.
System Global Area consists of following Memory component.
1. Fixed size
2. Variable size
3. DB Buffer cache
4. Redo log buffer
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning option
SQL> show sga
Total System Global Area 1654013952 bytes
Fixed Size 2103048 bytes
Variable Size 1342179576 bytes
Database Buffers 301989888 bytes
Redo Buffers 7741440 bytes
SQL>
SGA = Fixed size + Variable size + DB buffers + Redo buffers
What is Fixed size?
The size of the fixed portion is constant for a release and a platform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters. This memory varies in size from platform to platform and release to release. This is something over which we have no control and it is generally very small. Think of this area as a bootstrap section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.
What is Variable size?
Variable size is sum of shared pool, java pool, large pool and streams pool. The streams pool is added in oracle10g.
Shared pool = library cache + dictionary cache
Variable size = Shared pool + Java pool + large pool + streams pool
Let us briefly talk about each component in variable size.
Shared pool : It consists of Library cache and Dictonary cache.
The library cache stores shared SQL, caching the parse tree and the execution plan for every unique SQL statement. If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.
The dictionary cache stores dictionary information in order to parse the SQL statement. It also contains user information, integrity constraints defined for tables etc.
Java pool : It is used for caching parsed java programs.
Large pool : is used in shared/MTS server systems for session memory, by parallel execution for message buffers, and by RMAN backup processes for disk I/O buffers.
Stream pool : is used for streams.
What is DB buffer cache? It stores the recently executed database blocks. When other user fires the same query, oracle reads from buffer cache instead of accessing physical files.
What is Redo log buffer? It stores all the changes made in the database. It will be stored in archive log files for recovery purpose.
Please click to know more info about SGA
Program Global Area(PGA) A PGA is allocated by Oracle when a user connects to an Oracle database and a session is created. PGA contains session information and Private SQL area.
Content of PGA :
1. Session information. For instance, Logon info, session related info.
2. Private SQL area. Please remember, this is part of PGA only if the DB is dedicated server. This memory will be part of SGA if the DB is shared server. Private SQL area contains informatoin about the cursor, query execution status info, sql work area(sort area). Please click here to know more about PGA memory.
Oracle keep introducing new features in the memory management side to make DBA's life easy.
Let us talk about new oracle memory management features in each new version since from Oracle9i.
1. Oracle9i Feature
2. Oracle10g Feature
3. Oracle11g Feature