You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements. To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role.
You can setup AUTOTRACE as below:
- Run @$ORACLE_HOME/rdbms/admin/utlxplan.sql as someone with CREATE TABLE and CREATE PUBLIC SYNONYM priviledges.
- Run @$ORACLE_HOME/sqlplus/admin/plustrce.sql as SYSDBA
- Issue GRANT PLUSTRACE TO PUBLIC (or SCHEMA_NAME) as SYSDBA or DBA
You can control the report by setting the AUTOTRACE system variable:
- SET AUTOTRACE OFF – No AUTOTRACE report is generated. This is the default.
- SET AUTOTRACE ON EXPLAIN – The AUTOTRACE report shows only the optimizer execution path.
- SET AUTOTRACE ON STATISTICS – The AUTOTRACE report shows only the SQL statement execution statistics.
- SET AUTOTRACE ON – The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
- SET AUTOTRACE TRACEONLY – Like SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.
Description of Database Statistic Names:
- recursive calls – Number of recursive calls generated at both the user and system level. Oracle Database maintains tables used for internal processing. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
- db block gets – Number of times a CURRENT block was requested.
- consistent gets – Number of times a consistent read was requested for a block
- physical reads – Total number of data blocks read from disk. This number equals the value of “physical reads direct” plus all reads into buffer cache.
- redo size – Total amount of redo generated in bytes
- bytes sent through SQL*Net to client – Total number of bytes sent to the client from the foreground processes.
- bytes received through SQL*Net from client – Total number of bytes received from the client over Oracle Net.
- SQL*Net round-trips to/from client – Total number of Oracle Net messages sent to and received from the client
- sorts (memory) – Number of sort operations that were performed completely in memory and did not require any disk writes
- sorts (disk) – Number of sort operations that required at least one disk write
- rows processed – Number of rows processed during the operation
The client referred to in the statistics is SQL*Plus. Oracle Net refers to the generic process communication between SQL*Plus and the server, regardless of whether Oracle Net is installed. You cannot change the default format of the statistics report.
- To trace statements with the query output:
SQL> SET AUTOTRACE ON
SQL> SELECT * FROM t;
ID RSTRING
---------- --------------------
1 dwjUOhcLbvGBdccAJuJS
2 muIaimrBxmStjcDGUuvr
3 nSZmKaqAtuZoDsFLiaWL
4 UvFKYKIyXYsxNjFwVybb
5 gRqAeXFSDaRpqACMdilh
6 ziyGdelYqgnPsNMzSKdb
7 lanNZuKqRqdUgaiKwcZV
8 EshvneoJKfoOIZuYdIxv
9 VPtXHVaoplVXCKXYEcWk
10 ZyFaPYtJjfGRrTtLNIjc
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 20150 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 10 | 20150 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
775 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
- To trace statements without the query output:
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT * FROM t;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 20150 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 10 | 20150 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
775 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
- To trace statements with only the statistics:
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SELECT * FROM t;
10 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
775 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
- To trace statements only with the execution plan:
SQL> SET AUTOTRACE TRACEONLY EXPLAIN SQL> SELECT * FROM t; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 20150 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T | 10 | 20150 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement
- To trace statement running with the parallel query option:
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT /*+ PARALLEL (t 2) */ * FROM t;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3050126167
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 20150 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 10 | 20150 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 10 | 20150 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | 10 | 20150 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
95 recursive calls
4 db block gets
20 consistent gets
0 physical reads
680 redo size
775 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed