Whenever a user wants database connection then the user submits the request at client. The default client is sqlplus. Whenever the client application is started for running then user or client process is created, this process is responsible for interacting with remote DB server.
ORACLE SERVER PROCESS:
It is created and maintained on DB server machine for handling the user’s request. Initially one main or master server process is created later as per the type of database connection more server processes are created. The DB connections are given in two modes.
- Dedicated DB connection
- Shared DB connection
Dedicated DB connection:
It is given to the users who uses the database server several number of hours.
Ex: – railway reservation clerk
Shared DB connection:
In this one server process is used by several number of users simultaneously for running database requests with short execution time.
Ex: – railway reservation through IRCTC
Note: – for every server process one PGA is associated.
ORACLE INSTANCE:
It is used to perform operations on database. It is the combination of memory structures and background processes.
MEMORY STRUCTURES:
Memory structures are broadly classified into two types
- Program Global Area
- System Global Area
Program Global Area:
PGA is used to maintain the users request along with data and related details. This will be there until the users request is completed its execution. It consists of the following memory structures.
1.Private SQL Area:
It consists of two sub structures
1.Run time Area
2.Persistent Area
1.Run time Area:-
It is used to maintain the request submitted by the users. It also maintains the runtime status of users requests. Finally the users request is thrown out of the runtime area as soon as its execution completed.
2.Persistent Area:-
It maintains the intermediate results that are generated by the requests during its execution.
2.Session memory:
A session memory is nothing but a period time between connection and disconnection to the database based on the physical location of the client. The current connection settings ranges in terms of language, date, time format, currency etc.
Session memory is responsible for maintaining user connection settings. Session memory is configured in PGA fordedicated connections whereas session memory is configured in large pool of SGA if the DB connection is shared type.
3.SQL Work Area:
It consists of three sub structures
1.Sort Area
It is used to arrange the data in ascending or descending order as part of execution of requests. The default size is 64K. DBA customizes its size based on the data to be sorted frequently.
If data to be sorted is more than the size of Sort Area then the sorting is performed in TEMP Tablespace.
2.Bitmap Merge Area
It is used to merge to sets of data
3.Hash Area
In order to improve the performance of DB server Hash Area is used by generating hash values. These hash values are used for various purpose.
To find out whether the users requests are identical or not.
Indexing and clustering
Table partitions etc.
If ever two users requests are same then those requests are said to be identical. Oracle server processes identical requests for one time by avoiding repeated processing of same requests. In order to find out the requests are identical or not hash values are used. For each request hash values are generated.
System Global Area (SGA)
It is common storage area used by all database users. It consists of the following memory structures
- Shared pool
- DB Buffer cache
- Redolog buffer
- Java pool
- Stream pool
- Flashback buffer
- Large pool
Shared pool:
Dictionary cache:-
This memory structure is used to maintain the list of database objects such as tables, views, users, Tablespaces etc. This list is used to authenticate whether the specified object is present or not for the users request. Later it checks for the definition of the database objects. If it is not present in the dictionary cache then the definition is loaded from data dictionary cache.
If there is no storage space in dictionary cache then the oldest unused definitions are thrown out of from dictionary cache. In order to accommodate new definition, by using a Least Recently Used (LRU) technique.
Library cache:-
It is used to maintain details about the recently submitted users requests. It consists of the following memory structures
1.Shared SQL Area:- it consists the submitted sql commands, hash value, parsed tree and execution plan of sql commands.
2.PL/SQL Area:- it maintains PL/SQL programmes, hash value, parsed tree and execution plan of PL/SQL programme.
3.Private SQL Area:- in shared mode private sql area is maintained in Library cache.
4.Library Handlers:- maintains list of SQL commands and PL/SQL programmes present in the library cache.
5.Control Structures:- it controls the execution of user requests by maintain the details in locks and laches.
Locks:- it maintains details of locks applied on data dictionary object.
Latches:- latches provides the permission to use the database objects in orderly manner.
Server result cache:-
From 11g onwards the data that is to be delivered to the requested user is passed through this memory structures.
Reserved pool:-
It maintains common storage as a bank of memory and it is given to other memory structures of shared pool on demand.
Fixed Area:-
It maintains the code needed for the operation of Oracle DB server.
DB buffer cache:-
Database buffer cache is used to maintain data in the form of buffers related to the recent database operations such as insert, update, delete and select etc. . In order to execute any of these commands that required data is handle with the help of data blocks belongs to the table.
Whenever a table is created by default DB server allots 64KB of storage in terms of 8 data blocks of 8KB size. Initially all the data blocks are maintained in the database file.
Whenever there is a DML or select operation is there then the suitable data blocks are loaded into the buffer of database buffer cache.
For ex:- to execute insert operation a suitable empty block of the table is loaded on to the buffers.
While loading the block, reference is pushed at hot end of the LRU list. LRU list is maintain to keep track the usage of data blocks. The most recently data block reference is stored at hot end of LRU. Whereas the blocks that are not used for a long time are pushed towards cold end of LRU List.
DB server maintains a cut of point called Thresh hold point approximately to indicate unused blocks for a long time. So DB server removes these aged out blocks from the buffer cache.
States of data blocks are buffers
1.Clean state:-
Indicates committed data present in data block
2.Dirty state:-
Indicates the block content is changed due to insert, update or delete operation but not committed or rollback so far.
3.Empty state:-
Indicates the data block is having free storage for storing the data.
4.Unused state:-
Indicates data block is allotted but not at used so far.
Whenever Thresh hold point occurs DB server throws out aged out blocks from buffer cache. At that time it preserves the dirty blocks because those are required to apply commit or rollback.
From 11g on wards the data blocks are preserved in flash buffer to avoid unnecessary disk writings. In 10g or if flash buffer is full in 11g or 12c , the dirty blocks are preserved in the table storage by over writing the corresponding data block in the table. At this time the original data block is preserved in UNDO Tablespace as undo data.
This undo data is maintained up to by default 900 sec. Before this time the user must apply commit or rollback. Otherwise DB server is not responsible for data changes on the data block.
If commit is applied changes are made permanent or rollback is applied redo data is restored to its original location.
Types of buffer pools
1.Default pool:-
It is a collection of buffers of 8KB sized from 10g onwards. In 9i it is 4KB. These buffers are used to maintain data block of 8KB sized.
2.Optional default pool
If ever the row size are very small or large when compared to 8K then it is possible to store that type of data in different sized data blocks those are 2K, 4K, 16K and 32K. DBA need to configure them as per the requirement.
3.Optional keep pool
It is to maintain any fixed data throughout the running of Oracle Instance. This buffer is not managed with LRU list.
4.Optional recycle pool
It is used to maintain data only related to the current transactions. Later its content is thrown out immediately.
Redolog buffer:-
It is used to maintain the copy of changed data block whenever insert, update or delete is executed.
Java pool:-
Since Oracle DB server is integrated with java it is possible to run java programmers directly on oracle instance with help of java pool.
Flashback buffer:-
It is used to maintain committed old data of a table with which it possible to know any table’s data at a particular point of time in the past.
Stream pool:-
It maintains the data that is to be transferred to another server which is mirror database to the first server.
Large pool:-
I/O buffer: – it is used to transport data during import /export operation.
Request and Response Queue: – the users request and responses are maintained in orderly manner by these Queues.
RMAN Buffer:- it is used during Backup and Recovery operations for transferring database files.
XA Interface:- it is used to run advanced applications java, xml etc.
Session memory:- the users session settings are maintained in large pool if database is in shared mode.
BACKGROUND PROCESSES
- Database Writers (DBWn):-
It is used to write content of the database buffer cache into database files. Maximum of 10 writers can be used. To avoid frequently writing operations on the data files DB writers write the data only a considerable amount of data is accumulated. Since DB writes are writing data with some delay these are called lazy writers.
- Check Pointers (CKPT) :-
The changes occurred on the database due to operation such as insert, update, delete etc. are maintained with the help of unique identification number called system change number (SCN).
These numbers are stored not only in the control files but also in the headers of data files. DB server uses these scn’s to maintain the state of the database.
- Log Writer (LGWR) :-
It is used to write the changes occurred on the database immediately in any of the following situations.
1.Every 3 sec.
2.When commit is applied.
3.When 1/3 of redo log buffer is full.
- Archiver (ARCH) :-
It is an optional process used to take backup of online redolog files as archive log file, if ever database is in archive log mode.
- Recover Writer (RVWR) :-
It is used to write content of flash_back buffer into flashback files provided database flashback is ON.
- System Monitor (SMON) :-
It is used to monitor oracle instance. It recovers the instance whenever it is crashed.
- Process Monitor (PMON) :-
It is used to monitor all processes that are running on the oracle instance.
- Locks (LCKn):-
It provides locks on resources (rows, tables etc.) to allow users to work the resources without any confliction.
- Recoverer (RECO) :–
It is used to recover the database whenever it is damaged.
- Dispatcher (Dn) :-
In shared server mode the data to be delivered by a server process simultaneously is performed through dispatcher.
Parsing users Request:-
Parsing is nothing but compilation or evaluation of users request present in the form of sql commands or pl/sql programmes.
Steps in parsing:-
Parser is a software application installed as part of oracle DB server software and it evaluates a user’s request in the following steps.
Step1:– loading users request for processing.
Step 2:- syntax verification.
In this step the users request is verified against the grammar of sql and pl/sql. If it violates the grammar an error is thrown to the user.
Step 3:-
In this the meaning of the user’s requests is verified against the following aspects
- Whether the object is existing in DB or not.
- Checks whether the user having permission on the database object.
- Is definition is satisfied for the user request.
Step 4:-
Even though DB users are professionals, their requests are required to be converted into standard format as per the sql or pl/sql. This improves the performance of execution.
Step 5:-
Whenever a sql command is required to be executed DB server checks for the available methods for execution out of which the cheapest and best method is used for execution. This is called dynamic sampling technique introduced from 10g onwards.
Types of parsing:-
- Hard parsing:-
If a user’s request of that type is submitted for the first time to DB server then it performs all the steps of parsing because there is no old history related to the request.
- Soft parsing:-
If ever the submitted user’s request is already executed recently then some of the details are available in library cache such as hash value, parsed tree and execution plans.
So based on the situation either step 3 or step 4 or step 5 executed.
Dedicated DB connection:
It is given to the users who uses the database server several number of hours.
Ex: – railway reservation clerk
Shared DB connection:
In this one server process is used by several number of users simultaneously for running database requests with short execution time.
Ex: – railway reservation through IRCTC
Note: – for every server process one PGA is associated.
ORACLE INSTANCE:
It is used to perform operations on database. It is the combination of memory structures and background processes.
MEMORY STRUCTURES:
Memory structures are broadly classified into two types
1.Private SQL Area:
It consists of two sub structures
1.Run time Area
2.Persistent Area
1.Run time Area:-
It is used to maintain the request submitted by the users. It also maintains the runtime status of users requests. Finally the users request is thrown out of the runtime area as soon as its execution completed.
2.Persistent Area:-
It maintains the intermediate results that are generated by the requests during its execution.
2.Session memory:
A session memory is nothing but a period time between connection and disconnection to the database based on the physical location of the client. The current connection settings ranges in terms of language, date, time format, currency etc.
Session memory is responsible for maintaining user connection settings. Session memory is configured in PGA fordedicated connections whereas session memory is configured in large pool of SGA if the DB connection is shared type.
3.SQL Work Area:
It consists of three sub structures
1.Sort Area
It is used to arrange the data in ascending or descending order as part of execution of requests. The default size is 64K. DBA customizes its size based on the data to be sorted frequently.
If data to be sorted is more than the size of Sort Area then the sorting is performed in TEMP Tablespace.
2.Bitmap Merge Area
It is used to merge to sets of data
3.Hash Area
In order to improve the performance of DB server Hash Area is used by generating hash values. These hash values are used for various purpose.
To find out whether the users requests are identical or not.
Indexing and clustering
Table partitions etc.
If ever two users requests are same then those requests are said to be identical. Oracle server processes identical requests for one time by avoiding repeated processing of same requests. In order to find out the requests are identical or not hash values are used. For each request hash values are generated.
Dictionary cache:-
This memory structure is used to maintain the list of database objects such as tables, views, users, Tablespaces etc. This list is used to authenticate whether the specified object is present or not for the users request. Later it checks for the definition of the database objects. If it is not present in the dictionary cache then the definition is loaded from data dictionary cache.
If there is no storage space in dictionary cache then the oldest unused definitions are thrown out of from dictionary cache. In order to accommodate new definition, by using a Least Recently Used (LRU) technique.
Library cache:-
It is used to maintain details about the recently submitted users requests. It consists of the following memory structures
1.Shared SQL Area:- it consists the submitted sql commands, hash value, parsed tree and execution plan of sql commands.
2.PL/SQL Area:- it maintains PL/SQL programmes, hash value, parsed tree and execution plan of PL/SQL programme.
3.Private SQL Area:- in shared mode private sql area is maintained in Library cache.
4.Library Handlers:- maintains list of SQL commands and PL/SQL programmes present in the library cache.
5.Control Structures:- it controls the execution of user requests by maintain the details in locks and laches.
Locks:- it maintains details of locks applied on data dictionary object.
Latches:- latches provides the permission to use the database objects in orderly manner.
Server result cache:-
From 11g onwards the data that is to be delivered to the requested user is passed through this memory structures.
Database buffer cache is used to maintain data in the form of buffers related to the recent database operations such as insert, update, delete and select etc. . In order to execute any of these commands that required data is handle with the help of data blocks belongs to the table.
Whenever a table is created by default DB server allots 64KB of storage in terms of 8 data blocks of 8KB size. Initially all the data blocks are maintained in the database file.
Whenever there is a DML or select operation is there then the suitable data blocks are loaded into the buffer of database buffer cache.
For ex:- to execute insert operation a suitable empty block of the table is loaded on to the buffers.
While loading the block, reference is pushed at hot end of the LRU list. LRU list is maintain to keep track the usage of data blocks. The most recently data block reference is stored at hot end of LRU. Whereas the blocks that are not used for a long time are pushed towards cold end of LRU List.
DB server maintains a cut of point called Thresh hold point approximately to indicate unused blocks for a long time. So DB server removes these aged out blocks from the buffer cache.
States of data blocks are buffers
1.Clean state:-
Indicates committed data present in data block
2.Dirty state:-
Indicates the block content is changed due to insert, update or delete operation but not committed or rollback so far.
3.Empty state:-
Indicates the data block is having free storage for storing the data.
4.Unused state:-
Indicates data block is allotted but not at used so far.
Whenever Thresh hold point occurs DB server throws out aged out blocks from buffer cache. At that time it preserves the dirty blocks because those are required to apply commit or rollback.
From 11g on wards the data blocks are preserved in flash buffer to avoid unnecessary disk writings. In 10g or if flash buffer is full in 11g or 12c , the dirty blocks are preserved in the table storage by over writing the corresponding data block in the table. At this time the original data block is preserved in UNDO Tablespace as undo data.
This undo data is maintained up to by default 900 sec. Before this time the user must apply commit or rollback. Otherwise DB server is not responsible for data changes on the data block.
If commit is applied changes are made permanent or rollback is applied redo data is restored to its original location.
Types of buffer pools
1.Default pool:-
It is a collection of buffers of 8KB sized from 10g onwards. In 9i it is 4KB. These buffers are used to maintain data block of 8KB sized.
2.Optional default pool
If ever the row size are very small or large when compared to 8K then it is possible to store that type of data in different sized data blocks those are 2K, 4K, 16K and 32K. DBA need to configure them as per the requirement.
3.Optional keep pool
It is to maintain any fixed data throughout the running of Oracle Instance. This buffer is not managed with LRU list.
4.Optional recycle pool
It is used to maintain data only related to the current transactions. Later its content is thrown out immediately.
I/O buffer: – it is used to transport data during import /export operation.
Request and Response Queue: – the users request and responses are maintained in orderly manner by these Queues.
RMAN Buffer:- it is used during Backup and Recovery operations for transferring database files.
XA Interface:- it is used to run advanced applications java, xml etc.
Session memory:- the users session settings are maintained in large pool if database is in shared mode.
No comments:
Post a Comment