11 Replies Latest reply on Jan 17, 2012 12:43 PM by 890169

    Question about AUTOTRACE, SP2-0618 and SP2-0611

    783956
      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 ?
        • 1. Re: Question about AUTOTRACE, SP2-0618 and SP2-0611
          754304
          Hello ,


          You can manuwal trace of the user id and with serial# as falllowas.


          exec dbms_system.set_sql_trace_in_session(8,1877,TRUE);

          if you wont to take trace at dynamically level . You have to grant PLUSTRACE privs to that user ,on which user you going to trace .
          grant plustrace to user1;
          conn user1/passs
          set autotrace on
          i this may help you.

          Edited by: Shiv on Aug 7, 2010 11:18 PM
          1 person found this helpful
          • 2. Re: Question about AUTOTRACE, SP2-0618 and SP2-0611
            Aman....
            John,

            From 10.2 onwards, creation of the plan_table is not needed as the table is precreated inthe Sys schema with the name plan-table$ and its public synonym is made available to all the users with the name plan_table . For the rest, see if the below demo helps ,
            SQL> select * from V$version;
            
            BANNER
            -------------------------------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
            PL/SQL Release 11.2.0.1.0 - Production
            CORE    11.2.0.1.0      Production
            TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
            NLSRTL Version 11.2.0.1.0 - Production
            
            SQL> drop user john purge;
            drop user john purge
                           *
            ERROR at line 1:
            ORA-00921: unexpected end of SQL command
            
            
            SQL> drop user john cascade;
            
            User dropped.
            
            SQL> @D:\oracle\product\11.2.0\dbhome_1\sqlplus\admin\plustrce
            SQL>
            SQL> drop role plustrace;
            
            Role dropped.
            
            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> create user john identified by john;
            
            User created.
            
            SQL> grant connect, resource to john;
            
            Grant succeeded.
            
            SQL> grant create table to john;
            
            Grant succeeded.
            
            SQL> grant plustrace to john;
            
            Grant succeeded.
            
            SQL> conn john/john
            Connected.
            SQL> create table test(a number);
            
            Table created.
            
            
            SQL> set autot lon
            Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
            SQL> set autot on
            SQL> select * from test;
            
            no rows selected
            
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 1357081020
            
            --------------------------------------------------------------------------
            | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
            --------------------------------------------------------------------------
            |   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
            |   1 |  TABLE ACCESS FULL| TEST |     1 |    13 |     2   (0)| 00:00:01 |
            --------------------------------------------------------------------------
            
            Note
            -----
               - dynamic sampling used for this statement (level=2)
            
            
            Statistics
            ----------------------------------------------------------
                     24  recursive calls
                      0  db block gets
                      2  consistent gets
                      0  physical reads
                      0  redo size
                    281  bytes sent via SQL*Net to client
                    409  bytes received via SQL*Net from client
                      1  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      0  rows processed
            
            SQL>
            HTH
            Aman....
            • 3. Re: Question about Book
              754304
              Hi Aman,


              I wont to study RAC ,which book is good for RAC .

              Please could you suggest me.

              Edited by: Shiv on Aug 7, 2010 11:30 PM
              • 4. Re: Question about AUTOTRACE, SP2-0618 and SP2-0611
                Aman....
                Shiv wrote:
                Hi Aman,


                I wont to study RAC ,which book is good for RAC .
                Please don't hijack a thread. What RAC has to do with autotrace issues?

                Anyways, I would suggest to start with the RAC guide available at the oracle doc site as the first place.

                Aman....
                • 5. Re: Question about AUTOTRACE, SP2-0618 and SP2-0611
                  783956
                  Hi Aman,

                  I followed your instructions using a new user called "john" and everything went fine just as you had it in your post.

                  When I first tried it with HR (without dropping HR), I'd get the explain plan and error ORA-942 instead of the statistics. Logging HR off, then re-granting the PLUSTRACE role to HR and logging HR back in solved the problem. It looks like that, for PLUSTRACE it is important for the user to be logged out when the role is granted otherwise, it looks like there is a part of the process that doesn't "stick".

                  Problem solved now though :)

                  Thank you for your help... again ;)

                  John.
                  • 6. Re: Question about AUTOTRACE, SP2-0618 and SP2-0611
                    Aman....
                    Hi John,
                    It looks like that, for PLUSTRACE it is important for the user to be logged out when the role is granted otherwise, it looks like there is a part of the process that doesn't "stick".
                    That's not just with the PLustrace role. Any role would be working when the next time the user is going to log in. Please see below where using two sessions, I shall grant a user some privs and would see whether its working for the user or not.

                    *Session1
                    BANNER
                    --------------------------------------------------------------------------------
                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                    PL/SQL Release 11.2.0.1.0 - Production
                    CORE    11.2.0.1.0      Production
                    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                    NLSRTL Version 11.2.0.1.0 - Production
                    
                    SQL> create user john identified by john;
                    
                    User created.
                    
                    SQL> grant connect to john;
                    
                    Grant succeeded.
                    
                    SQL> create role dangerous ;
                    
                    Role created.
                    
                    SQL> grant drop any table,create any index to dangerous;
                    
                    Grant succeeded.
                    
                    SQL> conn john/john
                    Connected.
                    SQL> select * from session_privs;
                    
                    PRIVILEGE
                    ----------------------------------------
                    CREATE SESSION
                    So far, what I did that I created a user John, granted him a direct priviledge. There is a role also that's prepared and would be granted to the user while still he being connected. See below, this is the second session,
                    SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 8 15:33:09 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> grant dangerous to john;
                    
                    Grant succeeded.
                    I would check the available permissions for the user in the session. This command was entered in the first session where the user john was already connected.
                    SQL> /
                    
                    PRIVILEGE
                    ----------------------------------------
                    CREATE SESSION
                    You can see that there is no change in the session privs. Now, let's disconnect and connect again,
                    SQL> disconn
                    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    SQL> conn john/john
                    Connected.
                    SQL> select * from session_privs;
                    
                    PRIVILEGE
                    ----------------------------------------
                    CREATE SESSION
                    DROP ANY TABLE
                    CREATE ANY INDEX
                    All he privs are active now for the user.

                    Now, the privs in the role, if would be updated, would be reflecting right away in the user's currently connected session. I added one more priv to the role in the 2nd session and it was coming in the 1st session, see below please,
                    session-2
                    SQL> grant alter any table to dangerous;
                    
                    Grant succeeded.
                    
                    SQL>
                    
                    session-1
                    
                    SQL> /
                    
                    PRIVILEGE
                    ----------------------------------------
                    CREATE SESSION
                    ALTER ANY TABLE
                    DROP ANY TABLE
                    CREATE ANY INDEX
                    
                    SQL>
                    I copied extra lines I guess, deleted them now.

                    Aman....

                    Edited by: Aman.... on Aug 8, 2010 3:56 PM
                    1 person found this helpful
                    • 7. Re: Question about AUTOTRACE, SP2-0618 and SP2-0611
                      783956
                      Hi Aman,

                      Thank you for taking the time to create that additional example. From it, I gather that a role acts as a container of privileges that are assigned to the role.

                      The container (role) will be active the next time the user logs into the database, it won't be reflected in a current session (if there is one).

                      On the other hand, if the container (role) is already associated with the user and a new privilege is added to the container then, the new privilege is "active" immediately because the container role is already associated with the user.

                      Can you confirm that my understanding is correct ? (or incorrect - hopefully not ;) )

                      Thank you

                      John.
                      • 8. Re: Question about AUTOTRACE, SP2-0618 and SP2-0611
                        Aman....
                        Hi John,
                        role acts as a container of privileges that are assigned to the role.
                        Correct!
                        The container (role) will be active the next time the user logs into the database, it won't be reflected in a current session (if there is one).On the other hand, if the container (role) is already associated with the user and a new privilege is added to the container then, the new privilege is "active" immediately because the container role is already associated with the user.
                        AFAIK, its correct :) .

                        Aman....
                        1 person found this helpful
                        • 9. Re: Question about AUTOTRACE, SP2-0618 and SP2-0611
                          783956
                          >
                          AFAIK, its correct :).
                          >

                          Wonderful

                          Thank you :),

                          John
                          • 10. Re: Question about AUTOTRACE, SP2-0618 and SP2-0611
                            Aman....
                            You're welcome :) . Glad I could help!

                            Aman...
                            1 person found this helpful
                            • 11. Re: Question about AUTOTRACE, SP2-0618 and SP2-0611
                              890169
                              Run into the same error-massage on 11g.
                              According to the docuemntation/error-message the PLUSTRACE - role is required. But with 11g it seems that it's actually the SELECT_CATALOG_ROLE which autotrace really wants...
                              As currently neither the documentation nor Metalink is up to date on this matter I thought this finding may be helpful.
                              Cheers
                              Konrad