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.

Row chaining & Migration

ANALYZE TABLE {BAD_TABLE} LIST CHAINED ROWS INTO {MY_CHAINING_TABLE};

For More Reference

PCTFREE , PCTUSED ,INITRANS & MAXTRANS

       The datafiles are the physical storage space on the server. Storage in the datafiles is allocated by blocks to each extent that is used by an object. The block size is operating system-dependent and is determined when the database is created and cannot be changed. The database block size is set in the database parameter file DB_BLOCK_SIZE. Typically, a database block is either 2K or 4K.

•The PCTFREE parameter specifies the percentage of space in each segment's data block reserved for future record expansion into the block.

If records within a block are rarely updated after they are inserted, you should set PCTFREE lower to allow for full space usage. If records within a block are subject to many updates, you should set PCTFREE higher to allow for more growth.

Once the PCTFREE threshold is reached, no additional rows will be inserted until the PCTUSED threshold is reached due to deletion of data. If PCTFREE is set too low, row chaining and row migration will result because updates to the record will not fit into the block.
After that point, no new rows can be inserted into that block until the free space percentage falls below the PCTUSED threshold. The default for PCTFREE is 10%.

The PCTFREE and PCTUSED parameters tell Oracle when to link and unlink a block from the freelist chain. This is only relevant if you are not using the new Automatic Segment Space Management .

•The PCTUSED parameter defines the minimum percentage of data block used space that is necessary before the block is eligible for row insertion. A segment block is added to the free space list once its used space falls below this threshold. The default for PCTUSED is 40%.

If data is static, you will be able to set the PCTUSED lower and more fully use the space. If large amounts of data are inserted and deleted, you should set the PCTUSED higher to prevent block fragmentation.

You might set these parameters as follows: CREATE table emp (......) PCTFREE 5 PCTUSED 80.

The combined sum of PCTFREE and PCTUSED must be less than 100. Correct choice of these parameters can be used to improve the efficiency of table and index segments.

For example, tables that are insert-only (auditing tables, history tables, etc.) should have a PCTFREE setting of 1.

This simple change to a table's storage definition can reduce its total disk space requirements by 10%.
This means less object extension and less physical disk I/O when reading and writing: 10% more data can be read or written with a single data block read; 10% more data can be held in the database's SGA.

This is a very simple way of increasing your block buffer cache without having to buy any more memory.

INITRANS is the initial number of concurrent transactions allocated in each block header when the block is allocated. The default is 1, which is the minimum. The maximum is 255. The size of each transaction entry is operating system-dependent.

MAXTRANS is the maximum number of concurrent transactions for the blocks within the tablespace. The default is 255, which is the maximum. The minimum setting is 1.

Oracle Managed Datafiles (OMFs)

Oracle introduced Oracle Managed Files (OMF) concept in Oracle 9i. OMFs simplify the administration task of an Oracle Database. Oracle manages these files for the administrators.
Prior to Oracle9i, when you dropped a tablespace you would also have to manually remove the physical datafile associated with that tablespace from the operating system.

If a wrong datafile is removed manually from the Operating System, then the option remaining was to do the recovery of the file from the recent backup, which means either a bigger downtime or the tablespace containing the removed datafile was unavailable for the users.
This manual task of removing the datafile is taken over by Oracle. Hence, with Oracle-Managed Datafiles physical file management is left to the database itself.

Oracle-Managed Datafiles (OMFs) give Oracle the ability to manage database files for you.The database internally uses standard file system interfaces to create and delete files as needed.

OMFs can be used when creating database datafiles, tempfiles, online redo logfiles, and database control files.
Before using OMFs, the database must be configured for OMF use. After the database is configured

If the database creates an Oracle Managed Control File, and the database uses SPFILE, then the control_file parameter is automatically added to the SPFILE with the location of the control file.

If PFILE is used instead of SPFILE, then the control_file parameter needs to be added manually else the instance startup will fail.

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