Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Understanding SQL Execution
Oracle Tips by Burleson
 

This chapter will focus on the internal processes that occur when Oracle receives and executes a SQL statement. We will examine the process of parsing the SQL statement, generating the execution plan, and executing the SQL statement. The four most important phases in SQL are parsing, binding, executing, and fetching the result set. Let’s take a closer look at how an Oracle SQL statement is transformed within the library cache.

1.      Parse the SQLDuring the parse phase, the Oracle software searches for the SQL statement in the shared pool, checks the syntax and semantics, and in some cases, rewrites the query.

  • Check security  This phase validates the security rules to ensure that the requestor is authorized to view the data. This involves comparing the privileges of the user against the names of the tables in the SQL.
  • Check SQL syntax  This phase checks the syntax of the SQL statement to ensure that the statement is properly formatted.
  • Optional query rewrite  In certain cases Oracle will rewrite the query, changing table names or replacing literals with host variables before passing the SQL to the optimizer.

2.      Execute the SQLThis phase involves the creation of the execution plan and the actual fetching of the table data.

  • Generate the execution plan  The generator accepts the parsed SQL and passes it to the SQL optimizer. The optimizer examines the SQL and the data dictionary and generates the internal execution plan. This plan is machine-level code that makes the required API calls to retrieve the data from disk. Oracle has numerous access methods to choose from when deciding how to service a query, and the optimizer is programmed to choose the access method that will either maximize speed or minimize computing resources.
  • Bind the execution plan  The bind process scans the SQL statement for bind variables and then assigns a value to each variable.
  • Execute the execution plan  The Oracle database applies the parse tree to the data buffers and performs all necessary disk I/O.
  • Fetch the result row  This step retrieves rows for a SELECT statement during the fetch phase. Each fetch retrieves multiple rows, using an array fetch.

3.      Present the result setThis phase performs all required sorting and translates and reformats all column data.

  • Sort the result set  This phase involves satisfying all order by or group conditions in the SQL statement.

4.      Translate column dataThis phase involves reformatting and translating any column data that has been transformed with a BIF. We will go into greater detail on some of these steps as we move through this chapter, but our focus will be on SQL parsing and the generation of the execution plan. Let’s begin by examining the parsing phase.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter