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:
- Understand SQL as a declarative language
- Create a PLAN tabletype
- Run the EXPLAIN PLAN utilitytype
- Detect full-table scans with EXPLAIN PLANtype
- Use EXPLAIN PLAN to diagnose table joinstype
- Read non-correlated subqueriestype
- Detect index range scanstype
- Analyze complex EXPLAIN PLAN outputtype
EXPLAIN PLAN can also be used to view the execution plan for a SQL statement that has already been executed, which can help to diagnose performance problems.
To use EXPLAIN PLAN, you simply need to prefix your SQL statement with the EXPLAIN PLAN keyword. For example, to view the execution plan for the following SQL statement:
SELECT * FROM customers WHERE country = 'USA';
You would use the following statement:
EXPLAIN PLAN SELECT * FROM customers WHERE country = 'USA';
EXPLAIN PLAN will return an output that shows the steps that Oracle will take to execute the SQL statement. The output will also show the estimated cost of each step.
EXPLAIN PLAN is a valuable tool for Oracle database administrators and developers. It can help to improve the performance of SQL queries and to diagnose performance problems.
Here are some examples of when you might want to use EXPLAIN PLAN:
- When you are writing a new SQL query and you want to make sure that it is performant.
- When you are troubleshooting a performance problem with an existing SQL query.
- When you are tuning a SQL query to improve its performance.
EXPLAIN PLAN is a powerful tool that can help you to improve the performance of your Oracle database.
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