Oracle

Oracle Architecture-03

Advertisements

Oracle 11g Architecture

Oracle – PGA+ Instance + DB files

Instance – SGA + BG

InstanceSGA and BG

 

SGA — Shared Pool, Database buffer cache, Redo log buffer cache, large pool, Java pool & Streams pool.

Shared Pool — Most recently executed statements and definitions.

Data Dictionary cache & Library cache

Data Dictionary — most recently used definitions, including table, index & privilege.

Library cache — most recently used SQL & PL/SQL statements

Shared SQL area & Shared PL/SQL area

Database buffer pool — It stores a copy of data block.

Default, keep & Recycle

Redo log buffer pool — it stores all the changes made to the data blocks.

Large pool — it only used in the shared server environment and for some of RMAN features.

Java pool — it used for Java programs.

BG

DBWR — It writes a dirty buffers to data file.

LGWR — It writes a redo buffer entries to redo log file.

PMON — It cleanup failed user process, rollback transactions, releasing lock & restart death dispatchers.

SMON — It’s for instance recovery, Coalesce free space & Deallocate temporary segments.

CKPT — It will call the DBWR to write the dirty buffers to data file.

 

PGA – Program or Process Global Area

PGA is memory storage for each user process, which has link to server process.

Sort Area, Session Sate, Cursor state & Stack place

 

Process:

User process — It starts when a user make a connection.

Server process — It start when a user process started.

Background process — It start when an instance starts.

 

DatabasePhysical structure and Logical structure

Physical structure — Datafile, Redo log file, Control file, Archive file, Password file & Parameter file

Logical structure — Table space, segment, extent & blocks

 

SGA – Shared Global Area

Shared Pool, Database buffer cache, Redo log buffer cache – Mandadry

Large pool, Java pool & Streams pool – Optional

 

SGA is a group of shared memory areas that are dedicated to an Oracle instance. It allocated when an Oracle Instance starts up. It mostly will be half of RAM size.

Calculation:

If the SGA > 128 MB it will divide the components 16M , 16M…

If it’s less than 128 MB then 4M, 4M

Query to get the SGA info:

Sql> show parameter sga_max_size;

Sql> show sga;

Sql> show parameter memory_traget;

From 9i onwards, we can change the SGA online. Below 9i the DB needs take down

Shared Pool

–It will use most frequently executed SQL & PL SQL definitions & statements.

–For query processing.

Types: 1. Data dictionary cache 2. Library cache

  1. Data dictionary cache – It is a read-only set of tables that provides information about the database. The definitions of all schema objects are stored. Queries are validated here.

It will store most frequently executed SQL & PL SQL definitions.  So that, it’s easy to update in memory. During the parsing phase in the library cache , the server process scans this memory structure to resolve the database object names and validate access, which will improve the performance.

 

Example: Select name from employee;

It includes information about: — database files, tables, indexes, privileges, users, etc.

In case, if the column name was incorrect, it will throw an error “invalid column identifier”

 

  1. Library cache – It stores the executable form of PL and SQL codes. Oracle will reuses the code.

It will store most frequently executed SQL & PL SQL statements – (PL/SQL procedures and packages, and control structures), which eliminates the need for parsing and compiling the statements again if used subsequently and hence improves performance.

And it will reuse the same code, if the definition is same. It is using an LRU algorithm.

Parsing — Syntax and semantic check

Query to get the Shared pool info:

Sql> show parameter shared_pool_size;

The shared pool will be divided by DDC and LC.

 

Database Buffer Cache Same as SQL server

It holds copies of data blocks read from data files. The query results and records will stored here. It has three buffers.

Three stages of DB buffer:

–Pinned buffer (most frequently used data)

–Dirty buffer (buffer that has been changed in memory but not yet written to disk.)

–Free buffer (free space)

It has three sub components  — Default, Keep &  Recycle.

By Default, The default only available, if needed, we can configure the keep and recycle.

 

Query to get the database buffercache info:

Sql> show parameter db_cache_size;

Sql> show parameter db_keep_size;

Sql> show parameter db_recyle_size;

 

LRU will monitor the shared pool and DB buffer cache and will delete the old/ rare case query/results.

 

Redo Log Buffer Cache

All new entries will write here. Primary copy will be stored in DB buffer and secondary copy will store here. It’s for recovery purpose.

Any changes to the cache size needs a reboot. No dynamic allocation.

Size can be only given in bytes.

Default size is 512k (or) 128k * no.of CPU. It will take the bigger one by default.

Background process LGWR writes the redo log buffer to the active redo log file (or group of files) on disk.

Query to get the Redo log buffer cache info:

Sql> show parameter log_buffer;

 

Database server mode

Two modes 1. Dedicated (OLTP) 2. Shared (OLAP)

By default the dedicated will be configured.

In dedicated mode the  user information will store in here. In shared mode the user information will store in Large Pool.

Large Pool

It’s an optional, when you configure a shared server , this will be required.

Query to get the large pool info:

Sql> show parameter large_pool_size;

 

Java Pool

It’s an optional, From 9i onwards the database designed in java. The JAVA Pool holds the JAVA execution code.

It uses Oracle Database backup and restore operations.

{ Oracle history oracle 7,8,8i,9i,10g,11g & 12c}

I –internet, G – Grid and C –  Cloud

Query to get the Java pool info:

Sql> show parameter java_pool_size;

 

Streams Pool

It’s an optional, From 10g Oracle replication has changed to Oracle Streams. And it is used to store the replication changes to improve the performance.

It uses Oracle Database backup and restore operations.

Query to get the Streams pool info:

Sql> show parameter streams_pool_size;

 

Background process

DBWR, LGWR, SMON, PMON, CKPT – Mandatry process

AVC, MMON, MMAN, CTWR, RCVCAT, ASMB, Joo, RBAL – Optional process

DBWR – Database Writer

It will write a dirty data/ uncommitted into a data file. Oracle Database allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj).

Parameter – DB_WRITER_PROCESSES.

Default 1 or CPU_COUNT / 8, whichever is greater. We can also change.

When it will run:

Dirty buffers reach threshold

Checkpoint occurs

There are no free buffers

A timeout occurs

RAC ping request is made

When any tablespace is taken offline, read only, drop or truncated & when begin the backup

Sql> Alter tablespace <ts. name> begin backup

…End backup, online,offline,readyonly & readwrite

Sql> Truncate/drop table <t.name>

 

LWR – Database Writer

It will write a log entries into a redo log file.

When it will run:

At commit

Every 3 seconds

When 1/3rd full

Before DBWn writes.

The Redo Log Buffer contains 1MB worth of redo information

 

SMON – System Monitoring

It will recover the instance from corruption and failure. It’s an automatic recovery.

Mostly instance fails from Power failure & Shutdown abort. It will recover the data by “Rolling forward” and “Rolling backward”.

Coalesce the free space – Smon will separate the used and free space in the table space.

Example: In some cases, when segments are dropped, their extents are deallocated and marked as free, but any adjacent free extents are not immediately recombined into larger free extents. The result is fragmentation that makes allocation of larger extents more difficult.

SMON also cleans up temporary segments

 

PMON – Process Monitoring

It will recover the user process failure.  PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using.

Restart the death dispatches.

Release the dead lock.

Release the other resources. Like, it will release the hash value ask the new query to use it. In case, if the  old query needs back. It will create a new one.

 

Checkpoint

It’s a process and will run in redo log cache and data buffer cache. Checkpoint will create an SCN. It will ask DBWn write dirty blocks to disk.

It will create an SCN number same as all places. Data file, Control file and Redo log file.

The SCN (or time based) will be used in point in time recovery.

When it will run:

Shutdown immediate, Transactional & shutdown

A Log switch

Sql> Alter system switch;

Sql> Alter system archive log current;

Sql> Alter system checkponit;

Parameter fast_start_mttr_target=<No of Seconds>

 

PGA – Program global area

It will reside in RAM. It stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.

The user process will travels via PGA.

Recommended PGA size:

9i – 16% of SGA size

10g & 11g – 20 % of SGA size

How the user process travels

User process — Server process — PGA — SGA

Private –  User information are stored here

Sort  area size –  The sorting will be done here, if there is no enough space, then oracle will use temporary tablespace.

Sorting type: Oracle will categories the sorting internally.

Optimal pass sorting – Sorting will done within PGA

One pass sorting – Maximum sorting in PGA & minimum temporary tablespace

Multipass sorting – Maximum sorting in temporary tablespace & minimum in PGA

Ex: If I have a 100MB workload 70MB will be done in temp and 30MB will be done in PGA.

Merge Area

If the sorting happens in both places PGA and temporary tablespace. It will merge here.

PGA Parameters:

Workarea_size_policy = auto / manual

Pga_aggregate_traget = <size>

Sort_area_size = <size>

Note: If the Workarea_size_policy is auto then, Pga_aggregate_traget is PGA size. If manual then it’s Sort_area_size size.

Query to  get PGA

select * from v$pgastat;

select * from v$pga_target_advice order by pga_target_for_estimate;

select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process; — Max usage  by process

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: