PGA & SGA

PGA stands for Process Global Area which is also known as Program Global Area. It is called global area because is keeps information which is required by all modules of Oracle Code. PGA keeps information specific to the sever process upon which Oracle code acts.

PGA also keeps information about oracle shared resources so that it can free those resources .
Each session contains specific information like bind variables and runtime structures in a private SQL area.

Whenever a session executes a statement, a private SQL area is assigned to that session.
Even if multiple users are issuing the same statement using the same shared SQL area, each session will have its own dedicated private SQL area.

A private SQL area contains data such as bind information and runtime memory structures.
Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area.

Thus, many private SQL areas can be associated with the same shared SQL area. A private SQL area itself is divided into run-time area and persistent area.

Persistent area contains information like bind variable and will be freed once the cursor is closed.
Run-time area is allocated with the first step of the execute request and will be freed when execution is completed.

SGA
Each instance will have its own SGA .The SGA contains data buffer areas, redo log buffers and the shared pool .
Each area is important to the database overall performance.

The Oracle kernel process uses an LRU (Least Recently Used) algorithm to write data back to the disks.
Data is never altered on the disks directly, but is altered in memory first.

The shared SQL pool is used to store the Dictionary Cache as well as information about SQL statements that are being run against the database.

Cursors

         Whenever a SQL statement is executed, Oracle automatically allocates a memory area (known as context area) in Oracle database PGA i.e. Process Global Area. This allocated memory space is the query work area which holds the query related information.This is know as cursors

OPEN stage

•PGA memory allocation for cursor processing (OPEN Cursor)
•Parsing of SELECT statement (Parse SQL)
•Variable binding (Bind SQL)
•SELECT Query execution (Execute Query)
•Move the record pointer to the first record

FETCH stage

The record, to which the record pointer points, is pulled from the result set. The record pointer moves only in the forward direction. The FETCH phase lives until the last record is reached.

CLOSE stage

After the last record of the result set is reached, cursor is closed, and allocated memory is flushed off and released back to SGA. Even if an open cursor is not closed, oracle automatically closes it after the execution of its parent block


Cursor FOR loops

Cursor FOR loops improvise upon the performance and code interactivity by their implicit actions

Parameterized Cursors

Parameterized cursors enables programmer to pass parameter to the cursors

Dynamic Cursor FOR Loops

FOR I IN (SELECT EMPLOYEE_NAME, JOB_ID,SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=10)

FOR UPDATE OF clause is used to lock a set of rows in a session. This concept can be used in explicit cursors to impose exclusive row level lock on all the rows contained by the cursor query result set. These rows will remain locked until the session issues ROLLBACK or COMMIT.

Oracle provides WHERE CURRENT OF clause to update or delete the rows which are locked by the FOR UPDATE OF cursor in the session