 |
|
SQL Execution Plans
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall, Oracle ACE of the year, 2006:
When an SQL statement is passed to Oracle, the
cost based optimizer (CBO) uses database statistics to create an
execution plan which it uses to retrieve the data in the most
efficient matter.
The previous section explained how the tkprof
utility was able to produce execution plans for traced SQL statements
in addition to formatting the trace output. An alternative
approach is to use the explain plan statement that produces the likely
execution plan without having to execute the statement. This is
especially useful for long-running queries in which executing the
statement may result in an unacceptable delay to the tuning process.
Also, the execution plans displayed in the tkprof output are actually
derived using this method.
The following examples query tables within the
SCOTT schema. If it is not already present, it can be created
using the utlsampl.sql script found in the $ORACLE_HOME/rdbms/admin
directory.
Before proceeding, the plan_table table must
be created to hold the execution plan data.
plan_table
The execution plan information is stored in a
table. For any of the following examples to work, this table must be
created using the utlxplan.sql script. This script can be
created in the current schema or in a shared schema. The
following example shows how it is created in the SYS schema and made
available to all users.
SQL> CONN
sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table
created.
SQL> GRANT
ALL ON sys.plan_table TO public;
Grant
succeeded.
SQL> CREATE
PUBLIC SYNONYM plan_table FOR sys.plan_table;
Synonym
created.
With the plan table present, execution plans
using the autotrace feature of SQL*Plus can then be generated.
autotrace
The simplest way to get an execution plan is
to use the autotrace feature of SQL*Plus. By switching on this
feature, an execution plan is displayed for all suitable statement
runes by SQL*Plus, as shown below.
SQL> SET
AUTOTRACE ON
SQL> SELECT *
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND e.ename = 'SMITH';
EMPNO ENAME JOB
MGR HIREDATE SAL
COMM
---------- ---------- --------- ---------- --------- ----------
----------
DEPTNO DEPTNO DNAME
LOC
---------- ---------- -------------- -------------
7369 SMITH CLERK
7902 17-DEC-80 800
20
20 RESEARCH DALLAS
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL)
OF 'EMP'
3 1 TABLE ACCESS (BY
INDEX ROWID) OF 'DEPT'
4
3 INDEX (UNIQUE SCAN) OF 'PK_DEPT'
(UNIQUE)
Statistics
----------------------------------------------------------
81 recursive calls
4 db block gets
27 consistent gets
0 physical reads
0 redo size
941 bytes sent via SQL*Net to
client
425 bytes received via SQL*Net
from client
2 SQL*Net
roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
This is a relatively easy way to get the
execution plan, but it requires that the statement must run to
completion. If the query is particularly inefficient or returns
many rows, this may take considerable time. A better option is
to run the explain plan statement manually, which is shown in the next
section.
Explain Plan
The explain plan statement generates the
execution plan for a query without executing the query itself,
allowing the execution plan for poorly performing queries to be
displayed without impacting the database. The following example
shows how the explain plan statement is used to generate an execution
plan.
SQL> EXPLAIN
PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
If multiple people are accessing the same plan
table, or a history of the execution plans is to be saved, the
statement_id clause of the explain plan statement should be used.
This associates a user specified identifier with each plan, which can
be used when retrieving the data. The following example shows
how the statement_id is set using the explain plan statement.
SQL> EXPLAIN
PLAN SET STATEMENT_ID = 'TIM' FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
At this point the execution plan data is
present in the plan_table, which can be queried using several methods
to display the execution plan.
utlxpls.sql
Prior to Oracle 9i Release 2, the utlxpls.sql
script or the utlxplp.sql script for parallel queries was used to
query the plan_table and display execution plan. The example
below shows the expected output from the script.
SQL> EXPLAIN
PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Plan Table
-------------------------------------------------------------------------------
| Operation
| Name | Rows | Bytes| Cost |
Pstart| Pstop |
-------------------------------------------------------------------------------
| SELECT STATEMENT |
| |
| |
| |
| NESTED LOOPS
| |
| |
| |
|
| TABLE ACCESS FULL |EMP
| |
| |
| |
| TABLE ACCESS BY INDEX RO|DEPT |
| |
| |
|
| INDEX UNIQUE SCAN |PK_DEPT
| |
| |
| |
-------------------------------------------------------------------------------
8 rows
selected.
SQL>
By default, the Oracle scripts do not accept a
statement_id parameter, but they can be modified to create a personal
script, like the explain.sql script shown below.
explain.sql
--
*****************************************************************
-- Parameters:
-- 1) Statement ID
-- *****************************************************************
SET PAGESIZE
100
SET LINESIZE 200
SET VERIFY OFF
COLUMN plan
FORMAT A50
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN bytes
FORMAT 9999999999
COLUMN cost
FORMAT 9999999
COLUMN partition_start FORMAT A20
COLUMN partition_stop FORMAT A20
SELECT LPAD('
', 2 * (level - 1)) ||
DECODE (level,1,NULL,level-1 || '.' ||
pt.position || ' ') ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,'',' (' ||
INITCAP(pt.options) || ')') plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = '&1'
CONNECT BY PRIOR pt.id = pt.parent_id
AND pt.statement_id = '&1';
The following example shows the output from
the explain.sql script.
SQL> EXPLAIN
PLAN SET STATEMENT_ID = 'TIM' FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL> @explain.sql
TIM
PLAN OBJECT_NAME
OBJECT_TYPE BYTES COST PARTITION_START
PARTITION_STOP
--------------------------------------------------
------------------------------ --------------- ----------- ----Select
Statement
57 4
1.1 Nested
Loops
57 4
2.1 Table Access (Full) EMP
TABLE 37 3
2.2 Table Access (By Index Rowid)
DEPT
TABLE 20 1
3.1 Index (Unique Scan) PK_DEPT
INDEX (UNIQUE) 0
5 rows selected.
The
utlxpls.sql script is still present in later versions of Oracle, but
it now displays the execution plan using the dbms_xplan package.
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
