Oracle | Autotrace

Oracle AUTOTRACE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 [oracle@test21 dbhome_1]$ sqlplus sys@pdbdev as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 23 03:03:33 2021 Version 21.1.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.1.0.0.0 SQL> @?/sqlplus/admin/plustrce.sql SQL> drop role plustrace; drop role plustrace * ERROR at line 1: 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