The Oracle dbms_xplan
Utility
For
the dbms_xplan utility to work, the DBA or
developer first needs to create the plan
table. The DDL for this table is in the $ORACLE_HOME/rdbms/admin/utllxplan.sql
file.
The dbms_xplan.display_awr procedure
can be very helpful in diagnosing
Oracle performance issues.
The create table statement
for dbms_xplan can be submitted in any users
schema. The next step in using dbms_xplan is
running Explain Plan for a statement.
explain plan for
select * from employee where emp_id = 64523;
The command above will populate the plan
table with the data returned from the
optimizer. Next, the Oracle dbms_xplan
utility can be used to view the output.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Oracle
dbms_xplan Options
dbms_xplan has only one procedure - display.
dbms_xplan.display (
table_name in varchar2 default 'PLAN_TABLE',
statement_id in varchar2 default null,
format in varchar2 default 'TYPICAL');
The old PLAN_TABLE and
explain plan format can be replaced by what
DBMS_XPLAN
has
to offer. DBMS_XPLAN is a gateway not only
to SQL statements, but also into the AWR.
Armed with the SELECT_CATALOG_ROLE, a user
can view several dynamic performance views.
The package runs with the privileges of the
calling user, so the user needs to have
select privileges on
v$sql_plan
, v$session
, and
v$sql_plan_statistics_all
A simple implementation
is to add EXPLAIN PLAN FOR just before a
statement, and then view the plan by
issuing:
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Putting it together in an
example:
SQL> conn scott/tiger
Connected.
SQL> EXPLAIN PLAN FOR
2
SELECT * FROM emp e, dept d
3
WHERE e.deptno = d.deptno
4
AND e.ename='benoit';
Explained.
SQL> set lines 110 pages
35
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value:
3625962092
----------------------------------------------------------------------------------------
| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
1 |
57 |
4
(0)| 00:00:01 |
|
1 |
NESTED LOOPS
|
|
|
|
|
|
|
2 |
NESTED LOOPS
|
|
1 |
57 |
4
(0)| 00:00:01 |
|*
3 |
TABLE ACCESS FULL
| EMP
|
1 |
37 |
3
(0)| 00:00:01 |
|*
4 |
INDEX UNIQUE SCAN
| PK_DEPT |
1 |
|
0
(0)| 00:00:01 |
|
5 |
TABLE ACCESS BY INDEX ROWID| DEPT
|
1 |
20 |
1
(0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
3 - filter("E"."ENAME"='benoit')
4 - access("E"."DEPTNO"="D"."DEPTNO")
The
dbms_xplan
package has four
subprograms: DISPLAY, DISPLAY_AWR,
DISPLAY_CURSOR and DISPLAY_SQLSET. The
DISPLAY option was just shown in the prior
example. Since the SELECT statement is also
a cursor, take a look at the DISPLAY_CURSOR
function.
SQL> SELECT * FROM
table(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID
7v1g3p9b8052u, child number 0
-------------------------------------
SELECT * FROM table(DBMS_XPLAN.DISPLAY)
Plan hash value:
2137789089
-------------------------------------------------------------
| Id
| Operation
| Name
| Cost
|
-------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
29 |
|
1 |
COLLECTION ITERATOR PICKLER FETCH|
DISPLAY |
|
-------------------------------------------------------------
Note
-----
- cpu costing is off (consider
enabling it)
17 rows selected.
A big payoff in using
DBMS_XPLAN
is the quick and easy way of seeing what a
statement's SQL_ID value is. Not that the
DBA will be typing that odd-looking string
in all that much, but it does make some more
sophisticated queries easy to code since one
does not have to find the ID
The other benefit is that the ID can
be used to query again from the table
and be able to see past statements and
their plans
The DISPLAY function gets even more
granular than being able to query on older
ID. One can specify an input parameter for
format. The choices are ROWS, BYTES, COST,
PARTITION, and PARALLEL, to name a few
To pull AWR information,
use the DISPLAY_AWR function.
SELECT * FROM
table(DBMS_XPLAN.DISPLAY_AWR('7v1g3p9b8052u'));
Going up one level would
be tracing a session, either one's own or
someone else's via a remote interface.
Although some skilled people can read
through an unformatted trace file, mere
mortals can use TKPROF
to
format "trc" trace files. In addition to
basic formatting, the actual execution plan (explainy) as an input parameter will
output the plans.
Becoming more sophisticated in the
tracing is when wait events are also
analyzed. Wait analysis is the de facto
means of analyzing performance. The days of
X-whatever ratios are gone, although they
can be useful as trend indicators
Tracing at the TKPROF level
is good for SQL, but what about PL/SQL? That
is where DBMS_PROFILERcomes into play.
Above the individual session level are
STATSPACK and AWR. AWR reports, or what ADDM
generates from the AWR, are based on
STATSPACK reports. AWR reports, however,
contain much more drill down type of
information. AWR reports, available since
the release of Oracle 10g, also reflect the
increased amount of instrumentation found
within the RDBMS
The old
PLAN_TABLE and explain plan format
can be replaced by what
DBMS_XPLAN has to offer.
DBMS_XPLAN is a gateway not only
to SQL statements, but also into the AWR. Armed
with the
SELECT_CATALOG_ROLE, a user can
view several dynamic performance views. The
package runs with the privileges of the calling
user, so the user needs to have select
privileges on
V$SQL_PLAN, V$SESSION, and
V$SQL_PLAN_STATISTICS_ALL.
A simple implementation is to add EXPLAIN PLAN
FOR just before a statement, and then view the
plan by issuing:
SELECT * FROM
table(DBMS_XPLAN.DISPLAY);
Putting it together in an example:
SQL> conn scott/tiger
Connected.
SQL> EXPLAIN PLAN FOR
2
SELECT *
FROM emp e, dept d
3
WHERE
e.deptno = d.deptno
4
AND
e.ename='benoit';
Explained.
SQL> set lines 110 pages
35
SQL> SELECT * FROM
table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value:
3625962092
----------------------------------------------------------------------------------------
| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------
|
0 | SELECT
STATEMENT
|
|
1 |
57 |
4
(0)| 00:00:01 |
|
1 |
NESTED LOOPS
|
|
|
|
|
|
|
2 |
NESTED LOOPS
|
|
1 |
57 |
4
(0)| 00:00:01 |
|*
3 |
TABLE ACCESS FULL
| EMP
|
1 |
37 |
3
(0)| 00:00:01 |
|*
4 |
INDEX UNIQUE SCAN
| PK_DEPT |
1 |
|
0
(0)| 00:00:01 |
|
5 |
TABLE ACCESS BY INDEX ROWID| DEPT
|
1 |
20 |
1
(0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
3 -
filter("E"."ENAME"='benoit')
4 -
access("E"."DEPTNO"="D"."DEPTNO")
The package has four subprograms: DISPLAY,
DISPLAY_AWR, DISPLAY_CURSOR and DISPLAY_SQLSET.
The DISPLAY option was just shown in the prior
example. Since the SELECT statement is also a
cursor, take a look at the DISPLAY_CURSOR
function.
SQL> SELECT * FROM
table(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID
7v1g3p9b8052u, child number 0
-------------------------------------
SELECT * FROM
table(DBMS_XPLAN.DISPLAY)
Plan hash value:
2137789089
-------------------------------------------------------------
| Id
| Operation
| Name
| Cost
|
-------------------------------------------------------------
|
0 | SELECT
STATEMENT
|
|
29 |
|
1 |
COLLECTION ITERATOR PICKLER FETCH|
DISPLAY |
|
-------------------------------------------------------------
Note
-----
- cpu costing
is off (consider enabling it)
17 rows selected.
A big payoff in using
DBMS_XPLAN is the quick and easy
way of seeing what a statement's
SQL_ID value is. Not that the DBA
will be typing that odd-looking string in all
that much, but it does make some more
sophisticated queries easy to code since one
does not have to find the ID.
The other benefit is that the ID can be used
to query again from the table and be able to
see past statements and their plans.
The DISPLAY function gets even more granular
than being able to query on older ID. One can
specify an input parameter for format. The
choices are ROWS, BYTES, COST, PARTITION, and
PARALLEL, to name a few.
Related notes on DBMS_XPLAN:
Oracle
dbms_xplan
dbms_xplan Package - Identifying the
Impact of Code at the ...
Chinese dbms_xplan
Oracle 11g New Features Tips - 11g
Evaluation Workflow for SQL ...
Oracle Undocumented Utilities
gather_plan_statistics hint tips
PL/SQL Performance Tuning Training
Course
Oracle 11g provides the following
information about the dbms_xplan package:
Description of the dbms_xplan package:
FUNCTION DBMS_XPLAN.BUILD_PLAN_XML RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
PLAN_ID NUMBER IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
PLAN_TAG VARCHAR2 IN DEFAULT
REPORT_REF VARCHAR2 IN DEFAULT
FUNCTION DBMS_XPLAN.DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
FUNCTION DBMS_XPLAN.DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DBMS_XPLAN.DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DBMS_XPLAN.DISPLAY_PLAN RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
TYPE VARCHAR2 IN DEFAULT
FUNCTION DBMS_XPLAN.DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQLSET_NAME VARCHAR2 IN
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT
FUNCTION DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DBMS_XPLAN.FORMAT_NUMBER RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION DBMS_XPLAN.FORMAT_NUMBER2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION DBMS_XPLAN.FORMAT_SIZE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION DBMS_XPLAN.FORMAT_SIZE2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION DBMS_XPLAN.FORMAT_TIME_S RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION DBMS_XPLAN.PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PLAN_CUR REF CURSOR IN
I_FORMAT_FLAGS BINARY_INTEGER IN
FUNCTION DBMS_XPLAN.VALIDATE_FORMAT RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HASPLANSTATS BOOLEAN IN
FORMAT VARCHAR2 IN
FORMAT_FLAGS BINARY_INTEGER OUT
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
and include the URL for the page.
