SGA and PGA overview

Memory Parameter Structure in Oracle 11g:

What’s New?

à Prior to 11g the memory parameters in Oracle are individual per each in pfile.ora OR spfile.ora. But Oracle introduced new features in 11g version with parameters so called memory_target and memory_max-target.

à We can use either memory_target OR memory_max_target parameter in the initialization files.

à This feature helps DBA to allocate chunk of memory to an instance without worrying about the all sub-categories of the pfile.ora OR spfile.ora.

How this mechanism Functions?

à We have few parameters in Oracle called sga_target, sga_max_size and pga_aggregate_target etc. The memory for these parameters is individual till 10g

à We can make it centralization using memory_target or memory_max_target from 11g version.

à Oracle takes up memory equal to Memory_Target from OS RAM and manages its resource within itself. So RAM also acts a crucial role here.

à If we set sga_target, sga_max_size and pga_aggegate_target to ‘0’ and set Memory_target to non zero value, then Oracle auto tune the memory for both SGA and PGA with in the limit specified for Memory_Target.

à The memory sharing from memory_Target is 60% to SGA and 40% to PGA from Memory_Target.

à For an instance, I have given 1GB for Memory_Target and Oracle will distribute 60% (614 M) to SGA components and 40% (410 M) to PGA components.

à If we set Sga_Target and Pga_Aggregate_Target to a non-zero value, then Oracle consider these as minimum values for the particular parameter.

à If Sga_Target is set to non-zero value and Pga_Aggregate_Target not set, then Oracle still auto tune the parameter for Pga_Aggregate_target will be the initialization values of

(Memory_Target-Sga_Target).

à It works vies versa. When ISga_Target is not set and Pga_Aggregate_Target set to non-zero values then memory for Sga_Target will be (Memory_Target-Pga_Aggregate_Target.)

Errors and Reasons:

à Error..The most Irritated and Scary word for a DBA.

à If we get error on Initialization parameters i.e pfile.ora or spfile.ora,

Then entire application works on the particular database will get stops and people all around will hit on us to resolve.

à One fine day I have faced the same situation in office. And all developers stopped working due to that.

à I have faced ORA-04031: unable to allocate bytes of data error which means shared memory parameters are over limited on hits.

à So, here am explaining one instance which I faced on one of the development system in BLR (192.168.89.249)

à My server is Oracle11g XE version with Linux and RAM size is 3GB.

Solutions:

à So, I got that error on the database!! What need to do, it is not a big deal.

à Collect the information first:

SQL> show parameter sga;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                   boolean     FALSE

sga_max_size                    big integer 412 M

sga_target                          big integer 0

SQL>show parameter memory_target;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_target                        big integer 412M

à By observing above details we can see that, less memory allocation is there for memory_target which is not sufficient when huge number of hits are there on database.

à We can simple re-boot the database or slush all the cache, but that is temporary solution.

à So, to fix the permanent solution on it, make sure we have sufficient RAM else database can not start even if we increase the space. The fixing process as follows:

SQL> alter system set memory_target=1G;

SQL>show parameter memory_target scope=pfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_target                        big integer 412M

à Still showing same?!! Re-boot the database with pfile as below:

SQL> startup pfile='/u01//app/oracle/product/11.2.0/xe/dbs/initXE.ora'

ORACLE instance started.

à Now create spfile from pfile to alter the changes of pfile to spfile.

SQL> create spfile from pfile;

File created.

à Now check the parameters:

SQL> show parameter sga;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                   big integer 1G

sga_target                         big integer 0

SQL> show parameter memory_target;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_target                        big integer 1G

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/app/oracle/product/11.2.0

                                                 /xe/dbs/spfileXE.ora

SQL> show parameter pfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/app/oracle/product/11.2.0

                                                 /xe/dbs/spfileXE.ora

à So issue solved now. Developers can start working on database now!!!

That’s it!!
Thanks!