Oracle Database - Autotrace

> Database > Oracle Database

1 - About

Autotrace is:

AUTOTRACE provides you:

Autotrace is fully accessible to each developer while tkprof relies on access to a trace file.

“set autotrace on” doesn’t work with a PL/SQL procedure.

Advertising

3 - Autotrace Output

Autotrace output has two parts :

  • the query plan report
  • the statistics

3.1 - The query plan report

Execution Plan
----------------------------------------------------------
Plan hash value: 4015478428

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |    15 |  1755 |    13  (16)| 00:00:01 |
|   1 |  VIEW                          |         |    15 |  1755 |    13  (16)| 00:00:01 |
|   2 |   UNION-ALL                    |         |       |       |            |          |
|*  3 |    HASH JOIN OUTER             |         |    14 |   798 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | EMP     |    14 |   518 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL          | DEPT    |     4 |    80 |     3   (0)| 00:00:01 |
|   6 |    MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   8 |      INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  9 |     SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00:00:01 |
|  10 |      TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

In this query plan output, the optimizer information represents the following :

  • cost : The cost assigned to each step of the query plan by the optimizer. The Optimizer Oracle Database - The Query Plan Generator generate many different Sql Engine - (Physical|Execution) Plan for the same query and the Oracle Database - The Query Plan Estimator assigns a cost to each and every one. The execution plan with the lowest cost wins. In the full outer join example, we can see the total for this query is 13.
  • Rows (of Card as Cardinality for 9i) : It's the estimated number of rows that will flow out of a given query plan step. In the full outer join example, we can see the optimizer expects there to be 518 rows in EMP and 80 in DEPT.
  • Bytes : The size in bytes of the data the optimizer expects each step of the plan to return. This is dependent on the number of rows (card) and the estimated width of the rows.

In 9i and below, if the cost, card and bytes is not present, it's a clear indicator that the query was executed using the RBO and not the CBO.

3.2 - Statistics

The second part of the autotrace output are the session statistics.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
       1598  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         15  rows processed
Advertising

4 - How to install autotrace to a user

Below the action to perform in order to set autotrace to a user :

C:\Documents AND Settings\Nicolas>sqlplus SYS/password@ORCL AS sysdba
 
SQL*Plus: RELEASE 10.2.0.4.0 - Production ON Mon Nov 10 14:57:48 2008
 
Copyright (c) 1982, 2007, Oracle.  ALL Rights Reserved.
 
 
Connected TO:
Oracle Database 10g Enterprise Edition RELEASE 10.2.0.4.0 - Production
WITH the Partitioning, OLAP, Data Mining AND REAL Application Testing options
 
SQL> grant PLUSTRACE TO scott;
grant PLUSTRACE TO scott
      *
ERROR AT line 1:
ORA-01919: role 'PLUSTRACE' does NOT exist
 
SQL> @C:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.SQL
 
SQL> grant plustrace TO scott;
 
Grant succeeded.
 
SQL>