Lesson 1

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.

Oracle Database does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables. With bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan. From the text of a SQL statement, TKPROF cannot determine the types of the bind variables. It assumes that the type is CHARACTER, and gives an error message otherwise. You can avoid this limitation by putting appropriate type conversions in the SQL statement