Oracle Architecture

Memory Components

Metalink note; ORA-4031 Common Analysis/Diagnostic Scripts [Video] (Doc ID 430473.1)

SGA – v$sgainfo, v$sgastat

SELECT name, bytes/1024/1024 “SIZE In MB” FROM v$sgastat where name in (‘buffer_cache’,’fixed_sga’,’log_buffer’,’free memory’,’library cache’,’row cache’,’sql area’)

select name, bytes/1024/1024,resizeable from v$sgainfo

SGA  –  SGA_MAX_SIZE/ SGA_TARGETShared Pool = Shared pool :-  resizable areaSHARED_POOL_SIZE= library cache + data dictionary cachestake help of v$shared_pool_advice LIBRARY CACHE -measured by v$librarycache view, hit/miss ratioselect sum (sharable_mem)/1024/1024 from v$db_object_cache; select sum (sharable_mem)/1024/1024 from v$sqlarea; DATA DIC CACHE – Hit ratio can be measured using v$rowcacheThis area is resizeable   DB Buffer Cache (DB_CACHE_SIZE)Biggest memory component of SGA , resizablev$db_cache_adviceBuffer cache can have Tables Indexes Clusters Large Object (LOB) segments LOB indexes Rollback segments Temporary segments Redo buffer size? – Few MBs, non resizable JAVA_POOL_SIZE – Few MBs, resizableV$JAVA_POOL_ADVICE LARGE_POOL_SIZE – Few MBs, resizableRMAN uses  large pool id  BACKUP_TAPE_IO_SLAVES=true, then SGA and if BACKUP_TAPE_IO_SLAVES=false, then PGA STREAMS_POOL_SIZE – Few MBs, resizableV$STREAMS_POOL_ADVICE

PGA

UGA

Mandatory Processes and their functions

PMON – The process monitor (PMON) performs process recovery when a user process fails.

* Rolling back the user’s current transaction
* Releasing all currently held table or row locks (releasing locks)
* Freeing other resources currently reserved by the user
* PMON also registers information about the instance and dispatcher processes with the network listener.
* Restarts dead dispatchers – PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally).

SMON – System Monitor is a mandatory process which starts automatically with Oracle Instance.

Functions:
#1 When an Oracle instance fails/crashes, SMON performs instance recovery at startup using the online redo log files.
#2 It is responsible for cleaning up temporary segments that are no longer in use.
#3 It does coalescing contiguous free extents within dictionary managed tablespaces.

If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or file is brought back online.
SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it.

With Real Application Clusters, the SMON process of one instance can perform instance recovery for a failed CPU or instance.

When shutdown abort happens, oracle server gets crashed. To recover from the crash,
SMON will check the online Redo, data files and last SCN no. in the Redo log files and SCN number in the undo tablespace are same.
If yes, it performs recovery from redo log files else it rollback the values from undo tablespace.

CKPT

A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery.

A checkpoint is initiated

when the redo log file is filled and a log switch occurs,
when the instance is shut down with NORMAL, TRANSACTIONAL, or IMMEDIATE,
when a tablespace status is changed to read- only or put into BACKUP mode,
when a tablespace or datafile is taken offline,

 At a checkpoint, the following information is written:

Checkpoint number into the data file headers
Checkpoint number, log sequence number, archived log names, and system change numbers into the control file.

Checkpoints ensure that data blocks in memory that change frequently are written to data files regularly. Because of the least recently used algorithm of DBWn, a data block that changes frequently might never qualify as the least recently used block and thus might never be written to disk if checkpoints did not occur.

Every 3 seconds CKPT calculates the checkpoint target RBA based on:

The most current RBA
log_checkpoint_timeout
log_checkpoint_interval
fast_start_mttr_target
fast_start_io_target

Because all database changes up to the checkpoint have been recorded in the data files, redo log entries before the checkpoint no longer need to be applied to the data files if instance recovery is required. Therefore, checkpoints are useful because they can expedite instance recovery.

 LGWR process writes

When a transaction commits
When the redo log buffer cache is one-third full
When there is more than 1MB of changed records in the redo log buffer cache
Before DBWn writes modified blocks in the database buffer cache to the data files
Every 3 seconds.

DBWn

Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files.

When a buffer in the database buffer cache is changed, it is marked as a dirty buffer.
A dirty buffer is a buffer that has not been recently used according to the least recently used.
The DBWn process writes dirty buffers to disk so that user processes are able to find free buffers that can be used to write new blocks into the cache.

The number of dirty buffers reaches a threshold value
When no buffer found in buffer cache.
Placing a normal or temporary tablespace offline.
Placing a tablespace in read only mode.
Dropping or Truncating a table.
Putting tablespace in backup mode (ALTER TABLESPACE tablespace name BEGIN BACKUP)

Timeout occurs.
A ping request in Real Application Clusters environment.
Incremental or normal checkpoint
A process scans a specified number of blocks when scanning for free buffers and cannot fine any.

ARC

Basic Concepts of Oracle

Q1 How client/server connection establish?

Ans: Listener makes it possible.

Oracle listener runs only on the server and listens for incoming client connections, a utility called lsnrctl is used to manage the listener process. The listeners role is

• The database registers information about the services, instances and service handlers with the listener
• The client makes the initial connection with the listener
• The listener receives and verifies the connection and forwards it to the service handler, once the listener hands off the request, the listener is out of the picture.

The listener.ora file contains the listener configuration details, with oracle 10g the listener can automatically register itself with the database, The PMON process updates the listener.ora file with any new databases.

Dynamically registers a default listener	1. Register the default listener to the instance   # alter system set instance_name = P01; # alter system set service_name = P01; Note: port 1521 is assumed, you do not need to update the listener.ora file
Dynamically registers a non-default listener	1. Create the listener (use Net Manager) 2. Create the Net Service name (Use Net Configuration Assistant) 3. Register the service with the instance   # alter system set local_listener = ‘P01_1522’;# alter system register;Note: you need to update the listener.ora and tnsnames.ora files

Reference Link

http://www.adp-gmbh.ch/ora/network/connection.html

Q2 How Select query process?