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

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Concepts by Burleson Consulting

Understanding SQL Execution

Now that we have a general understanding of what happens to an SQL statement inside library cache, it's time to take a closer look at how optimizer SQL within a relational database.

Some of the more sophisticated relational databases such as Oracle and DB2 have specialized techniques that you can use in order to make SQL statements fully reentrant.  Many database management systems also have an automated query rewrite facility, where many sub-optimal SQL query form can be detected and dynamically re-written into a more optimal form.

This section explores techniques to reduce excessive re-parsing for SQL statements, and then takes a look at how you can view the actual execution plan for specific SQL statements.  Once we are able to view the execution plan for an SQL statement, the next step is to take a look at the table and index access methods that are available to service a query.

As we discussed in the last tutorial, the fall-back position for the SQL optimizer is to perform a full table scan, reading every row in the table.  Of course, it is far more efficient for an SQL statement to use index access to get the table rows, especially when the where clause of the SQL query has highly restrictive clauses.

We are now ready to investigate the details of how row data is accessed from SQL.  We will start by exploring the process of determining the optimal table join order because this is one of the most important decisions to the optimizer must make.  For example, an SQL query that joins six tables together has six factorial (6!) possible combinations of table joins.  This is 6 x 5 x 4 x 3 x 2, or 720 possible ways that the SQL optimizer can join these six tables together.  This can take a huge amount of time!  Oracle provides a hint called ORDERED to allow us to manually specify the table join order, and bypass the expense of determining the optimal table join order.

Once the optimizer is determined the optimal order in which to join the tables together, the SQL optimizer must then decide upon the appropriate method for joining the tables together.  The Oracle database offers several table join methods.  Oracle has a sort merge join, a nested loop join, and a hash join method, all of which serve the common purpose of joining to tables together, but with radically different internal mechanisms and external performance.

The most common table join mechanisms are nested loop joins and hash joins.  In a nested loop join, the database reads and index, builds a list of ROW ID’s and then probes into the second table for the matching rows.

In a hash join, Oracle uses RAM memory to speed the join.

In a hash join, the Oracle database does a full-scan of the driving table, builds a RAM hash table, and then probes for matching rows in the other table.  For certain types of SQL, the hash join will execute faster than a nested loop join, but the hash join uses more RAM resources.  The propensity of the SQL optimizer to invoke a hash join is heavily controlled by the setting for the hash_area_size Oracle parameter.  The larger the value for hash_area_size, the more hash joins the optimizer will invoke. As we will soon learn in , the hash_area_size defaults to double the value of the sort_area_size parameter.  Here is how to see these values on your Oracle database:

SQL> show parameters area_size 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------- bitmap_merge_area_size               integer     1048576                        create_bitmap_area_size              integer     8388608
hash_area_size                       integer     1048576
sort_area_size                       integer     524288
workarea_size_policy                 string      MANUAL     

Pay special attention to the readings on sort operations, and understanding how Oracle first attempts to sort row results sets inside the RAM memory of the SGA or PGA.  Sorting can occur whenever an SQL statement contains an order by, or group by clause. If there is no room in the RAM memory region to sort the results set quickly, Oracle will go to the temporary tablespace, and complete the sort operation using disk storage.  The management of sorting is a very critical part of SQL tuning because RAM memory sorts are many thousands of times faster than sort that have to be done inside the temporary table space.

For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

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


email BC:

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

Oracle® is the registered trademark of Oracle Corporation.