Lesson 4	Executing Oracle SQL
Objective	Explain the Process of SQL execution

Executing Oracle SQL Commands and translating SQL into an executable Form

The process of executing a SQL statement is quite complex. The SQL statement must be paired to ensure that the syntax is correct and then translated into an executable form for Oracle. In order to translate the SQL statement into executable form, Oracle will access the data dictionary to get information about all of the tables and indexes that are involved in the query. If the SQL is using the cost-based optimizer, Oracle will also access statistics about the nature and distribution of data. Once having gathered this information, Oracle will build an execution plan to run the SQL. The following SlideShow will demonstrate how a SQL statement is executed within Oracle.

1) Check to see if the SQL already exists in the Oracle library cache. This is accomplished by parsing the SQL statement and deriving a unique hash key.

2) The hash key is then referenced in memory. If the statement is found, then Oracle will execute the SQL.

3) If the statement is not found at the address, the SQL must be processed. Oracle will PARSE the SQL statement to ensure proper syntax.

4) If there is a syntax error, the process will terminate and return the error to the user.

5) the SQL will be used to formulate an execution plan — 5) If there is a syntax error, the SQL will be used to formulate an execution plan.

6) If the rule-based Optimizer is being used, Oracle dictionary entries (DBA_INDEXES) are passed. — 6) If the cost-based Optimizer is being used, Oracle statistics will be passed. If the rule-based Optimizer is being used, Oracle dictionary entries (DBA_INDEXES) are passed.

7) The optimizer then completes an execution plan and then proceeds to execute the SQL statement.

The importance of SQL Reusability

The preceding Slideshow demonstrates that the fastest way to execute SQL is to make the SQL re-usable. If Oracle detects that an already parsed statement can be used to service the query, then the statement can immediately run. Making SQL re-usable has the added benefit of keeping the library cache full of useful SQL, instead of leftover junk statements that will never be re-used. To ensure usability, put the SQL into a stored procedure and have all programs call the same, uniform SQL. The other method is to parameterize all SQL with host variables. For example, consider the following statements:

Select * from customer where last_name =  ‘Coulomb’;
Select * from customer where last_name = ‘Gauss’;
Select * from customer where last_name =  ‘Tesla’;

These would be recognized as three distinct SQL statements by Oracle and each would have to be parsed and built as separate processes. However, if we replaced the name with a host variable, the same statement could be re-used:

Select * from customer 
where last_name = ‘:VAR1’;

Remember, the SQL statements must be IDENTICAL.
For example, the following statements are NOT considered equivalent:
Now that we have a basic understanding of how SQL is processed, let us move deeper and look at the EXPLAIN PLAN utility.

Select * from Customer;
Select * from customer;