Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle DBMS_XPLAN
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.