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.
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 (explain=y) 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_PROFILER comes 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.
All of the data collected to obtain the big
picture has to be gathered from all of the
active sessions. The details, which are
aggregated and summarized to create the big
picture ADDM or AWR report, come from the
V$ACTIVE_SESSION_HISTORY dynamic
view. This view, and many of the related
wrh$_* views, are explained in
detail in Oracle Wait Event Tuning High
Performance with Wait Event Interface Analysis
(Stephen Andert, Rampant TechPress, 2004).
Conclusion
There is a plethora of information Oracle
collects about itself and what users are doing.
With each newer release of the RDBMS engine,
Oracle is becoming more self-aware and
intelligent. The optimizer is becoming more
sophisticated, even though at a root level, it
must apply rules to the conditions it sees, and
the advisory framework is then used to surface
information to the database administrator. The
ideal end-state would be a database that is
completely self-aware and able to flawlessly
diagnose and correct itself. That is a good
thing, just as long as it does not become like
Skynet in the Terminator
series.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
