Transcript Oracle Query Execution

This PowerPoint is based on slides from:
Lee, JeongKyu and Seema Sirpal
Advanced Databases (ADAB)
4. Feb. 2010
By Henrik Høltzer
SQL Statements (IUDS)
 Insert
 Update
 Delete
 Select
What happens when
someone connects to
the database and issues
one of these
statements?
Connect to Oracle – What Happens
User
Process
Connect User/Pwd
Server
Process
 Are “User” definition and privileges in SGA
“Dictionary Cache”?
 If not, try to fetch from the “System” tablespace
 If there, validate Pwd and privileges to login
 Allow connection if all is right; otherwise decline
connection
“Select” – What Happens First?
Select *
From MyTab
Order by 1;
 Is this statement in the “Library Cache”?
 If statement cached then it has been recently been
“parsed” and “executed”
 If not cached then “parse” the statement
 If cached then skip parsing stage and execute statement
“Select” – Parsing the Statement
Select *
From MyTab
Order by 1;
 Does “MyTab” exist?
 Does user have Privs to select from “MyTab”
 What columns are in “MyTab”
 What is the first column in “MyTab”
“Select” – Executing the Statement
Select *
From MyTab
Order by 1;
 Is “MyTab” data in the SGA “Buffer Cache”?
 If not, fetch data into Buffer Cache
 Sort data in “MyTab” by the first column
 If can sort records in memory then do so
 If cannot sort in memory then use “Temporary”
tablespace as disk-based staging area
 Return records to client process
“I/U/D” – What Happens First?
Insert (a,b,c)
Into MyTab;
 Is this statement in the “Library Cache”?
 If statement cached then it has been recently been
“parsed” and “executed”
 If not cached then “parse” the statement
 If cached then skip parsing stage and execute statement
“I/U/D” – Executing the Statement
Insert (a,b,c)
Into MyTab;
 Validate values to be inserted
 Execute the statement
 Keep DB version of the record in “Undo” tablespace
until Commit or Rollback
 Record changes in SGA “Redo Log Buffer”
 Change records in SGA “Buffer Cache”
 DBWn writes changed records to data file(s) as part of
buffer cache management
“I/U/D” – Executing the Statement
Insert (a,b,c)
Into MyTab;
Commit;
or
Rollback;
 If “Commit” then…
 LGWn writes “Redo Log Buffer” entries to “Redo Logs”
 Undo entries are invalidated
 If “Rollback” then…
 Migrate DB version of record in Undo back to
tablespace/data file
Archivelog Mode
 If Instance in “Archivelog” mode Redo Log Files are
“Archived” to Archive Logs; which may be used to
recover your database in the case of disaster
Oracle Database
Data Files
Control Files
Redo Log Files
Archived Logs
ARCn
Summary
 What we called a “Database” is really an “Instance”
and a “Database.”
 An Oracle “Instance” is not persistent. It consists
of the System Global Area (SGA) and a collection
of processes.
 An Oracle “Database” is persistent. It consists of a
collection of “Required” and “User” tablespaces
(with corresponding data files) along with other
supporting files.
 Nearly every component of the “Instance” and
“Database” are mobilized to execute SQL
statements.