about sga and pga in oracle

Shared pool in sga in oracle memory

It is a Shared portion of the Sga it contains the Library Cache and Dictionary cache

Library Cache: The library cache contains the current SQL execution plan information. It also holds stored procedures and trigger code.

Dictionary cache: The dictionary cache stores environmental information, which includes referential integrity (like primary key etc), table definitions, indexing information, and other metadata stored within Oracle's internal tables.

To change the shared pool in Sga in oracle

1     open the file using notepad SPFILEXE.ORA in this location C:\oraclexe\app\oracle\product\10.2.0\server\dbs

2     find the parameter of shared_pool_size=88080384(the value is in bytes)

      give the required memory for shared pool

Buffer Cache in sga in oracle memory

It is also called DB Buffer Cache.

The DB buffer cache is read the data from datafiles.

Datafiles: Datafiles store the information contained in the db(i.e our tables,views…. data)

To change the Buffer Cache in Sga in oracle

1     open the file using notepad SPFILEXE.ORA in this location C:\oraclexe\app\oracle\product\10.2.0\server\dbs

       find the parameter of db_cache_size=176160678 (the value is in bytes)

3     give the required memory for shared pool 

 We can also set Sga and Pga

1open the file using notepad SPFILEXE.ORA in this location C:\oraclexe\app\oracle\product\10.2.0\server\dbs

2sga_target=314572800 (to set sga memory using this parameter)

    pga_aggregate_target=90M(to set pga memory using this parameter)