Oracle | Autotrace

Oracle AUTOTRACE Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

CDB Warning Thus script used to create the autotrace role can not be run in CDB$ROOT only within a PDB.

-- the fact that this role can not be created in CDB$ROOT is poor form by essentially any definition. I reported it during Beta 2 and it was not fixed.

Create PLUSTRACE Role sqlplus sys@pdbdev as sysdba SQL> @?/sqlplus/admin/plustrce.sql SQL> drop role plustrace; * ORA-01919: role 'PLUSTRACE' does not exist SQL> create role plustrace; Role created. SQL> grant select on v_$sesstat to plustrace; Grant succeeded. SQL> grant select on v_$statname to plustrace; Grant succeeded. SQL> grant select on v_$mystat to plustrace; Grant succeeded. SQL> grant plustrace to dba with admin option; Grant succeeded. SQL> SQL> set echo off

Grant PLUSTRACE Role GRANT plustrace TO <user_name>;

SQL> conn sys@pdbdev as sysdba -- connect as SYS to the pdbdev PDB

      

      SQL> GRANT plustrace TO uwclass;

Syntax and Demos

Syntax SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

Trace and Run SQL> conn uwclass@pdbdev Enter password: set autotrace on SELECT * FROM dual; D - X Execution Plan ---------------------------------------------------------- Plan hash value: 272002086 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 5 recursive calls 6 db block gets 6 consistent gets 0 physical reads 1548 redo size 564 bytes sent via SQL*Net to client 52 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

Trace Only set autotrace traceonly SELECT * FROM dual; Execution Plan ---------------------------------------------------------- Plan hash value: 272002086 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 5 recursive calls 6 db block gets 6 consistent gets 0 physical reads 1548 redo size 564 bytes sent via SQL*Net to client 52 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

Trace and Explain set autotrace traceonly explain SELECT * FROM dual; Execution Plan ---------------------------------------------------------- Plan hash value: 272002086 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------

Trace and Statistics set autotrace traceonly statistics SELECT * FROM dual; Statistics ---------------------------------------------------------- 5 recursive calls 6 db block gets 6 consistent gets 0 physical reads 1548 redo size 564 bytes sent via SQL*Net to client 52 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

Stop Tracing SET AUTOTRACE OFF

set autotrace off

      

      SELECT * FROM dual;

      

      D

      -

      X