13 Replies Latest reply: Jul 15, 2009 3:54 AM by Azar RSS

    automatic memory management in 10g R2

      we are using oracle on windows server 2003 with 32 GB RAM. what can be the setting for SGA etc, in order to get maximum memory(automatic memory management) for oracle functions. Oracle is just installed with its default settings.

        • 1. Re: automatic memory management in 10g R2
          Mohammed Taj
          Oracle is just installed with its default settings.
          Don't do anything let it be same.

          If it is production then observe database performance according to that increase or modify SGA size.

          Best way : AWR Or STATPACK report.

          • 2. Re: automatic memory management in 10g R2

            Do you how heavy the application will be?How will the database be used.start with some value and see the database performance.If required then increase if again.There is no formula to get the right sga value.

            • 3. Re: automatic memory management in 10g R2
              for 32GB upto how much value SGA canbe increased?
              • 4. Re: automatic memory management in 10g R2
                Are you planning to create some more database on that server??

                • 5. Re: automatic memory management in 10g R2
                  Mohammed Taj
                  for 32GB upto how much value SGA canbe increased?
                  1. 32 GB is what ? PHSYICAL RAM Or DATABASE SIZE.

                  1. SGA will not depands on DATABASE SIZE , it is depands on database type (transaction) no of user, session etc blah blah.

                  2. As i told you in last post. go with Oracle Default SGA size and wait for sometime then check database performance and based on performance modify SGA.

                  Normally 80% of PHYSICAL memory set for SGA. (Term and Condition apply :))

                  • 6. Re: automatic memory management in 10g R2
                    There is only one database with real time data streaming of 30-50 mb data in 1hour.
                    • 7. Re: automatic memory management in 10g R2
                      There is no way out as such which I guess is out there to tell anyone what to put the SGA size. The only way to check is to see the performance of the database over a period of time, compare it with the past performances and keep on playing around with the memory as long as a satisfactory performance state is not achieved.

                      What's the current database sga size? Have you seen any kind of performance issues so far with the current setting so that you want to increase the sga size or its just that you have good amount of ram and you want to use it?

                      • 8. Re: automatic memory management in 10g R2
                        user10659725 wrote:
                        we are using oracle on windows server 2003 with 32 GB RAM. what can be the setting for SGA etc, in order to get maximum memory(automatic memory management) for oracle functions. Oracle is just installed with its default settings.
                        What else runs on that server and requires memory? If a dedicated Oracle database server, I would assign 24GB to the SGA and leave 8GB for the o/s and other dedicated processes. (and of course monitor the instance and the advice performance views)

                        Also, with this amount of memory I would want to see basically no swapping. So I would monitor that and when swapping do occur, investigate why.

                        BTW, do not use Windows, but have a dozen Linux servers with 32GB RAM being used as Oracle servers. Conceptually, I would not treat my approach to memory different between the two operating systems. But as Windows is thread based, I would expect to see a slightly lower memory footprint being used on Windows than Linux (as the latter is more multi-process based and Oracle does not use POSIX threads on the Linux kernel).
                        • 9. Re: automatic memory management in 10g R2
                          kiran patil
                          Assign the 30% of the RAM size as a sga value.Generate AWR reports or monitor through the dbconsole at peak level how much memory it consumes and what

                          recommendation do you get through sql tuning advisor

                          • 10. Re: automatic memory management in 10g R2
                            The memory management base on two thing
                            1. how much user connected to the database.
                            2. what type of transaction they are performed if user so many and they continuesly perform transaction then it is recommanded that SGA should be 85% of physical memory and 15% is enough for OS.
                            and then check the performance of database.

                            • 11. Re: automatic memory management in 10g R2
                              Just a quick question, (I dont know much about windows). Is it 32 bit or 64 bit Windows 2003?


                              • 12. Re: automatic memory management in 10g R2

                                have a look there

                                How can I tell if my computer is running a 32-bit or a 64-bit version of Windows?
                                To see if your computer is running 32-bit or 64-bit Windows, do the following:
                                Open System by clicking the Start button Picture of the Start button, clicking Control Panel, clicking System and Maintenance, and then clicking System.
                                Under System, you can view the system type.


                                • 13. Re: automatic memory management in 10g R2
                                  Automatic Shared Memory Management

                                  Frustrated by trying to allocate the precise amount of memory required for different pools? Automatic Shared Memory Management makes it possible to allocate memory where it's needed most, automatically.

                                  Whether you're a new or veteran DBA, you've almost certainly seen an error similar to this one at least once:

                                  ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool"... ...

                                  or this one:
                                  ORA-04031: unable to allocate XXXX bytes of shared memory
                                  ("large pool","unknown object","session heap","frame")

                                  or perhaps this one:
                                  ORA-04031: unable to allocate bytes of shared memory ("shared pool",
                                  "unknown object","joxlod: init h", "JOX: ioc_allocate_pal")

                                  The cause of the first error is obvious: the memory allocated to the shared pool is insufficient for answering the user request. (In some cases the cause may not be the size of the pool itself, but rather the fragmentation that results from excessive parsing due to non-usage of bind variables—a favorite topic of mine; but let's stay focused on the issue at hand right now.) The other errors derive from inadequate space in the large pool and Java pool respectively.

                                  You need to resolve these error conditions without any application-related changes. What are your options? The question is how to divide available memory among all the pools required by the Oracle instance.

                                  How Do You Split the Pie?

                                  The System Global Area (SGA) of an Oracle instance, as you know, comprises several memory areas, including the buffer cache, shared pool, Java pool, large pool, and redo log buffers. These pools occupy fixed amounts of memory in the operating system's memory space; their sizes are specified by the DBA in the initialization parameter file.

                                  The four pools—db block buffer cache, shared pool, Java pool, and large pool—occupy almost all the space inside the SGA. (Relative to the other areas, the redo log buffer does not occupy much space and is inconsequential to our discussion here.) You, as the DBA, must ensure that their respective memory allocations are sufficient.

                                  Suppose you decide that the values of these pools should be 2GB, 1GB, 1GB, and 1GB respectively. You would set the following initialization parameters to mandate the sizes of the pools for the database instance.
                                  db_cache_size = 2g
                                  shared_pool_size = 1g
                                  large_pool_size = 1g
                                  java_pool_size = 1g

                                  Now, take a close look at these parameters. Honestly, are these values accurate?

                                  I'm sure you have your doubts. In real life, no one can specify these pools to an exact science—they depend too heavily on the processing inside the database and the nature of processing changes from time to time.

                                  Here's an example scenario. Say you have a typical, "mostly" OLTP database and have dedicated less memory for the buffer cache than you would have for a purely OLTP one (few of which exist anymore). One day, your users turn loose some very large full table scans for end-of-the-day reporting. Oracle9i Database gives you the ability to change the allocation online, but because the total physical memory available is limited, you decide to pull something away from the large pool and the Java pool:
                                  alter system set db_cache_size = 3g scope=memory;
                                  alter system set large_pool_size = 512m scope=memory;
                                  alter system set java_pool_size = 512m scope=memory;

                                  This solution works fine for a while, but then the nightly RMAN jobs—which use the large pool—begin and the pool immediately falls short. Again, you come to the rescue by supplementing the large pool with some memory from the db cache.

                                  The RMAN jobs complete, but then a batch program that uses Java extensively fires up, and consequently, you start to see Java pool-related errors. So, you reallocate the pools (again) to accommodate the demands on the Java pool and db cache:
                                  alter system set db_cache_size = 2G scope=memory;
                                  alter system set large_pool_size = 512M scope=memory;
                                  alter system set java_pool_size = 1.5G scope=memory;

                                  The next morning, the OLTP jobs come back online and the cycle repeats all over again!

                                  One alternative to this vicious cycle is to set the maximum requirements of each pool permanently. By doing that, however, you may allocate a total SGA more than the available memory—thereby increasing the risk of swapping and paging when the allocation is less than adequate for each pool. The manual reallocation method, although impractical, looks pretty good right now.

                                  Another alternative is to set the values to acceptable minimums. However, when demand goes up and memory is not available, performance will suffer.

                                  Note that in all these examples the total memory allocated to SGA remained the same, while the allocation among the pools changed based on immediate requirements. Wouldn't it be nice if the RDBMS were to automatically sense the demand from users and redistribute memory allocations accordingly?

                                  The Automatic Shared Memory Management feature in Oracle Database 10g does exactly that. You can decide the total size of the SGA and then set a parameter named SGA_TARGET that decides the total size of the SGA. The individual pools within the SGA will be dynamically configured based on the workload. A non-zero value of the parameter SGA_TARGET is all that is needed to enable the automatic memory allocation.

                                  Setting up Automatic Shared Memory Management

                                  Let's see how this works. First, determine the total size of the SGA. You can estimate this value by determining how much memory is allocated right now.
                                  SQL> select sum(value)/1024/1024 from v$sga;


                                  The current total size of the SGA right now is approximately 500MB, which will become the value of SGA_TARGET. Next, issue the statement:
                                  alter system set sga_target = 500M scope=both;

                                  This approach obviates the need to set individual values for the pools; thus, you'll need to make their values zero in the parameter file or remove them completely.
                                  shared_pool_size = 0
                                  large_pool_size = 0
                                  java_pool_size = 0
                                  db_cache_size = 0

                                  Recycle the database to make the values take effect.

                                  This manual process can also be implemented via Enterprise Manager 10g. From the database home page, choose the "Administration" tab and then "Memory Parameters." For manually configured memory parameters, the button marked "Enable" will be displayed, along with the values of all manually configured pools. Click the "Enable" button to turn Automatic Shared Memory Management on. Enterprise Manager does the rest.

                                  After the automatic memory allocations are configured, you can check their sizes with the following:
                                  SQL> select current_size from v$buffer_pool;


                                  SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

                                  POOL MBYTES
                                  ------------ ----------
                                  java pool 4
                                  large pool 4
                                  shared pool 148

                                  As you can see, all the pools were automatically configured from the total target size of 500MB. (See Figure 1.) The buffer cache size is 340MB, Java pool is 4MB, large pool is 4MB, and shared pool is 148MB. Together they total (340+4+4+148=) 496MB, approximately the same size as the target SGA of 500MB.

                                  Figure 1: Initial allocation pools

                                  Now suppose the host memory available to Oracle is reduced from 500MB to 300MB, meaning we have to reduce the size of the total SGA. We can reflect that change by reducing the target SGA size.

                                  alter system set sga_target = 300M scope=both;

                                  Checking the pools now, we can see that:
                                  SQL> select current_size from v$buffer_pool;


                                  SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

                                  POOL MBYTES
                                  ------------ ----------
                                  java pool 4
                                  large pool 4
                                  shared pool 44

                                  The total size occupied is 240+4+4+44 = 296MB, close to the target of 300MB. Notice how the pools were automatically reallocated when the SGA_TARGET was changed, as shown in Figure 2.

                                  Figure 2: Reallocation of pools after reducing SGA size to 300MB

                                  The size of the pools is dynamic. Based on the workload, the pools will expand to accommodate the increase in demand or shrink to accommodate the expansion in another pool. This expansion or contraction occurs automatically without the DBA's intervention, unlike the example in the opening of this article. Returning to that scenario for a moment, assume that after the initial allocation the RMAN job starts, indicating the need for a larger large pool; the large pool will expand from 4MB to 40MB to accommodate the demand. This additional 36MB will be carved out of the db buffers and the db block buffers will shrink, as shown in Figure 3.

                                  Figure 3: Reallocated pools after demand for large pool increases

                                  The changed sizes of the pools are based on the workload on the system, so the pools needn't be sized for the worst-case scenario—they will automatically adjust to the growth in demand. Furthermore, the total size of the SGA is always within the maximum value specified by SGA_TARGET, so there is no risk of blowing the memory requirement out of proportion (which will lead to paging and swapping). You can dynamically increase the SGA_TARGET to the absolute maximum specified by adjusting the parameter SGA_MAX_SIZE.

                                  Which Pools are Not Affected?

                                  Some pools in SGA are not subject to dynamic resizing, and must be specified explicitly. Notable among them are the buffer pools for nonstandard block sizes and the non-default ones for KEEP or RECYCLE. If your database has a block size of 8K, and you want to configure 2K, 4K, 16K, and 32K block-size pools, you must set them manually. Their sizes will remain constant; they will not shrink or expand based on load. You should consider this factor when using multiple-size buffer, KEEP, and RECYCLE pools. In addition, log buffer is not subject to the memory adjustment—the value set in the parameter log_buffer is constant, regardless of the workload. ( In 10g, a new type of pool can also be defined in the SGA: Streams pool, set with parameter streams_pool_size. This pool is also not subject to automatic memory tuning.)

                                  This gives rise to an interesting question. What if you need a non-default block size pool yet want to manage the other pools automatically?

                                  If you specify any of these non-auto-tunable parameters (such as db_2k_cache_size), their total size is subtracted from the SGA_TARGET value to calculate the automatically tuned parameter values so that the total size of the SGA remains constant . For instance, imagine that the values look like this:
                                  sga_target = 500M
                                  db_2k_cache_size = 50M

                                  and the rest of the pool parameters are unset. The 2KB buffer pool of 50MB leaves 450MB for the auto-tuned pools such as the default block size buffer pool (db_cache_size), shared pool, Java pool, and large pool. When the non-tunable parameter such as the 2KB block size pool is dynamically adjusted in such a way that the tunable portion's size is affected, the tunable portion is readjusted. For example, raising the value of db_2k_cache_size to 100MB from 50MB leaves only 400MB for the tunable parameters. So the tunable pools such as shared, large, Java, and default buffer pools shrink automatically to reduce their total size to 400MB from 450MB, as shown in Figure 4.

                                  Figure 4: Effect of configuring non-automatic buffer parameters

                                  But what if you have sufficient memory available or the risks described above may not be that pronounced? If so, you can turn off automatic resizing by not specifying the parameter SGA_TARGET in the parameter file, by setting it to zero in the file, or by changing it to zero dynamically with ALTER SYSTEM. When SGA_TARGET is set to zero, the current values of the pools are automatically set to their parameter.
                                  Using Enterprise Manager

                                  You can also use Enterprise Manager 10g to manipulate these parameters. From the database home page, click the hyperlink "Memory Parameters," which will show you a screen similar to the one in Figure 5.

                                  Figure 5: Adjusting Automatic Shared Memory Management in Enterprise Manager

                                  Note the items circled in red: The database is running in Automatic Shared Memory Management mode and the total size is 564MB, the same value specified in the parameter SGA_TARGET. You can modify it here and click on the Apply button to accept the values; the tunable parameters will automatically adjust.

                                  Specifying a Minimum for Each Pool

                                  Suppose you have set SGA_TARGET to 600MB and the various pools have been allocated automatically:

                                  Pool Size (MB)
                                  Buffer 404
                                  Java 4
                                  Large 4
                                  Shared 148

                                  Looking at the above you might conclude that the Java and large pools are a bit inadequate at 4MB; this value will definitely need to be increased at runtime. Therefore, you may want to make sure the pools at least start with higher values—say, 8MB and 16MB respectively. You can do that by explicitly specifying the value of these pools in the parameter file or dynamically using ALTER SYSTEM as shown below.
                                  alter system set large_pool_size = 16M;
                                  alter system set java_pool_size = 8M;

                                  Checking the pools now, you can see:
                                  SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

                                  POOL MBYTES
                                  ------------ ----------
                                  java pool 8
                                  large pool 16
                                  shared pool 148

                                  SQL> select current_size from v$buffer_pool;


                                  The reallocation of the pools is shown below:

                                  Pool Size (MB)
                                  Buffer 388
                                  Java 8
                                  Large 16
                                  Shared 148

                                  Note how the Java and large pools have been reconfigured to 8MB and 16MB respectively, and that to keep the total SGA under 600MB, the buffer pool has reduced to 388MB from 404MB. Of course, these pools are still governed by Automatic Shared Memory Management—their sizes will shrink or expand based on demand. The values you have specified explicitly put a lower limit on the pool size; they will never sink below this limit.


                                  The memory requirements of various pools in Oracle SGA are not static—rather, they vary based on the demand on the system. Automatic Shared Memory Management in Oracle Database 10g allows DBAs to manage system memory more efficiently by dynamically reallocating resources to where they are needed most while enforcing a specified maximum to prevent paging and swapping. More efficient memory management also leads to fewer memory requirements, which can make leaner hardware more viable.