Memory Parameter in Oracle10g

Oracle10g new feature on Memory Management :

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_size
2. 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

Memory Parameters in oracle9i

Oracle9i new features on Memory Management :

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 Oracle11g

Oracle is enhancing the memory management in each version starting from oracle9i. Oracle9i introduced pga_aggregate_target to dynamically manage the memory for PGA. Oracle went one step further in 10g and introduced sga_target parameter to dynamically allocate the SGA memory. Oracle introduced memory_target parameter in oracle11g and dynamically allocate both SGA and PGA memory. This is one of exciting features that takes care of SGA as well as PGA in one parameter. We let Oracle to adjust the memory as the workload needs it.

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

Oracle Memory Management

It has been a few months since i added a new post in my blog. I moved to Austin, TX last December 2009 and since that time, i have been busy with my new job, settling down with new place, buying new home etc. I am going to discuss about Oracle Memory components and their function of each component. I am writing this article based on my experience, reading oracle websites, books and other blogs.

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

Dynamic Sampling in Oracle

I started my career with oracle 7.0 and i remember, in oracle7.x/8.x version, optimizer use Rule based optimizer if one or all of the tables does not have any statistics in the join query. In oracle9i R2, dynamic sampling query is introduced and it has ability to sample the table and collect the better statistics dynamically during the hard parse. This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.

Oracle optimizer use the default statistic values when table does not have any statistics and dynamic sampling query feature is disabled. The default statistics values are well documented in Performance Tuning Guide.

How do we set Dynamic sampling parameter? There are couple of ways, we can set the parameter.

1. We can set this in session level or instance level.
2. Dynamic sampling query hint in query level. (Hint)

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter session set OPTIMIZER_DYNAMIC_SAMPLING = 2;

Session altered.

SQL> alter system set OPTIMIZER_DYNAMIC_SAMPLING = 2;

System altered.

SQL> select /*+ dynamic_sampling(emp 0) */ * from scott.emp;

When does dynamic sampling is useful?
1. It is useful when table data is drastically changing and statistic becomes stale very often.
2. Global temporary table is good place to use dynamic sampling feature.

Dynamic sampling Levels? Optimizer Dynamic Sampling has 11 levels between 0 to 10. What are these levels? The answer is pretty straight forward and it is clearly documented in Oracle Performance Tuning Guide. In Oracle9i R2, the default setting for dynamic sampling level is 1. At this setting, the optimizer will tend to use dynamic sampling only in some cases when a table is unanalyzed. This includes the case of global temporary tables without statistics but doesn’t ensure that dynamic sampling will be used against all unanalyzed tables. In Oracle Database 10g Release 1 and above, the default setting is 2. This setting ensures that the optimizer will have a good estimate whenever it optimizes a query that accesses a table with no statistics.

The source of this Article is Asktom. Please read this link and this link has very pretty good information about Dynamic sampling.

Restoring old statistics

I see some DBA's, they won't backup the statistics before collect the new statistics. If we don't have backup, and optimizer behaves differently with new statistics, then we are in trouble. If we have old statistic backup, then we can resolve the performance issue by restoring the old statistics. Oracle highly recommends to take the backup of current statistics before we collect new statistics.

Oracle10g has a new feature which will save the statistics before overwriting the new statistics.

There are couple of ways, we can backup the statistics.

1. We can export the statistics in a table and import back when ever we need. I discussed this approach in different thread. This approach is mostly useful when we transfer the statistics from one server to another server. Please refer this thread Post

2. Since Oracle10g, whenever statistics are modified, old version of statistics are saved automatically before overwriting the new statistics. However automatic saving of old statistics will not take place if you use ANALYZE in 10g for statistics collection.

I am going to discuss how the statistics are saved and how do we restore the old statistics in Approach 2.

Where does oracle store the statistics? Oracle DBA_OPTSTAT_OPERATIONS table contains a log history of statistics collected on the database at various level such as table, schema or database. We can restore older statistics anytime by making use of START_TIME and END_TIME values from DBA_OPTSTAT_OPERATIONS table.

How does Oracle maintain the Statistics History? GET_STATS_HISTORY_AVAILABILITY is a Function in DBMS_STATS package that returns oldest timestamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp. Here is the query to find the statistics history starting timestamp.
SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
06-OCT-09 09.52.45.351000000 PM -05:00

SQL>

Oracle maintain the history statistics based on the retention value. Oracle Purge the statistics automatically if it exceeds the statistics retention setting. By defualt, oracle purge the statistics if it is older then 31 days. We can also alter this retention period by using DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure. PURGE_STATS is procedure in DBMS_STATS package that enables you to manually purge old statistics beyond a time stamp.

SQL> execute dbms_stats.alter_stats_history_retention(20);

PL/SQL procedure successfully completed.

As per the above code, oracle keep 20 days statistics history.

SQL> execute dbms_stats.purge_stats('06-OCT-09 09.52.45.351000000 PM -05:00');

PL/SQL procedure successfully completed.

SQL>

As per the above code, oracle purge the statistics prior to 06-OCT-09.

How do we recover the statistics? Oracle RESTORE_TABLE_STATS procedure restores statistics of a table as of a specified timestamp. It also restores the statistics of associated indexes and columns. Let me take EMP table in scott Schema and restore previous statistics.

Here are the steps to restore the previous timestamp statistics:

Step1: Let us check the past statistics and what date the statistics were collected.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select STATS_UPDATE_TIME from dba_tab_stats_history where table_name='EMP';

STATS_UPDATE_TIME
---------------------------------------------------------------------------
30-OCT-09 06.25.54.809000 PM -04:00
07-NOV-09 07.23.23.504000 AM -05:00
07-NOV-09 07.27.31.709000 AM -05:00
07-NOV-09 07.29.54.324000 AM -05:00

SQL> select last_analyzed from dba_tables where
2 table_name='EMP' and owner='SCOTT';

LAST_ANAL
---------
11-NOV-09

SQL> select sysdate from dual;

SYSDATE
---------
11-NOV-09

SQL>

Step2 : Let us restore the statistics which we collected on 30-OCT-2009.

SQL> execute dbms_stats.restore_table_stats('SCOTT','EMP','30-OCT-09 06.25.54.809000 PM -04:00');

PL/SQL procedure successfully completed.

SQL> select last_analyzed from dba_tables where
2 table_name='EMP' and owner='SCOTT';

LAST_ANAL
---------
30-OCT-09

SQL>

At what circumstances, do we restore the Statistics? We have performance issue in the database and DB was fine yesterday and since yesterday, there is no major change on database. Oracle10g automatic statistic gathering is turned on and last statistics gathering was close to the time that users started complanining about performance. At this circumstances, may be new statistics could be a culprit. We can restore the old statistics and see if we have any performance issue.

Restriction on restoring the statistics:

1. It does not have ability to restore user-defined statistics.

2. Old statistics will not be saved if we use ANALYZE command to gather statistics. Hence, we can recover the statistics.

Here is the input(Help) to write this topic.

Automatic Statistics Gathering in Oracle10g

Oracle10g has one of the automated feature which is called Automatic Statistics gathering. Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly scheduled maintenance job. When the DB is created, job is automatically created and started to collect statistics on database object with missing or stale statistics. The job GATHER_STATS_JOB calls the procedure dbms_stats.gather_database_stats_job_proc to gather statistics during the maintenance window.

What does GATHER_DATABASE_STATS_JOB_PROC do? Unfortunately, the package body is wrapped, so without access to the original source code, the implementation details are obscured. This procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).

This is an internal procedure, but its operates in a very similar fashion to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The primary difference is that the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first. This ensures that the most-needed statistics are gathered before the maintenance window closes.

How do we enable this feature? Automatic statistics gathering is enabled by default when a database is created, or when a database is upgraded from an earlier database release. The following condition should be met to enable this feature.

1. GATHER_STATS_JOB should be enabled.
2. statistics_level should be TYPICAL or ALL.

We can enable the job as below when the job is already disabled for any reason.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> connect sys/password@orcl as sysdba
Connected.
SQL> BEGIN
2 DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL>

What time does automatic statistic collection run? By default this job runs within a maintenance window between 10 P.M to 6 A.M week nights and all day on weekends. But in oracle11g, i believe, the maintenance window is not same as Oracle10g.

Do we need to collect statistics in other then the scheduled window? In few Scenario's, it would be required to gather statistics manually.

1. In some cases, we might need to collect the statistics in specific objects other than this maintenance window. If the table data are changed rapidly and optimizer behaves differently due to data changes, then we need to gather statistics manually. We can not wait till the maintenance window open.

2. If the table is bigger and it is failing in the maintenance window for some reason, then you can lock that table during the maintenance window and unlock the table and gather the statistics manually and lock back again.

3. If you have specific table and you want to gather statistics with different option, then you can lock those tables during the maintenance window and gather statistics manually during day time.

Should we change the parameter values in Automatic statistic collection? Oracle recommends not to change the default values for automatic statistics collection. In case, if you need to gather statistics something differently, then lock the table during the maintenance window and unlock the table and gather statistics manually with specfic parameter and lock the table back again.

We have get_param function to find out the parameter values. Also set_param procedure to change the default values for dbms_stats pacakge parameters.