Chapter 1 : ORACLE ARCHITECTURAL COMPONENTS

Chapter 1 : ORACLE ARCHITECTURAL COMPONENTS

CHAPTER # 1

ORACLE ARCHITECTURAL COMPONENTS

The database administrator is responsible for maintaining the oracle server

So that oracle server can process user requests.

CORE TASK OF DATABASE ADMINISTRATOR

1- To plan and create database

2- To manage database availability

3- To manage physical and logical structure

4- To manage storage based on design

5- To manage security

6- Network administration

7- Backup and recovery

8- Database tuning

ORACLE SERVER:

The oracle server is a database management system that provide an open, comprehensive, integrated approach to information management.

There are several files, processes, and memory structures in a oracle server.

However not all of them are used when processing a SQL statement.

Some are used to improve the performance of the database, to ensure database can recovered in case of media failure or hardware failure.

And some are used to perform other task necessary to maintain the database.

The oracle server consist of:   ORACLE INSTANCE

                                                ORACLE DATABASE

ORACLE INSTANCE:

An instance is a mean to access oracle database.

Always open one and only one database

Consist of:  MEMORY STRUCTURE

                   BACKGROUND PROCESSESS (that are used to manage database)

Every time an instance is started, a system global area (SGA) is allocated and background process are started.

ORACLE DATABASE:

An oracle database is a collection of data that is treated as unit.

The general purpose of database is to store and retrieved related information.

An oracle database has physical and logical structure.

Physical structure of database consist of 3 types of files:  

  DATA FILES      (containing actual data in the database)

  CONTROL FILES   (containing inforamtion necessary to maintain and verify DB integrity)

  REDO LOG FILES  (containing a records changes made to the database for recovery of data

                   in case of failure)

Also known as operating system files, database files.

OTHER KEY FILES STRUCTURE

The oracle server also use other files that are not part of the database

1- The parameter file

2- The password file

3- Archived redo log files

MEMORY STRUCTURE:

Oracle memory structure consist of two memory areas known as:

1- SYSTEM GLOBAL AREA (SGA) : allocated at instance start up and it is the fundamental component of oracle instance.

2- PROGRAM GLOBAL AREA(PGA) : allocated when the server process started.

SYSTEM GLOBAL AREA (SGA) :

It is used to store database information that is shared by database process.

It contains data and control information for the oracle server and is allocated in the virtual memory of the computer where oracle resides.

The SGA consist of several memory structures:

- Shared Pool

- Database buffer cache

- Redo log buffer

There are 2 additional memory structures

- Large pool

- Java pool

DYNAMIC SGA : this feature allows the size of

Database buffer cache

Shared pool

Large pool

To be changed without shutdown the instance.

SHOW PARAMETER SGA_MAX_SIZE (use this command to see the max size of SGA)

UNIT OF ALLOCATION

A granule is a unit of contiguous virtual memory allocation.

And its calculation based on the SGA_MAX_SIZE parameter.

- 4MB is estimated if estimated SGA size is Less than 128 MB

- 16 MB otherwise.

SHARED POOL :

The shared pool environment contains both fixed and variable structure.

The fixed structure remain same

Where as the variable structure grow and shrink based on user and program requirement.

The actual size of both fixed and variable structure is based on initialization parameter file.

USED TO STORE:

Most recently executed SQL statement

Most recently used data definition

It is also divided into 2 parts

 - Library Cache

 - Data Dictionary Cache

To watch shared pool size

                          SHOW PARAMETER SHARED_POOL_SIZE

TO resize the shared pool size

                          ALTER SYSTEM SET SHARED_POOL_SIZE = 64MB scope = spfile/both

LIBRARY CACHE :

- Store Information about the most recently used SQL and PL/SQL statement.

- Is managed by least recently used (LRU) algorithm

- Consist of two structures     1- Shared SQL area

                                               2- Shared PL/SQL area

- Size determined by shared pool sizing

- If the sized of shared pool too small statements are continuously reloaded in LRU which effect  

  Performance

- Enable the sharing of commonly used statements.

DATA DICTIONARY CACHE:

A collection of most recently used definitions in the database.

- Include information about database files, tables, indexes, columns, users, privileges and other  

  database objects.

- During the parse phase, the server process looks at the data dictionary for information to  

  resolve object name and validate access.

- Size determined by shared pool sizing that is manage internally  by the database.

DATABASE BUFFER CACHE :

- Stores copies of data blocks that have been retrieved from the data files

- Enable great performance gains when you obtain and update data.

- Manage through LRU algorithm

- To see the size : SHOW PARAMETER DB_BLOCK_SIZE

- Can be dynamically resize by ALTER SYSTEM SET command

- The size of each buffer in Database buffer cashe is equal to the size of oracle block

REDO LOG BUFFER :

- Records all changes made to the database datablock / database buffer cashe

- Primary purpose is recovery

- Changes records within are called redo entries

- Redo entries contain the information necessary to re-create the data prior to the changes

  made by INSERT, UPDATE, DELETE, CREATE, ALTER, DROP operations.

- Size defined by LOG_BUFFER

LARGE POOL :

- An optional area of memory in SGA

- relieves the burden placed on the shared pool

USED FOR :

  I/O server process

  Backup and restore operation of RMAN

- Size defined by large pool size and can dynamically resize

JAVA POOL :

The Java pool is an optional setting but is required if you are installing and using JAVA.

Default size of JAVA pool is 24 MB

PROGRAMM/PERSONAL/PRIVATE/PROCESS GLOBAL AREA (PGA) :

PGA is a memory that contains data and control information for a single server process or a single background process.

Main Purpose: Memory reserved for each user process connecting to an oracle database

- Allocated when a process is created

- Deallocated when the process is terminated

- Used by only one process

PROCESS STRUCTURES

Oracle take advantage of various types of processes:

1- USER PROCESS:

   started at the time a database user request connection to the oracle server

2- SERVER PROCESS :

   Connect to the oracle instance and is started when a user establishes a session

3- BACKGROUND PROCESSES :

   started when an oracle instance is started

USER PROCESS :

- A program that request interaction with oracle server

- Must first establish a connection

- The user does not interact directly with the oracle server

  That's way it generates calls through the user program interface (UPI), which create a session   and starts a server process

- Started at the time a database user request connection to the oracle server

SERVER PROCESS :

- A program that directly interact with the oracle server

- Full fills call generated and return results

- can be dedicated and shared server

- The server process communicates with the oracle server using (OPI) oracle program interface

- Connect to the oracle instance and is started when a user establishes a session

BACKGROUND PROCESSES :

- Background process performs functions on behalf of invoking process.

- Background process performs I/O and monitors other oracle process to provide increase parallelism for better performance and reliability.

- Maintain & enforces relationship between physical and logical structure.

There are 5 mandatory background processes:

1- DBWn

2- LGWR

3- CKPT

4- SMON

5- PMON 

DATABASE WRITER (DBWN) :

- DBWn writes the dirty buffer (modified data) from the database buffer cache to the data file

- DBWn writes when

  1- CKPT occur

  2- dirty buffer reach threshold(Max) value

  3- There is no free buffer

  4- Timeout occurs

  5- RAC ping request is made

  6- Tablespace offline

  7- Tablespace read only

  8- Table Drop / Truncate

  9- Tablespace begin backup

LOG WRITER (LGWR) :

- LGWR performs sequential writes from the redo log buffer to the online redo log files.

- LGWR writes when

  1- At Commit

  2- At 1/3 fill

  3- Every 3 second

  4- When 1 MB fill

  5- Before DBWn writes

SYSTEM MONITOR (SMON) :

- If the oracle server fails any information in the SGA that has not been written to the disk is lost

  in this way background process SMON automatically performs recovery.

- Recovery Process consist of 3 steps

STEP # 1:   Rolls forward changes in online redo log file

Means : to recover the data that has not been recorded in the data files but has been recorded in the online redo log files

STEP # 2: Open database for user access

STEP # 3: Rollback Uncommitted data.

PROCESS MONITOR (PMON) :

- The background process PMON cleans up after fails process by

  1 - Rollback user current transactions

  2 - releasing all currently helt table or rowlocks

  3 - freeing other resources currently reserved by the user

  4 - Restarts dead dispatcher.

CHECKPOINT (CKPT) :

Every 3 second CKPT process store data in the control file to identify that place in the

online redo log file where recovery is to be begin which is called a check point

Purpose :  Signaling DBWn

           Updating Data file header with check point information

           Updating control file with check point information

ARCHIVER (ARCn) :

Optional Background process

- Automatically archives online redo log file when ARCHIVELOG MODE is set

- Preserve the record of all changes made to the database.

CONNECTION :

A connection is a communication pathway between a user process and oracle server

Dedicated server connection when there is one to one correspondance b/w user and server process

Session :

A session is a specific connection of a user to an oracle server.

The session start when the user is connected by the oracle server

and it ends when user log out and there is an abnormal termination.

LOGICAL STRUCTURE :

- Dictates how the physical space of a database is used

- Hierarchy consisting of

  Tablespace

  Segment

  Extent

  Blocks

An oracle database contain at least one tablespace

A tablespace contains one or more segments

A segment is made up of extents.

A extent is made up of logical blocks.

A block is the smallest unit for read and writes operations.

Further detail about logical structure discuss in chapter no 8 .

PROCESSING SQL STATMENTS

PROCESSING A QUERY :

- PARSE :    Search for identical statement

                     Check syntax, object names, and privileges

                     Lock object used during parse

                     create and store execution plan

- BIND :     Obtain values for variables

- EXECUTION : Process statments

- FETCH :     Return Row to user process

PROCESSING A DML QUERY :

PARSE , BIND , EXECUTION process work fetching in not required

--------------------------------  END OF CHAPTER 01  -----------------------------