 |
|
dbms_xplan Package
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:
The dbms_xplan package was introduced in
Oracle 9i Release 2 as a standard server-based method for displaying
execution plans. It is intended as a replacement for the utlxpls.sql
script which is now implemented using the dbms_xplan package. The
package contains a table function called display, which can be queried
to display an execution plan, as shown below.
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> SET
LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.display);
Plan hash value: 1863486531
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 |
4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 57 |
4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 37 |
3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 |
1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | |
0 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")
17 rows
selected.
SQL>
The display function can accept three parameters which affect
the output displayed.
table_name – The name of plan table to
be queried, the default value is 'PLAN_TABLE'.
statement_id – The statement_id of the
plan to be displayed, the default value is NULL.
format – This controls the level of
detail displayed, the default value is 'TYPICAL'. Other values
include 'BASIC', 'ALL' and 'SERIAL'.
The following example shows the usage of these
parameters.
SQL> EXPLAIN
PLAN SET STATEMENT_ID='TSH' FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL> SET
LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));
Plan hash value: 1863486531
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | EMP |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 4 | INDEX UNIQUE SCAN | PK_DEPT |
------------------------------------------------
11 rows
selected.
The next section will discuss the
identification of problem code at the database instance level.
Identifying the Impact of Code at the
Database Level
The first half of this chapter focused on
identification of performance problems in specific areas of PL/SQL and
SQL code. This is fine if the areas of concern are already known, but
how are these problem areas identified in the first place? The second
half of this chapter focuses on just that problem, allowing tuning
efforts to be directed at the areas of the application that are having
the biggest impact on system performance. The first area to be
explained is the use of the dynamic performance views.
|
