Skip to main content

Oracle Memory Structure - Oracle Architecture

SGA - System Global Area

 SGA is a  group of shared memory area which is dedicated to oracle instance.

 Shared Pool

 Shared pool is dividing into two major areas which are :

  • Library Cache : Area which stores the SQL information such as select statements, cursors and execution plans.
  • Data Dictionary Cache : Which contains cache, user information and privileges, segments and extent information as well.
Buffer Cache


 Buffer cache holds blocks of data from disk that have been recently read to satisfy a select statement or that contains modified data-blocks. 

 Redo Log Buffer

 Redo Log buffer holds the most recent changes to the data blocks in the data-files.  

 Large Pool

 This is an operational area of the SGA it is used for transactions that interact with more than one database.

 Java Pool

 Java Pool is used by the Oracle JVM (Java Virtual Machine) for all Java code & data within user session.

 Stream Pool

 Stream Pool is sized by using the initialization parameter STREAMS_POOL_SIZE. the stream pool holds data & control structures to support Oracle stream feature of Oracle Enterprise Edition. 

 PGA - Program Global Area
PGA is an area of memory allocated and private for one process.

Comments

Post a Comment

Popular posts from this blog

SQL Blocking query

SELECT s.session_id, r.status, r.blocking_session_id 'Blk by' , r.wait_type, wait_resource, r.wait_time / ( 1000.0 ) 'Wait Sec' , r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / ( 1000.0 ) 'Elaps Sec' , Substring (st. TEXT ,(r.statement_start_offset / 2 ) + 1 , (( CASE r.statement_end_offset WHEN - 1 THEN Datalength (st. TEXT ) ELSE r.statement_end_offset END - r.statement_start_offset) / 2 ) + 1 ) AS statement_text, Coalesce ( Quotename ( Db_name (st.dbid)) + N '.' + Quotename (Object_sch...
Post a Comment
Read more

REF Cursor

REF CURSOR WILL BE DYNAMICALLY OPENS OR OPEN BASED ON A LOGIC. DECLARE TYPE C1 IS REF CURSOR ; CURSOR C IS SELECT * FROM DUAL; REF_CURSOR RC; BEGIN IF (TO_CHAR(SYSDATE, 'DD' ) = 30 ) THEN OPEN REF_CURSOR FOR 'SELECT * FROM TABLE1' ; ELSIF ( TO_CHAR(SYSDATE, 'DD' ) = 29 ) THEN OPEN REF_CURSOR FOR SELECT * FROM TABLE2; ELSE OPEN REF_CURSOR FOR SELECT * FROM DUAL; END IF ; OPEN C; END ;
Post a Comment
Read more