AUTOTRACE and TKPROF

As we have seen the result of select count(*) from cat; with TKPROF utility http://rakeshocp.blogspot.com/2009/05/tkprof.html now we will compare the result with autotrace.

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

SQL> conn sys/rock@rock as sysdba
Connected.

SQL> grant plustrace to hr;
grant plustrace to hr
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> conn sys/rock@rock as sysdba
Connected.
SQL> @D:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql;
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>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
SQL>
SQL> grant plustrace to hr;
Grant succeeded.
SQL> show userUSER is "SYS"
SQL> conn hr/hr@rock
Connected.
SQL> set autotrace on
SQL>

SQL> select count(*) from cat;
COUNT(*)
----------
21

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view

SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
333 recursive calls
0 db block gets
1060 consistent gets
7 physical reads
0 redo size
411 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

You don’t need any special system privileges in order to use the EXPLAIN PLAN statement. However, you do need to have INSERT privileges on the plan table, and you must have sufficient privileges to execute the statement you are trying to explain. The one difference is that in order to explain a statement that involves views, you must have privileges on all of the tables that make up the view. If you don’t, you’ll get an “ORA-01039: insufficient privileges on underlying objects of the view” error.
http://www.dbspecialists.com/files/presentations/use_explain.html