RelationalDBDesign 





Oracle SQL Tuning  «Prev  Next»
Lesson 1

Introduction to Oracle EXPLAIN PLAN Utility

In the previous module basic SQL tuning and the affects of ANSI standards were discussed.
The EXPLAIN PLAN utility is the foremost tool for demonstrating how Oracle accesses tables and indexes.
Because this module is one of the most challenging and technical modules, much of the information learned will be used or reinforced in other modules throughout the remainder of the course.
By the end of this module, you will be able to:
  1. Understand SQL as a declarative language
  2. Create a PLAN tabletype
  3. Run the EXPLAIN PLAN utilitytype
  4. Detect full-table scans with EXPLAIN PLANtype
  5. Use EXPLAIN PLAN to diagnose table joinstype
  6. Read non-correlated subqueriestype
  7. Detect index range scanstype
  8. Analyze complex EXPLAIN PLAN outputtype
In the next lesson, we will examine SQL access paths.
It is impossible to tune an Oracle database without understanding SQL tuning. Oracle is a SQL processing engine and the execution speed of any SQL query is influenced by many factors, both internal and external. As a declarative data access method, SQL relies on the Oracle cost-based optimizer to always choose the "best" execution plan for every SQL query. Oracle's SQL is among the most flexible and robust in the world, and along with this great power comes complexity. Tuning Oracle SQL is the single most important skill of any Oracle professional, and Oracle professionals are challenged to create SQL statements that will support thousands of concurrent executions with sub-second response time. Advanced Oracle SQL Tuning is a pragmatic treatment of Oracle SQL tuning, short on theory and big on real-world techniques and tips.