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.
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.