Oracle Database - Autotrace

Card Puncher Data Processing

About

Sql Developer Autotrace

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.

Autotrace Output

Autotrace output has two parts :

  • the query plan report
  • the statistics

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.

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

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>





Discover More
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - SQL*Net Statistics

The SQLNet Statistics are a part of the autotrace statistics and you can find three parts on it : How much you sent to the server : bytes received via SQLNet from client How much the server send to...
Card Puncher Data Processing
Oracle Database - bytes sent via SQL*Net to client

bytes sent via SQLNet to client is a which is a part of the autotrace statistics and means: How much the server send to you Just by selecting only the columns that are relevant to you.
Card Puncher Data Processing
Oracle Database - Buffer IO (Logical IO)

A buffer is a container for data. A logical I/O, also known as a buffer I/O, refers to reads and writes of buffers in the buffer cache. When a requested buffer is not found in memory, the database performs...
Card Puncher Data Processing
Oracle Database - EXPLAIN PLAN and Autotrace Comparison

First, I create a partition table and I generate random data with the DBMS_RANDOM package (). We see with : that a full table scan will be performed in the table t the cost for each step (3) ...
Card Puncher Data Processing
Oracle Database - Physical Read

The physical read statistic from the autotrace statistics is a measure of how much real I/O, or physical i/o, your query performed. A physical read of table or index data places the block into the buffer...
Card Puncher Data Processing
Oracle Database - Rows (of Card as Cardinality for 9i)

Cardinality is the second measure that Query Plan Estimator use to estimate a execution plan. It represents the number of rows in a row set that the Optimizer expects to come back. Here, the row set...
Card Puncher Data Processing
Oracle Database - SQL*Net roundtrips to from client

SQLNet roundtrips to from client is a statistics that you can see in the autotrace statistics and means : How much SQLNet mesages are sent to and received from the client. This includes round-trips...
Card Puncher Data Processing
Oracle Database - Session Statistics (V$SESSTAT)

The second part of the autotrace output are a part of the session statistics. V recursive_calls : Number of SQL...
Card Puncher Data Processing
Oracle Database - sorts (Disk)

sorts (Disk) is a statistic metric of autotrace that you can see in the autotrace statistics and confirm that Oracle use the temporary tablespace.



Share this page:
Follow us:
Task Runner