Archive for the ‘Data Dictionary’ Category

Performance Monitoring

List of Common Oracle Metrics

Monitoring Methodologies

 

  • Reactive
  • Proactive

Reactive monitoring is not a recommend approach but at times it becomes inevitable. Oracle 10g provide tools that aid in proactively monitoring database. Server-generated alerts and Automated Database Diagnostic Monitor(AADM) are going to be discussed in another post.

 

Database and Instance Metrics

Performance measurements are commonly referred as databases metrics which is basically the rate of change of cumulative statistic that need to be monitored. Metrics are indicators of the health of various database targets. Excerpt from Oracle Database 10g New Features by Rampant TechPress

“The Metrics are the statistics derived from base statistics. They represent the delta values between the snapshot periods. Metrics are used by internal components (clients) for system health monitoring, problem detection and self-tuning. There are hundreds of different measurement points available to monitor database system.

Each metric is also associated with a metric name. You can query the view v$metricname to find the names of all the metrics.

SQL> select METRIC_NAME, METRIC_UNIT from v$metricname; "

 

Metric                                                  Description                                                                                                  
Event Class Metrics Metrics collected on the wait event class level. e.g.  DB_TIME_WAITING
Event Metrics Metrics collected on various wait events
File Metrics Long Duration Metrics collected at the file level. e.g. AVERAGE_FILE_WRITE_TIME
Service Metrics Metrics collected at the service level. e.g. CPU_TIME_PER_CALL
Session Metrics Short Duration Metrics collected at the session level. e.g. BLOCKED_USERS
System Metrics Short Duration Metrics collected at the system level
Tablespae Metrics Metrics collected at the Tablespace level e.g. TABLESPACE_PCT_FULL

 

Viewing Metrics

The new MMON background process collects database metrics continuously and automatically saves them in the SGA for one hour.It also transfers the memory version of AWR stats to disk on regular basis(in snapshots). Performance metrics are available through

  • Data Dictionary
  • Dynamic Performance Views
  • Optimizer Statistics

Data Dictionary Metrics

Data dictionary metrics provide information about space consumption and object status. Database indexes and stored procedures both need to be VALID to be used.

Unusable indexes always require DBA intervention to restore them to a valid state. Invalid
PL/SQL objects will normally recompile automatically the first time they are called, but
sometimes require DBA intervention in cases where the automatic recompilation fails.

Compiling PL/SQL procedures and Packages

Indexes can become unusable due to normal maintenance operations on tables. Unusable indexes can be rebuild to make them valid.

Rebuilding Indexes

 

Viewing In-Memory Metrics

All system related metrics saved in memory and they can be viewed using dynamic performance views. Most of the cumulative statistics can be viewed through following:

  • V$SYSSTAT
  • V$SESSSTAT
  • V$SYSMETRIC
  • V$SYSMETRIC_HISTORY

Example of few system metrics maintained in V$SYSMETRIC view:

  • Buffer Cache Hit Ratio
  • CPU Usage Per Sec
  • Disk Sort Per Sec
  • Host CPU Utilization (%)
  • Library Cache Hit Ratio
  • SQL Service Response Time
  • Shared Pool Free (%)

Viewing Saved Metrics

After every 60 min, MMON places metric info from SGA to disk through AWR snapshot mechanism. The metric data that AWR collects through MMON is permanently stored in DBA_HIST_* views like

  • DBA_HIST_SERVICE_NAME
  • DBA_HIST_SESSMETRIC_HISTORY

Optimizer Statistics

Optimizer statistics for tables and indexes are stored in the data dictionary. These statistics are not intended to provide real-time data. They provide the optimizer a statistically correct snapshot of data storage and distribution which the optimizer uses to make decisions on how to access data.

Metrics collected include:

  • Size of the table or index in database blocks
  • Number of rows
  • Average row size and chain count (tables only)
  • Height and number of deleted leaf rows (indexes only)

As data is inserted, deleted, and modified these values change. The performance impact of
maintaining real-time data distribution statistics would be prohibitive, so these statistics are updated by periodically gathering statistics on tables and indexes.

Finding time for Specific SCN

 

SELECT CURRENT_SCN FROM V$DATABASE;

To get the current SCN

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL

1.1 ORA_ROWSCN

Is a pseudocolumn of any table that is not fixed or external. It represents
the SCN of the most recent change to a given row, that is, the latest COMMIT operation for the row. For example:

SELECT ora_rowscn, last_name, salary FROM employees
WHERE employee_id = 7788;
ORA_ROWSCN NAME SALARY
———- —- ——
202553 Fudd 3000

1.2 SCN_TO_TIMESTAMP( nSCN NUMBER)

Converts a SCN to TIMESTAMP
It has a precision of +/- 3 seconds

SQL> select scn_to_timestamp(884871) as timestamp from dual;

TIMESTAMP
—————————————————————————
09/03/2007 14:52:02,000000000

1.3 TIMESTAMP_TO_SCN(dTimestamp)

Converts TIMESTAMP to SCN
It has a precision of +/- 3 seconds

SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;

SCN
———-
845396

 

1.4 Table: SMON_SCN_TIME

This table translate time to SCN approximately.

When you use time based flashback you get the data in a period between +- 5 mn.
DBMS_FLASHBACK.ENABLE_AT_TIME and AS OF TIMESTAMP maps to an SCN value.  As the SCN-time is recorded every X minutes. The time you specify is rounded down by up to X minutes from database startup.
This situation could create the ORA-01466 unable to read data – table definition has changed. There is only track of times up to a maximum of 5 days (Database up time).

SELECT TO_CHAR(TIME_DP,’DDMONYYYY HH24:MI’) DATE_TIME,SCN
FROM SMON_SCN_TIME
Date_time               SCN
04MAY2006 16:20 576601
04MAY2006 16:26 576799
04MAY2006 16:30 577003
04MAY2006 16:36 577393
04MAY2006 16:41 577585
04MAY2006 16:46 577790

LINK WIKI ORACLE

Locks and Queries

Query to check blocking and waiting sessions

SELECT holding_session bsession_id, waiting_session wsession_id,
b.username busername, a.username wusername, c.lock_type type,
mode_held, mode_requested, lock_id1, lock_id2
FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE c.waiting_session=a.sid and c.holding_session=b.sid

 

To Find which session is blocking other sessions with some system info

 

select s1.username || ‘@’ || s1.machine   || ‘ ( SID=’ || s1.sid || ‘ )  is blocking ‘
  || s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
  from v$lock l1, v$session s1, v$lock l2, v$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1   and l2.id2 = l2.id2 ;

 

Lock type and the ID1 / ID2 columns

The first place to look is the TYPE column. There are only three types of user locks, TX, TM and UL. UL is a user-defined lock — a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it’s acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.

The TM lock is a DML lock. It’s acquired once for each object that’s being changed. The ID1 column identifies the object being modified.

Selecting locking information using v$lock, v$session, dba_objects. First look up for rows with block > 0. Pick the sid and look for rows having same id1(one row should have sid that has another row with block > 0). Create rowid using sid identified earlier as blocking session. Using rowid look up the row in the table.

select * from v$lock

select object_name from dba_objects where object_id=68046

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=135

select do.object_name,
   row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
    dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,  ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
    from v$session s, dba_objects do
    where sid=135
    and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

    select * from sys.tstlock where rowid = ‘AAAQnOAABAAAQ9yAAA’

For RAC

SELECT substr(DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid,1,12) sess,
       id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
   (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
     ORDER BY id1, request;

Isolation Levels are how Oracle executes SQL statements in regards to read consistency and is directly related to what lockmay be ignored.

  • Read Committed (Default)
  • Serializable Transactions
  • Read-only

Read Committed: Each query executed by a transaction sees only data that was committed before the query (not the transaction) began.

Serializable Transaction: See only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements.

Read-Only: See only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.

Oracle Lock Modes

  • Exclusive Lock Mode
  • Share Lock Mode

Exclusive Lock Mode: Prevents the associates resource from being shared. This lockmode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.

Share Lock Mode: Allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.

Oracle Lock Types

  • DML locks (data locks)
  • DDL locks (dictionary locks)
  • Oracle Internal Locks/Latches
  • Oracle Distributed Locks
  • Oracle Parallell Cache Management Locks

Oracle DML Lock Types

  • Row Level Locks
  • Table Level Locks

        Oracle Row Locks [TX]

  • All DML locks Oracle acquires automatically are row-level locks.
  • No limit to the number of row locks held by a transaction.
  • Oracle does not escalate locks from the row level.
  • Row locking provides the lowest level of locking possible provides the best possible transaction concurrency.
  • Readers of data do not wait for writers of the same data rows.
  • A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back.
  • If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

        Oracle  Table Level Lock [TM]

  • A transaction acquires a table lock for DML statements such as INSERT/UPDATE/DELETE, SELECT with the FOR UPDATE, and LOCK TABLE.  Reasons are to reserve DML access to the table on behalf of a transaction and prevent DDL operations
  • Table locks prevent the an exclusive DDL lock on the same table which prevents DDL operations.  Example, a table cannot be altered or dropped if any uncommitted transaction holds a table lock for it.
  • A table lock can be held in several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X).

Oracle DDL Lock Modes

  • Exclusive DDL Locks
  • Shared DDL Locks
  • Breakable Parse Locks

Oracle Latches

  • Latches are low-level serialization mechanisms to protect shared data structures in the system global area (SGA). Latches protect the oracle lists like list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. The implementation of latches is operating system dependent, particularly in regard to whether and how long a process will wait for a latch.

Oracle Internal Locks

  • Data Dictionary Locks
    • Held on entries in dictionary caches while the entries are being modified or used. They guarantee that statements being parsed do not see inconsistent object definitions.
  • File and Log Management Locks
    • Protect various files like control files, redo log files so that only one process at a time can change it. Datafiles are locked to ensure that multiple instances mount a database in shared mode or that one instance mounts it in exclusive mode.
  • Tablespace and Rollback Segment Locks
    • Protect tablespaces and rollback segments. Example, all instances accessing a database must agree on if s tablespace is online or offline. Rollback segments are locked so that only one instance can write to a segment.

Data Dictionary Tables

DBA_LOCK_INTERNAL
1 row for every lock or latch held or being requested

DBA_LOCKS
All locks or latches held or being requested

DBA_DML_LOCKS
All DML locks held or being requested in DB

DBA_DDL_LOCKS
All DDL locks held or being requested in DB

DBA_BLOCKERS
Non-waiting sessions holding locks being waited on

DBA_WAITERS
All sessions waiting on, but not holding waited for locks

 

Good Link for Locks

ORAFAQ on Locks

Oracle Dynamic Performance Views

Dynamic Performance v$ Views

Real-time performance metrics covering memory usage, wait events, input/output device
throughput, and instance activity are available through dynamic performance views, also known as v$ views.

Throughout its operation, the Oracle server records current database activity in a set of virtual tables called dynamic performance views. These virtual tables exist in memory only when the database is running, to reflect real-time conditions of the database operation. They point to actual sources of information in memory and the control file.

Purpose of Dynamic Performance Views

Dynamic performance views provide a snapshot of real-time data.

The dynamic performance views answer questions such as:

  • Is the object online and available?
  • Is the object open?
  • What locks are being held?
  • Is the session active?

Dynamic performance views record current database activity.

  • Views are continually updated while the database is operational
  • Information is accessed from  Memory  and Control file
  • DBA uses dynamic views to monitor and tune the database
  • Dynamic views are owned by SYS user
  • Dynamic views data is non persistent
  • Dynamic performance metrics should be considered in context of performance trends or against baseline data

Dynamic performance views should not be used to make configuration decisions until the instance hasbeen operational for a while.

Architecture of Dynamic Performance Views

Dynamic Performance views are based on very low level Oracle views X$ views. The X$views are actually not tables rather Oracle internal structures(database representation of C Structures) maintained by Oracle kernel.

On top of X$ views, Oracle has created complete set of views called V_$ views. These V_$ views have synonyms with same names starting with V$. These V$ synonyms are called dynamic performance views and V$ views as well.

The dynamic performance tables are owned by SYS.

Dynamic Performance Views Examples

v$controlfile

Displays the location and status of each controlfile in the database.

v$instance

Instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment.

v$database

This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode

Data Dictionary

Oracle Dynamic Performance Views

Oracle Data Dictionary Tables

Data Dictionary

A repository of information describing the data in the database. This includes the name and structures of all tables, constraints, indices, views, synonyms, sequences triggers, procedures, functions, and packages.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = ‘EMPLOYEES’
and OWNER = ‘SCOTT’

 

DBA_TABLES
select TABLE_NAME,LAST_ANALYZED,ROW_LEN, AVG_ROW_LEN,NUM_ROWS,,STATUS,PCTFREE, PCTUSED,
CACHE, BUFFER_POOL,
from dba_tables
where table_name = ‘EMPLOYEE’

SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE =’FKHALID’

 

Table and Index

SELECT DT.TABLE_NAME, DI.INDEX_NAME,DT.CACHE TBL_CACHE, DT.BUFFER_POOL TBL_BUFFER_POOL,
BLEVEL,DI.LOGGING, DI.LAST_ANALYZED "INDX LAST ANALYZED"
FROM DBA_TABLES DT, DBA_INDEXES DI
WHERE DT.OWNER=’HR_DUP’ AND DT.TABLE_NAME= DI.TABLE_NAME

CONSTRAINTS

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = ‘EMPLOYEES’

SELECT Owner,Constraint_name, Table_name, Column_name 
FROM dba_cons_columns
where  table_name = ‘EMPLOYEES’
and owner = ‘HR_DUP’

 

SESSIONS

SELECT sid,serial#,process,username,status,machine,terminal,
lockwait,machine,terminal,blocking_session,blocking_session_status,state,
program,type,logon_time
FROM v$session

  

 

 

Some Useful Data Dictionary Tables

USER_TABLES
Lists each table that belongs to your Oracle user.

USER_TAB_COMMENTS
Shows comments on the tables and views.

USER_TAB_COLUMNS
Tells you the names, data types, default values, etc. of each column in each table.

USER_COL_COMMENTS
Shows comments on the columns.

USER_CONSTRAINTS
Gives you all constraints (either single- or multi-column), such as primary key, foreign key, not null, check constraints, etc.

USER_CONS_COLUMNS
Maps constraints to columns (since a constraint can act on one or many columns).

USER_INDEXES
Lists indexes defined on columns (either defined explicitly when creating the data model or defined automatically by Oracle, as is the case with indexes on primary keys).

USER_IND_COLUMNS
Maps indexes to columns.

USER_VIEWS
Lists all views, along with the text used to originally create them.

USER_SYNONYMS
Lists the synonyms and original table names.

USER_SEQUENCES
Lists all sequences, including min value, max value, and amount by which to increment.

USER_TRIGGERS
Contains trigger names, criteria for activating each trigger, and the code that is run.

USER_SOURCE
Contains the source code for all PL/SQL objects, including functions, procedures, packages, and package bodies.

Constraints

Several types of Oracle constraints can be applied to Oracle tables to enforce data integrity, including:

  • Oracle "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to the table definition to ensure the validity of the region column.
  • Not Null Constraint: This Oracle constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time.
  • Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key.
  • References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times.  At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.
  • Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.

Important Constraint Views

dba_cons_columns
dba_constraints

 

CREATE TABLE Dept_tab (
    Deptno  NUMBER(3) CONSTRAINT Dept_pkey PRIMARY KEY, 
    Dname   VARCHAR2(15), 
    Loc     VARCHAR2(15))
CREATE TABLE Emp_tab ( 
    Empno    NUMBER(5) CONSTRAINT Emp_pkey PRIMARY KEY, 
    Ename    VARCHAR2(15) NOT NULL, 
    Job      VARCHAR2(10), 
    Mgr      NUMBER(5) CONSTRAINT Mgr_fkey REFERENCES Emp_tab,
    Hiredate DATE, 
    Sal      NUMBER(7,2), 
    Comm     NUMBER(5,2), 
    Deptno   NUMBER(3) NOT NULL 
             CONSTRAINT dept_fkey REFERENCES Dept_tab ON DELETE CASCADE);

alter table  table_name add constraint constraint_name;

Primary and Foreign Key Constraints
ALTER TABLE Dept_tab 
    ADD CONSTRAINT PK_Dept PRIMARY KEY (deptno); 
ALTER TABLE Emp_tab 
    ADD CONSTRAINT FKEY_Dept FOREIGN KEY (Deptno) REFERENCES Dept_tab;
Unique Check and Not Null Constraints
ALTER TABLE Dept_tab CONSTRAINT UQ_Dname UNIQUE (Dname, Loc);
ALTER TABLE Dept_tab CONSTRAINT Loc_check1
CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'));
ALTER TABLE Emp_tab MODIFY (Ename VARCHAR2(15) NOT NULL);
Enabling Disabling Dropping Constraints

ALTER TABLE Dept  ENABLE CONSTRAINT UQ_Dname;

ALTER TABLE Dept_tab  DISABLE CONSTRAINT UQ_Dname;

ALTER TABLE Dept_tab  DROP CONSTRAINT  UQ_Dname;

 

Constraint State

DEFERRABLE Clause

The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

  • Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The checking of a NOT DEFERRABLE constraint can never be deferred to the end of the transaction.

    If you declare a new constraint NOT DEFERRABLE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

  • Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

The following statement creates table games with a NOT DEFERRABLE INITIALLY IMMEDIATE constraint check (by default) on the scores column:

CREATE TABLE games (scores NUMBER CHECK (scores >= 0));

CREATE TABLE games (scores NUMBER, CONSTRAINT unq_num UNIQUE (scores) INITIALLY DEFERRED DEFERRABLE);

VALIDATE | NOVALIDATE

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default.

ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)
ENABLE VALIDATE;

 

RELY Constraints

The ETL process commonly verifies that certain constraints are true. For example, it can validate all of the foreign keys in the data coming into the fact table. This means that you can trust it to provide clean data, instead of implementing constraints in the data warehouse. You create a RELY constraint as follows:

ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id) 
RELY DISABLE NOVALIDATE;

This statement assumes that the primary key is in the RELY state. RELY constraints, even though they are not used for data validation, can:

  • Enable more sophisticated query rewrites for materialized views. See Chapter 18, " Query Rewrite" for further details.

  • Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it.

DYNAMIC SQL FOR CONSTRAINTS

To disable constraints in a schema for all tables

SELECT ‘alter table ‘||owner||’.’||table_name||’ disable constraint ‘||constraint_name||’ ;’

FROM dba_constraints where owner = ‘HR’

To enable constraints, make sure that referential integrity constraints are enabled after primary key constraints.

SELECT ‘alter table ‘||owner||’.’||table_name||’ enable constraint ‘||constraint_name||’ ;’

FROM dba_constraints  where owner = ‘HR’

order by constraint_type

QUERIES FOR CHECKING CONSTRAINTS

  SELECT Constraint_name, Search_condition  
    FROM User_constraints  
    WHERE (Table_name = 'DEPT_TAB' OR Table_name = 'EMP_TAB') AND  
        Constraint_type = 'C';

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE

FROM DBA_CONSTRAINTS

WHERE TABLE_NAME = ‘EMPLOYEES’

SELECT Owner,Constraint_name, Table_name, Column_name 
FROM dba_cons_columns

where  table_name = ‘EMPLOYEES’

and owner = ‘HR_DUP’

PGA

Program Global Area (PGA)

The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated.  The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.

Contents of PGA:

The contents of the PGA memory varies, depending whether the instance is running in a Dedicated Server or Shared Server configuration. Generally the PGA memory includes these components:

Private SQL area:

Contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. 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. The private SQL area of a cursor is divided into two areas:

Persistent area: Contains bind information, and is freed only when the cursor is closed

Run-time area: Created as the first step of an execute request. For INSERT, UPDATE, and DELETE commands, this area is freed after the statement has been executed. For queries, this area is freed only after all rows are fetched or the query is canceled.

The location of private SQL area depends on the type of connection established for the session. In a Dedicated Server environment, the private SQL areas are located in the PGA of their server process. In a Shared Server environment, the private SQL areas are located in the SGA.

The management of private SQL areas is the responsibility of the User Process. The number of Private SQL areas that a User Process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50.

Session Memory:

Consists of memory allocated to hold a session’s variables and other information related to the session. For a Shared Server environment, the session memory is shared and not private.

SQL Work Areas:

Used for memory-intensive operations such as: Sort, Hash-join, Bitmap merge, Bitmap create. The size of a work area can be controlled and tuned.

Beginning with Oracle9i, the size of the work area can be automatically and globally managed. This is enabled by setting the WORKAREA_SIZE_POLICY parameter to AUTO, which is the default, and the PGA_AGGREGATE_TARGET initialization parameter. The PGA_AGGREGATE_TARGET parameter is set by the DBA to specify the target aggregate amount of PGA memory available to the instance. This parameter is only a target and can be dynamically modified at the instance level by the DBA. It will accept a number of bytes, kilobytes, megabytes or gigabytes. When these parameters are set, sizing of work areas becomes automatic and all *_AREA_SIZE parameters are ignored for these sessions.

Prior to Oracle9i, the DBA controlled the maximum size of SQL work areas by setting the following parameters: SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE. Setting these parameters can be difficult because the maximum work area size is ideally selected on the basis of the data input size and the total number of work areas active in the system. These two factors vary a lot from one work area to another and from one time to another. Thus, these parameters are hard to tune under the best circumstances.

 

 

PGA Memory Management for Dedicated Mode

You can automatically and globally manage the size of SQL work areas. The database administrator simply needs to specify the total size dedicated to PGA memory for the Oracle instance by setting the initialization parameter PGA_AGGREGATE_TARGET. The specified number (for example, 2G) is a global target for the Oracle instance, and Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target.

 

There are fixed views and columns that provide PGA memory use statistics. Most of these statistics are enabled when PGA_AGGREGATE_TARGET is set.

  • Statistics on allocation and use of work area memory can be viewed in the following dynamic performance views:

V$SYSSTAT

V$SESSTAT

V$PGASTAT

V$SQL_WORKAREA

V$SQL_WORKAREA_ACTIVE

  • The following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle process:

PGA_USED_MEM

PGA_ALLOCATED_MEM

PGA_MAX_MEM

Note:

The automatic PGA memory management mode applies to work areas allocated by both dedicated and shared Oracle database servers.