Oracle 11g Architecture
Oracle – PGA+ Instance + DB files
Instance – SGA + BG
Instance —SGA 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.
Database —Physical 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
- 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”
- 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
