Re: Strange behavior on enabling autotrace..

From: nirav <shivam71_at_gmail.com>
Date: 13 Dec 2005 05:27:14 -0800
Message-ID: <1134480434.803233.327040@g43g2000cwa.googlegroups.com>

see detailed outputs here:

FROM SERVER: (without using connection string)

---

SQL> conn user1/tmp
Connected.
SQL> set autotrace on
SQL> select * from ns_a;

         A B
---------- -------------------

         1 12/13/2005 19:05:08
         2 12/14/2005 19:05:08
         3 12/15/2005 19:05:08
         4 12/16/2005 19:05:08
         5 12/17/2005 19:05:08
         6 12/18/2005 19:05:08
         7 12/19/2005 19:05:08
         8 12/20/2005 19:05:08
         9 12/21/2005 19:05:08
        10 12/22/2005 19:05:08

10 rows selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=110)    1 0 FILTER
   2 1 TABLE ACCESS (FULL) OF 'NS_A' (Cost=2 Card=10 Bytes=110)

Statistics

---

         17  recursive calls
         24  db block gets
         10  consistent gets
          1  physical reads
        768  redo size
        740  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>

---

FROM CLIENT: SQL> CONN user1/TMP@<connection_string>
Connected.
SQL> SET AUTOTRACE ON
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report SQL> select * from ns_a;

         A B
---------- ---------

         1 13-DEC-05
         2 14-DEC-05
         3 15-DEC-05
         4 16-DEC-05
         5 17-DEC-05
         6 18-DEC-05
         7 19-DEC-05
         8 20-DEC-05
         9 21-DEC-05
        10 22-DEC-05

10 rows selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=110)    1 0 FILTER
   2 1 TABLE ACCESS (FULL) OF 'NS_A' (Cost=2 Card=10 Bytes=110)

End of output...

Note that here the statistics report is not obtained..

Also about necessary roles see:

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF

OS_
------------------------------ ------------------------------ --- ---
---

user1                		  CONNECT                        NO  YES NO
user1                 		 PLUSTRACE                      NO  YES NO
user1                		  RESOURCE                       NO  YES NO

Received on Tue Dec 13 2005 - 07:27:14 CST