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

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.

Recursive Calls

Recursive calls is a call(sql statement) that must be completed before the completion of user’s SQL statement. Basically, recursive calls are generated by Oracle internal sql statements to maintain changes to tables for internal processing.

Reasons for Recursive Calls

Recursive calls can be generated due to following reasons:

• Dictionary cache is too small resulting in misses on cache
• Database Trigger Firing
• Performing DDL
• PL/SQL blocks containing sql statements

The recursive calls Oracle metric maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates a SQL statement. These internal SQL statements generate recursive calls.

Oracle MetaLink documents also offer information regarding “OPTIMIZING ORACLE7.1 DATABASE PERFORMANCE” including some great points on the recursive calls metric:

“Recursive Calls : occur because of cache misses and dynamic storage
extension. If the dictionary data is found in cache, a recursive call is not
made and the data is read from cache directly. In general if recursive calls
is greater than 4 per process, the data dictionary cache (described below)
should be optimized and segments should be rebuilt with storage clauses
to have a few large extents. Segments include tables, indexes, rollback
segment, and temporary segments.
Also, Recursive calls should be fewer than user calls (less than one-
tenth). Where there is an imbalance the aim should be to reduce parsing.
review : recursive calls to user calls parse count to user calls. High levels
of recursive sql may also be attributable to significant use of PL/SQL.
For each sql statement in a PL/sql block, on each iteration, there are
recursive calls to do the equivalent of bind and define.”

SGA

Larger Pic Link

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. SGA information is shared among multiple users of oracle database instance, its sometimes called shared global area. Oracle automatically allocates memory to SGA when an instance starts up, and the operating system reclaims the memory when the instance shuts down.

The SGA contains the following data structures:

• Database buffer cache
• Redo log buffer
• Shared pool
• Java pool
• Large pool (optional)
• Streams pool
• Data dictionary cache
• Other miscellaneous information

Dynamic SGA:

Sizing the SGA:

The size of the SGA is determined by several initialization parameters. The parameters that most affect SGA size are:

DB_CACHE_SIZE: The size of the cache of standard blocks. The default is 48 MB on UNIX and 52 MB on NT

LOG_BUFFER: The number of bytes allocated for the Redo Log Buffer

SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and PL/SQL. The default is 16 MB. If 64 bit, then the default size is 64 MB

LARGE_POOL_SIZE: The size of the Large Pool. The default is zero (Unless the init.ora parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE, then the default is automatically calculated.)

JAVA_POOL_SIZE: The size of the Java Pool. The default is 24 MB

Therefore, the SGAs size can not exceed SGA_MAX_SIZE – DB_CACHE_SIZE – LOG_BUFFER – SHARED_POOL_SIZE – LARGE_POOL_SIZE – JAVA_POOL_SIZE.

Components of the SGA

The SGA consists of the following four (five if MTS) parts:

• Fixed Portion
• Variable Portion
• Shared pool
• java pool

Fixed SGA

The fixed SGA is a component of the SGA that varies in size from platform to platform and release to release. It is compiled into the database. The fixed SGA contains a set of variables that point to the other components of the SGA and variables that contain the values of various parameters. The size of the fixed SGA is constant and cannot be changed through any means and it is generally very small. Think of this area as a bootstrap section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.

Variable portion

The variable portion is called variable because its size (measured in bytes) can be changed.The variable portion consists of:

• large pool (optional)
  It provides large memory allocation for backup and restoration operations, session memory for shard servers and Oracle XA interface, I/O server processes(DBWO) or parallel query buffers.
• Java pool The location where session-specified Java code and application variables reside during program execution. 
• Shared pool The shared pool is used for objects that are shared among all users. For example: table definitions, PL/SQL definitions, cursors and so on.

Shared Pool

Shared Pool

The shared pool portion of the SGA contains the Library Cache, the dictionary cache, buffers for parallel execution messages, and control structures.

Library Cache

The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.

Dictionary Cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.

The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). The other area in memory to hold dictionary data is the library cache. All Oracle user processes share these two caches for access to data dictionary information.See dictionary cache efficiency for monitoring dictionary cache.

If the data dictionary cache is too small, requests for information from the data dictionary will cause extra I/O to occur; these I/O-bound data dictionary requests are called recursive calls and should be avoided by sizing the data dictionary cache correctly.

 

Streams Pool

The streams pool holds data and control structures to support the Oracle Streams
feature of Oracle Enterprise Edition. Oracle Streams manages the sharing of data and events in a distributed environment. If the initialization parameter STREAMS_POOL_SIZE is uninitialized or set to zero, the memory used for Streams operations is allocated from the shared pool and may use up to 10 percent of the shared pool.

The Oracle Streams pool stores buffered queue messages in memory, and it provides memory for capture processes and apply processes.