Explain plans
During my previous post I’ve
showed you that the main “action zone” in which one RDBMS keeps all the needed
resources to access the data in best and fastest way, is the memory and the
core of RDBMS thinking to achieve the same objectives of best and fastest
response, is the optimizer.
The SQL language used to query
and modify data in a database has the following characteristics: it is
supported by all major commercial database systems, it’s standardized, it’s
interactive via graphical user interfaces or prompt, or it’s embedded in
programs, and is declarative, based on relational algebra, meaning that in SQL
you write pretty simple queries where you say exactly what you want to get from
database and the query doesn’t need to describe how to get the data from the
database. This declarative characteristic of the SQL lead to the importance of
the optimizer in a database system, because the optimizer has to translate the
SQL queries in exact steps used to access the data.
When choosing the best and
fastest way to get the data from database, the optimizer generates all the
possible plans to access data queried and chooses the best one. This plan,
called explain plan, is the main tool in seeing and understanding how the
optimizer will retrieve the data from the database.
In Oracle database server there
are some ways to access this explaining plan depending the time when the SQL
statement was executed.
For the statements that will be
run, if the query is run from SQL Plus tool, there are the following options: set autotrace on to see the rows, the
explain plan and the statistics of the query, set
autotrace traceonly to get only the explain plan and the statistics
after the query has ran and set
autotrace traceonly explain to get only the explain plan without
executing the query. The last option is useful in getting only the explain plan
in the situations of a long running query.
For a specific sql explain plan
can be generated using SQL command EXPLAIN PLAN FOR <statement>, and can
be viewed using the dbsm_xplan package with the following query:
SELECT * FROM TABLE (dbms_xplan.display);
When using SQL Developer as a
tool for writing SQL, the explain plan can be viewed with F10 key and the autotrace output can
be viewed with F6 key.
For a series of statements,
explain plans can be generated using the DBMS_MONITOR package to enable trace
running for statements between DBMS_MONITOR.SESSION_TRACE_ENABLE
and DBMS_MONITOR.SESSION_TRACE_DISABLE
statements. The trace files with explain plans will be generated in path
specified by background_dump_dest
internal parameter, and can be formatted to be human readable using TKPROF tool.
To get the explain plan of the
currently running SQLs, you have to query internal dictionary to identify this
SQL, by its session identifier or by its text, get its SQL identifier (SQL_ID)
and then use the package DBMS_XPLAN
to get the explain plan for its SQL_ID.
SELECT * FROM
TABLE(dbms_xplan.display_cursor('&SQLID'));
For the SQL that were executed previously
and now are stored in history dictionary tables of Automatic Workload
Repository (AWR), explain plan can be viewed also by SQL_ID using
SELECT * FROM
TABLE(dbms_xplan.display_awr('&SQLID'));
To get the SQL_IDs from the
internal dictionaries, the user has to have corresponding privileges to access
those structures.
This is a sample execution plan:
Plan hash value: 529144872
------------------------------------------------------------------------------------------------
| Id |
Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
3 | 189 | 8
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | |
| | |
| 2 | NESTED LOOPS | | 3 |
189 | 8 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 3 |
141 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID|
EMPLOYEES | 3 |
60 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 3 |
| 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| JOBS |
1 | 27 | 1
(0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | JOB_ID_PK |
1 | | 0
(0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | DEPT_ID_PK
| 1 | |
0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS |
1 | 16 | 1
(0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 -
access("E"."EMPLOYEE_ID"<103)
7 -
access("E"."JOB_ID"="J"."JOB_ID")
8 -
access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
The reading of the explain plan have
the following general guidelines: the first statement is the one that has the
most indentation and if two statements appear at the same level of indentation,
the top statement is executed first.
Each explain plan generated by
the optimizer has an estimated cost and the optimizer will choose in execution
of a specific statement the plan with the smallest cost. But this will not
guarantee that this plan is the fastest way to return the rows. So, in practice
all statements should have explained plans verified and understood.
In MySQL and PostgreSQL, there is
also a similar command EXPLAIN
that, if precedes a SQL statement, will display information from the optimizer
about the query execution plan.
Each database server has its own
tools and commands to get and view the explain plans, but the main goal is to
understand how the server chose to interpret our query and how it translates in
steps to get data from databases. This is why the explain plan of a statement
is the first step in SQL tuning even from the development phase.
In my next post I intend to show
you some interesting things about NULL values. So, stay close.
No comments:
Post a Comment