Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
  

 

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.


 

     

Remote DBA Service
 

Oracle Tuning Book

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.