autotrace enhancement in 10g release 2

In what is possibly the shortest article on oracle-developer.net, we will look at a small enhancement to SQL*Plus (Autotrace, specifically) in 10g Release 2 (10.2). Many developers will be aware that Autotrace has several output formats; one of these being a theoretical execution plan (via explain plan). In prior releases of SQL*Plus, Autotrace had its own formatting for explain plan, but in 10.2, it uses the now-standard DBMS_XPLAN package.

To demonstrate this, we will run a simple select from the EMP table on a 10.2 database with the autotrace traceonly setting in SQL*Plus. The traceonly setting tells Autotrace to execute the query, record and report a number of workload statistics and also to explain the query and provide its theoretical plan. Note that despite Autotrace providing actual workload statistics, the plan is purely theoretical.

The demonstration is as follows.

SQL> set autotrace traceonly
 
SQL> SELECT *
  2  FROM   emp
  3  WHERE  sal > 1000;


12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    13 |   416 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    13 |   416 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">1000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1298  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

why is this significant?

Despite being a seemingly minor enhancement, this is significant because DBMS_XPLAN exposes more information about our plan than was previously available. In particular, we can see the filter and access predicates used by the query. These can help us to diagnose a range of issues such as implicit datatype conversions (disabling index use, for example), view merging, transitive closure, predicate pushing, missing predicates and even excessive user-function calls. It also begins to expose some of the methods used by Oracle to re-write and optimise our queries.

a note on database versions

This new feature of SQL*Plus will also work against 10g Release 1 (10.1) databases. The lowest version tested is 10.1.0.3 but it is probably the case that all 10.1.0.x patch releases will be supported. When using the 10.2 SQL*Plus against a 9.2 database, however, Autotrace reverts to its "classic" format (despite 9i Release 2 being the version in which DBMS_XPLAN was introduced).

further reading

For more information on Autotrace, read the SQL*Plus User's Guide and Reference. The documentation for DBMS_XPLAN can be found in the PL/SQL Packages and Types Reference. An oracle-developer.net article on 10g extensions to DBMS_XPLAN can be found here.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, August 2005

Back to Top