Archive for the ‘Oracle Architecture’ Category

Oracle Database Architecture


Database Architecture                   

An Oracle database server consists of an Oracle database and an Oracle instance.

Oracle Database architecture is based on physical and logical structures. 


Oracle Database Physical Structure

Oracle database consists of physical files to store information. Basically there are three types of physical files required for database:

Core Files:

  • Data Files                    Contains all the data of database
  • Control Files              Contains data about database(metadata)
  • Redo Log Files          Contains all changes made to data, allows recovery

Other Files

  • Parameter File         Defines how instance will be configured when it starts up
  • Password File           Allow users to connect remotely to the database
  • Archive Log Files   Contains history of redo log files
  • Trace Files                 Contains messages written by server and background process
  • Alert File                    Special trace file, chronological log of messages and errors              



Oracle Database Logical Structure

Oracle database is divided into logical storage units known as Tablespaces, which can be used to group related logical structures together. Logical structure mainly consists of following structures starting with the bigger unit and going down to the smallest.


Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.


Tablespace consists of one or more segments. A segment is a set of extents allocated for a certain logical structure stored in the same tablespace. The segments can be of one of following types:

  • Data segment  (stores user data within the database)
  • Index Segment (store Indexes)
  • Temporary Segment ( stores rollback segment)
  • Rollback/Undo segment (created when sql statement needs a temporary work area)


An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information in one data file.

Data Blocks

Oracle database data is stored in data blocks(at the lowest logical level).  One data block corresponds to a specific number of bytes of physical database space on disk(default 8K). The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.

Oracle Data blocks are the smallest unit of I/O in the database and they are mapped to OS block on the storage device




Oracle Instance

Oracle instance is comprised of memory structures and background processes that are required to perform different tasks to keep the instance and database running. Instance does not exist until database is started, which involves reading initialization parameter file and configuring instance according to the information within the parameter file.

Memory Structures

Instance has two major memory structures:

Background Processes

Oracle creates a set of background processes for an instance that  manage the memory structures, asynchronously perform I/O to write data to disk, and do general housekeeping.
The most common background processes are the following:

  • System monitor (SMON): Performs crash recovery when the instance starts after a failure
  • Process monitor (PMON): Performs process cleanup when a user process fails
  • Database writer (DBWn): Writes modified blocks from the database buffer cache to the files on disk
  • Checkpoint (CKPT): Signals DBWn at checkpoints and updates all of the data files and control files of the database to indicate the most recent checkpoint
  • Log writer (LGWR): Writes redo log entries to disk
  • Archiver (ARCn): Copies the redo log files to archival storage when the log files are full or a log switch occurs


Oracle Parameter Files

Parameter Files

Oracle must read either an initialization parameter file or a server
parameter file(SPFILE) to start an instance. These files contain list of initialization parameters and a value for each parameter.

In the platform-specific default location, Oracle Database locates your initialization parameter file by examining filenames in the following order:

  • spfile$ORACLE_SID.ora
  • spfile.ora
  • init$ORACLE_SID.ora

Initialization Parameters


Initialization parameters are divided into two groups: basic and advanced. In the majority of cases, it is necessary to set and tune only the basic parameters to get reasonable performance. In rare situations, modification to the advanced parameters may be needed for optimal performance.

Complete List of Initialization Parameters (10g)

Basic Parameters

There are about 29 basic parameters. Only a few of these parameters must be explicitly set because the default values are adequate in the majority of cases.



Advanced Parameters

Other than basic parameters, several parameters are used for database tuning and other advance services. Few examples of parameters


Hidden/Undocumented Parameters

Hidden parameters should only be used as the last resort and preferably when advised by Oracle Support.






Complete List of Hidden Parameters(10g)


Viewing and Changing Parameters Settings

  • SHOW PARAMETERS command from SQL*PLUS can be used to to view parameter values

  • V$PARAMETER, V$PARAMETER2 views is used to display current parameter values
  • V$SPPARAMETER view is used to display contents of server parameter file.


  PFILE can be edited with any text editor but the SPFILE is a binary file and its parameter values can be changed using “ALTER SYSTEM SET” and “ALTER SYSTEM RESET”commands.

Removing a Parameter from SPFILE: 



Parameter Setting to SPFILE / Memory/ Both




The scope parameter can be set to SPFILE, MEMORY or BOTH. Memory is default if PFILE was used at STARTUP and BOTH is default if SPFILE was used at STARTUP.

  • MEMORY:  The parameter value is set for the current instance only.
  • SPFILE: The parameter value is updated in SPFILE and takes effect at next STARTUP.
  • BOTH: The parameter value is set for current instance and persists to the SPFILE

Recommended init parameters for Websphere Portal

Recommended init parameters for IBM Websphere Portal


Enhanced by Zemanta

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’


SELECT substr(DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid,1,12) sess,
       id1, id2, lmode, request, type, inst_id
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

1 row for every lock or latch held or being requested

All locks or latches held or being requested

All DML locks held or being requested in DB

All DDL locks held or being requested in DB

Non-waiting sessions holding locks being waited on

All sessions waiting on, but not holding waited for locks


Good Link for Locks

ORAFAQ on Locks


Open cursors take up space in the shared pool, in the library cache. OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.

Monitoring open cursors

v$open_cursor shows cached cursors, not currently open cursors, by session. If you’re wondering how many cursors a session has open, don’t look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.

To monitor open cursors, query v$sesstat where name=’opened cursors current’. This will give the number of currently opened cursors, by session:

   --Total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and = 'opened cursors current';

To see if you’ve set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS

select max(a.value) as highest_open_cur, p.value as max_open_cur

   from v$sesstat a, v$statname b, v$parameter p

   where a.statistic# = b.statistic#

   and = ‘opened cursors current’

   and ‘open_cursors’

   group by p.value;

Monitoring the session cursor cache

v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.

--session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and = 'session cursor cache count' ;

You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.

select c.user_name, c.sid, sql.sql_textfrom v$open_cursor c, v$sql sqlwhere c.sql_id=sql.sql_id — for 9i and earlier use: c.address=sql.addressand c.sid=&sid

Check Session Cursor Cache tuning for details.


UNDO Tablespace

Undo Segment

An undo segment is used to save the old value (undo data) when a process changes data in a database. It stores the location of the data and the data as it existed before being modified. The header of an undo segment contains a transaction table where information about the current transactions using the undo segment is stored.
A serial transaction uses only one undo segment to store all of its undo data.
Many concurrent transactions can write to one undo segment.

Initialization Parameter
If AUTO, use automatic undo management. The default is MANUAL.

An optional dynamic parameter specifying the name of an undo tablespace. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.

Transaction Rollback
When a transaction modifies a row in a table, the old image of the modified columns (undo data) is saved in the undo segment. If the transaction is rolled back, the Oracle server restores the original values by writing the values in the undo segment back to the row.

Transaction Recovery
If the instance fails while transactions are in progress, the Oracle server needs to undo any uncommitted changes when the database is opened again. This rollback is part of transaction recovery. Recovery is possible only because changes made to the undo segment are also protected by the redo log files.

Read Consistency
While transactions are in progress, other users in the database should not see any uncommitted changes made by these transactions. In addition, a statement should not see any changes that were committed after the statement begins execution. The old values (undo data) in the undo segments are also used to provide the readers a consistent image for a given statement.

Read Consistency
The Oracle server guarantees that a statement sees data from a consistent time, even if that data is modified by other transactions.
When the Oracle server begins executing a SELECT statement, it determines the current system change number (SCN) and ensures that any changes not committed before this SCN are not processed by the statement. Consider the case where a long-running query is executed at a time when several changes are being made. If a row has changes that were not committed at the start of the query, the Oracle server constructs a read-consistent image of the row by retrieving the before image of the changes from the undo segment and applying the changes to a copy of the row in memory.

TYPES of UNDO Segment

• SYSTEM: Used for objects in the SYSTEM
• Non-SYSTEM: Used for objects in other
– Auto Mode: Requires an UNDO tablespace
– Manual Mode:
– Private: Acquired by a single instance
– Public: Acquired by any instance
• Deferred: Used when tablespaces are taken offline
immediate, temporary, or for recovery

Dropping an UNDO Tablespace
• The DROP TABLESPACE command drops an UNDO
• An UNDO tablespace can only be dropped if it is
currently not in use by any instance.
• To drop an active UNDO tablespace:
– Switch to a new UNDO tablespace
– Drop the tablespace after all current transactions
are complete

You can drop tablespace UNDOTBS after all transactions within the tablespace are complete.
To determine whether any active transactions exists use the following query:
SQL> SELECT,b.status
FROM v$rollname a, v$rollstat b
WHERE IN ( SELECT segment_name
FROM dba_segments
WHERE tablespace_name = ‘UNDOTBS’
)  AND a.usn = b.usn;
NAME                        STATUS
————————- —————

Automatic Undo Management:

     Set to TRUE, this parameter suppresses errors while attempting to  execute  manual operations in AUTO mode.
Controls the amount of undo data to retain for consistent read

Determines how long to retain undo data to provide for consistent reads. Retaining undo data longer allows for longer queries and also requires larger data files for the UNDO tablespace.
The UNDO_RETENTION parameter, defined in seconds, can be set in the initialization file or modified dynamically with an ALTER SYSTEM command.


A value of 900 retains undo data for 15 minutes.

Sizing an UNDO Tablespace

Determining a size for the UNDO tablespace requires
three pieces of information
• (UR) UNDO_RETENTION in seconds
• (UPS) Number of undo data blocks generated per second
• (DBS) Overhead varies based on extent and file size (db_block_size)

        UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
            FROM v$parameter
            WHERE name = ‘undo_retention’),
      (SELECT (SUM(undoblks)/SUM(((end_time –
            begin_time)*86400))) AS UPS
            FROM v$undostat),
      (SELECT value AS DBS
            FROM v$parameter
            WHERE name = ‘db_block_size’);

Divide the result by 1048576 to get size in MB.

Data Dictionary Views

• Dynamic Performance Views

SELECT segment_name,owner,tablespace_name,status
FROM dba_rollback_segs;

——————— ———–   ——————-  ——
SYSTEM               SYS          SYSTEM         ONLINE
_SYSSMU1$     PUBLIC     UNDO1          ONLINE

Join the V$ROLLSTAT and V$ROLLNAME views to obtain the statistics of the undo segments currently used by the instance.

SELECT, s.extents, s.rssize,s.hwmsize,
s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;


To check the use of a undo segment by currently active transactions, join the

SELECT s.username, t.xidusn, t.ubafil,  t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;

Automatic segment space management

Automatic Segment-Space Management

• Bitmap segments contain a bitmap that describes
the status of each block in the segment with
respect to its available space.
• The map is contained in a separate set of blocks
referred to as bitmapped blocks (BMBs).
• When inserting a new row, the server searches the
map for a block with sufficient space.
• As the amount of space available in a block
changes, its new state is reflected in the bitmap.

Automatic segment-space management can be
enabled at the tablespace level only, for locally
managed tablespaces.
• After a tablespace is created, the specifications
apply to all segments created in the tablespace.

DATAFILE ‘/u01/oradata/data02.dbf’ SIZE 5M