Oracle Database - Autotrace

Autotrace is:

• a feature of sql plus
• can also be found in SQL Developer

AUTOTRACE provides you:

• an an execution plan (such as explain plan)
• and some important statistics regarding its actual execution.

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.

• Oracle Database - EXPLAIN PLAN and Autotrace Comparison

Autotrace output has two parts :

• the query plan report
• the statistics

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 - The Query Plan Generator generate many different Oracle Database - Execution/Query Plan for the same query and the Oracle - 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.

The second part of the autotrace output are the 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

• recursive_calls : Number of SQL statements executed in order to execute your SQL statement

db block gets and consistent gets are the most important part of the output. They represent the Oracle Database - Buffer IO (Logical IO). The less we latch, the better.

• db block gets : Total number of block read from the buffer cache in current mode
• consistent gets : Number of times, a consistent read was requested for a block in the buffer cache. Consistent reads may require read asides to the undo (rollback) information, and these reads to the undo will be counted here as well.

• Oracle Database - Physical Read : Number of physical reads from the datafiles into the buffer cache

• Oracle Database - Redo Size statistics : Total amount of redo generated in bytes during the execution of this statement

SQL*Net Statistics

• bytes sent via SQL*Net to client : Total number of bytes sent to the client from the server
• bytes received via SQL*Net from client : Total number of bytes received from the client
• SQL*Net roundtrips to from client : Total number of SQL*Net mesages sent to and received from the client. This includes round-trips for fetches from a multiple-row result set.

• sorts (memory) : Sort done in the user's session memory (sort area). Controlled via the sort_area_size database parameter.
• sorts (disk) : Sorts that use the disk (temporary tablespace) because the sort exceeded the user's sort area size.

• Rows processed by modifications or returned from a SELECT statement.

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>

You can control the report by setting the AUTOTRACE system variable :

• SET AUTOTRACE OFF: No autotrace
• SET AUTOTRACE ON EXPLAIN. Only the optimizer execution path is shown.
• SET AUTOTRACE ON STATISTICS. Only the SQL Statement execution statistics is shown.
• SET AUTOTRACE ON. All
• SET AUTOTRACE TRACEONLY. Show me the statistics but hold the data. The same that ON but without the printing of the user's query output.