Wednesday, March 31, 2010

Difference with "traceonly explain", "autotrace on" and "traceonly statistics"

Explain plan is supported by default:

SQL> set autotrace traceonly explain


But reporting statistics requires plustrace role.

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> set autotrace traceonly statistics
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report



SQL> get ?\sqlplus\admin\plustrce.sql
1 --
2 -- Copyright (c) Oracle Corporation 1995, 2002. All Rights Reserved.
3 --
4 -- NAME
5 -- plustrce.sql
6 --
7 -- DESCRIPTION
8 -- Creates a role with access to Dynamic Performance Tables
9 -- for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
10 -- After this script has been run, each user requiring access to
11 -- the AUTOTRACE feature should be granted the PLUSTRACE role by
12 -- the DBA.
13 --
14 -- USAGE
15 -- sqlplus "sys/knl_test7 as sysdba" @plustrce
16 --
17 -- Catalog.sql must have been run before this file is run.
18 -- This file must be run while connected to a DBA schema.
19 set echo on
20 drop role plustrace;
21 create role plustrace;
22 grant select on v_$sesstat to plustrace;
23 grant select on v_$statname to plustrace;
24 grant select on v_$mystat to plustrace;
25 grant plustrace to dba with admin option;
26* set echo off
27