Acknowledgments

Acknowledgments This PowerPoint is based on slides from: Lee, JeongKyu and Seema Sirpal

Oracle Query Execution 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?

UserProcess ServerProcess Connect User/Pwd Connect to Oracle – What Happens • 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? • 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 * From MyTab Order by 1;

“Select” – Parsing the Statement • Does “MyTab” exist? • Does user have Privs to select from “MyTab” • What columns are in “MyTab” • What is the first column in “MyTab” Select * From MyTab Order by 1;

“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? • 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 Insert (a,b,c) Into MyTab;

“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

Oracle Database Redo Log Files Control Files Data Files 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 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.

Acknowledgments