ORACLE INSTANCE: Oracle Instance is consist of SGA(System Global Area) memory structure and background process.an Instance can open and use only one database at time.
List of Physical files in Oracle Database
* Data files
* Redo log files
* Control files
* Parameter file(init.ora)
* password file
* Configuration files(sqlnet.ora,tnsnames.ora,listener.ora )
SQL Query Execution PHASE CYCLE
1*loading into Shared pool (sql code loaded into RAM)
2*Syntax Phase(Check for syntax error)
3*Semantic Phase(check table/column access)
4*Query Transformation(transform complex query into simple)
5*Optimization (Create Execution plan)
6*Create Executable
Memory Structure
*SGA
* PGA
*SGA(System/Shared Global Area):
it is a group of shard memory structure.
It's allocate when instance is startup.
its have several memory structure components.
it is use to store the information that is shared by oracle database and background process.
Mandatory memory structure
->Shard Pool : it is use to store most recently executed sql query and most recently used data definition.the shared pool area is RAM area with in RAM heap that is created start up time.
.it contain two performance related memory structure components
->Library Cache : its size depend on the shard pool size .its store information about the most
recently used sql and pl/sql statement. is manage by LRU(Least Recently Used
Algo). Library cache consist with two structure.
*Shard SQL: its store and share the information about execution plan and parse tree for sql
statement against run database.if second time again run same sql ans parse information is available in the shared sql area then not use resource to perform parse and execution plan.
if the cache memory is not available in LC then LRU Algo. is use to free memory for perform operation.
*Shard PL/SQL: its contain information about pl/sql statement execution plan and parse information . like(Procedure,Function,Trigger etc.)
->Dictionary Cache: it is a collection of most recent used definition in database.its include information about table,index,privilege user and other database objects.
during the parse phage the server process look in the data dictionary to validate statements.
in database two type parsing available
*Hard Parse: if execution plan and parse tree available in shared pool(LC+DC)
* Soft Parse: if server create execution plan and parse tree means not available in Shard pool(LC+DC)
->Database Buffer Cache: Its store copies of data block that have been retrieved from data files.
buffer cache organized two list(MRU,LRU)
->Redo log Buffer
Optional memory structure
-> Large Pool
-> Java Pool
-> Stream Pool
*PGA(Program/Process Global Area):It's allocated when server process start.
List of Physical files in Oracle Database
* Data files
* Redo log files
* Control files
* Parameter file(init.ora)
* password file
* Configuration files(sqlnet.ora,tnsnames.ora,listener.ora )
SQL Query Execution PHASE CYCLE
1*loading into Shared pool (sql code loaded into RAM)
2*Syntax Phase(Check for syntax error)
3*Semantic Phase(check table/column access)
4*Query Transformation(transform complex query into simple)
5*Optimization (Create Execution plan)
6*Create Executable
Memory Structure
*SGA
* PGA
*SGA(System/Shared Global Area):
it is a group of shard memory structure.
It's allocate when instance is startup.
its have several memory structure components.
it is use to store the information that is shared by oracle database and background process.
Mandatory memory structure
->Shard Pool : it is use to store most recently executed sql query and most recently used data definition.the shared pool area is RAM area with in RAM heap that is created start up time.
.it contain two performance related memory structure components
->Library Cache : its size depend on the shard pool size .its store information about the most
recently used sql and pl/sql statement. is manage by LRU(Least Recently Used
Algo). Library cache consist with two structure.
*Shard SQL: its store and share the information about execution plan and parse tree for sql
statement against run database.if second time again run same sql ans parse information is available in the shared sql area then not use resource to perform parse and execution plan.
if the cache memory is not available in LC then LRU Algo. is use to free memory for perform operation.
*Shard PL/SQL: its contain information about pl/sql statement execution plan and parse information . like(Procedure,Function,Trigger etc.)
->Dictionary Cache: it is a collection of most recent used definition in database.its include information about table,index,privilege user and other database objects.
during the parse phage the server process look in the data dictionary to validate statements.
in database two type parsing available
*Hard Parse: if execution plan and parse tree available in shared pool(LC+DC)
* Soft Parse: if server create execution plan and parse tree means not available in Shard pool(LC+DC)
->Database Buffer Cache: Its store copies of data block that have been retrieved from data files.
buffer cache organized two list(MRU,LRU)
->Redo log Buffer
Optional memory structure
-> Large Pool
-> Java Pool
-> Stream Pool
*PGA(Program/Process Global Area):It's allocated when server process start.
