Oracle Database Discussions

Good morning,

I wanted to look at the execution plan(s) for some sample queries. I did a little research using Google, found out about autotrace, plan_table, plustrace role and utlxplan. I followed the instructions I found but the result isn't quite as expected.

Here is an output of a freshly started session:

0 01:50 [SS1TB Attic-Z] [5G] [750M] Z:\Documentation\Oracle\
Oracle Concepts\Scripts>sqlplus hr/abc123

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 8 01:50:16 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from plan_table;

  COUNT(*)
----------
         2

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> select count(*) from plan_table;

  COUNT(*)
----------
         2


Execution Plan
----------------------------------------------------------
Plan hash value: 3662021055

-------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |            |     1 |            |          |
|   2 |   TABLE ACCESS FULL| PLAN_TABLE |     2 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL>

1. the first select is there to ensure I have a plan_table, that checks.
2. When I set autotrace on, I get SP2-0618 and SP2-0611 for reasons that I cannot figure out.
3. the second select is there to find out if the autotrace in step 2 had any effect. It did since now the explain plan is displayed.

The question is: if it is working (as step 3 demonstrates) then why I am getting those errors in step 2 ? and what do I have to do to not get those errors anymore ?

Thank you (again!) for your help,

John.

Note: I created the plustrace role, granted it select on v_$sesstat, v_$statname, v_$session, then I granted plustrace to hr. Was there something else needed ?