Oracle 9i Architecture
Oracle server Consist of database, instance and oracle software.
Oracle instance consist of Memory structure (sga) and background process.
Oracle database consist of datafile, logfile, controlfile and other files
Oracle datafile consist of tablespace, segment, extent, block
Shared Pool
Shared pool can holds the most recently used SQL/PLSQL statements. The statement can be shared among the oracle users. The SP is divided into two cache call Library Cache (LC) and Data Dictionary Cache (DDC). SP is maintaining its room based on LRU list.
Library Cache
The LC has an area called Shared SQL and Shared PL/SQL. Where the SQL statements and PLSQL packages get stored. When a query execute for the first time. It will be parsed. So, what is Parse?.
The LC has an area called Shared SQL and Shared PL/SQL. Where the SQL statements and PLSQL packages get stored. When a query execute for the first time. It will be parsed. So, what is Parse?.
How Parsing is done
Syntax : Checking the spelling errors in the statement
Semantic : Checking whether the object (table, index, cluster) are present the DB
Checking Whether the user has privilege to access those objects.
Hash Value : Generate hash value for the statement
Execution Plan : Generate and execution plan and choose the best path to obtain the data
Cursor :
Cursor stores the Hash Value and Execution plan in its area. When another executing similar or exact statement. The result will fetch quickly.
Data Dictionary Cache
DDC hold the information about the Dictionary information. Meta information.
DDC hold the information about the Dictionary information. Meta information.
Database Buffer Cache - DBBC
It’s a Cache to hold the piece of information for frequent access. The Blocks are maintained by LRU list. Once the block gets completely filled it is called Dirty Block, it will be maintained by Dirty List.
Redo Log Buffer – RLBC
What are the changes made to database has recorded in RLBC.
How SELECT Statement Work
1. The server process work for user process. Take the select query to the Library Cache in SP.
2. The Library Cache in SP parses the select statement. Check the syntax, whether object available or not. Whether the user has access to select the object is called symantic. If these are fine, then it will generate a hash value for the query and create a execution plan.
3. If the object is already present in the DBBC, it quickly obtains the data from the buffer cache.
4. If the object not present in the DBBC, the server process obtain the block from Datafile and place it in buffer cache for subsequent usage.
5. Step 3 and 4 are execution. Based on the execution plan it will get the data from buffer.
6. If the select statement has any sorting e.g. Order by, group by. These sorting processes are done in PGA sort area. After the statement has been sorted it will fetch to user
What are the changes made to database has recorded in RLBC.
How SELECT Statement Work
1. The server process work for user process. Take the select query to the Library Cache in SP.
2. The Library Cache in SP parses the select statement. Check the syntax, whether object available or not. Whether the user has access to select the object is called symantic. If these are fine, then it will generate a hash value for the query and create a execution plan.
3. If the object is already present in the DBBC, it quickly obtains the data from the buffer cache.
4. If the object not present in the DBBC, the server process obtain the block from Datafile and place it in buffer cache for subsequent usage.
5. Step 3 and 4 are execution. Based on the execution plan it will get the data from buffer.
6. If the select statement has any sorting e.g. Order by, group by. These sorting processes are done in PGA sort area. After the statement has been sorted it will fetch to user
When Commit executes
LGWR start writes the block from RLBC to ONRLF. Then the check point fined the committed statements CNS
No comments:
Post a Comment