Please send your Questions & Answers or Feedback to "dvmadhavreddy@javabook.org"

Please send your Questions & Answers or Feedback to "mohan@javabook.org"

Two memory structures of Oracle

The two memory structures of oracle are
                1.SGA (System Global Area / Shared Global Area)
                2.PGA (Program Global Area)
Parsing takes place in the LC(Library Cache). Parsing is nothing but checking for syntactical as well as symantical errors.
Parsing is of 2 types :
                1.Soft Parsing
                2.Hard Parsing

For the first time every SQL statement has to under go hard parsing. Parsed representations (parse trees) will be stored in LC until there is space for the other incoming SQL statements.
If there is no space, oracle uses LRU (Least Recently Used) algorithm to flush off.
During parsing phase, server process looks into DDC(Data Dictionary Cache) to resolve the names of the objects and to check the privileges of the users.
Once parsing is completed, oracle has to determine the execution plan and these execution plans will be sorted in the LC(Library Cache).
“system.dbf” contains Data Dictionary which is nothing but the meta data(entire information about the database).
In order to resolve the names of the objects and to determine the user privileges, server process looks into the Data Dictionary and pulls that information and keeps in the DDC(Data Dictionary Cache).
Oracle uses LRU to flush off the data from various sub components.
Soft parsing means no parsing.
Initially, sorting operations takes place in PGA, if PGA is not sufficient, oracle creates temporary segments in temp.dbf where sorting operations takes place. Once the sorting is completed, oracle drops the temporary segments automatically.
Prior to 9i, on behalf of the server process, for every user process oracle allocates a separate PGA.
From 9i, oracle has introduced the parameter(pga_aggrigate_target) to allocate a consolidated PGA for all the users.
In dedicated server architecture, a server process will be created for every user process (100 user processes means 100 server processes) – from 10g every server process occupies 5MB of memory which may lead to wastage.
Related Posts Plugin for WordPress, Blogger...

Flag Counter