This discussion is archived
1 2 Previous Next 19 Replies Latest reply: May 2, 2007 3:15 AM by Satyaki_De RSS

ERROR:ORA-01031: insufficient privileges

NicloeiW Journeyer
Currently Being Moderated
Hi,

When i execute this command, i receive the error as mentioned in subject line,

nic >SET autotrace traceonly explain

ERROR:ORA-01031: insufficient privileges

SP2-0611: Error enabling EXPLAIN report


Should i contact my DBA or run utxplan.sql scripts ?

Regards
nic

Message was edited by:
Nicloei W
  • 1. Re: ERROR:ORA-01031: insufficient privileges
    566473 Newbie
    Currently Being Moderated
    Tom Kyte, Expert one-on-one Oracle:

    Setting up AUTOTRACE in SQL*PLUS
    Throughout the book it will be useful for us to monitor the performance of the queries we
    execute by getting a report of the execution plan used by the SQL optimizer, along with
    other useful execution statistics. Oracle provides a tool called EXPLAIN PLAN that, with
    use of the EXPLAIN PLAN command, allows us to generate this execution plan output.
    Note For information about interpreting the output of EXPLAIN PLAN, see the Oracle8i
    Designing and Tuning for Performance guide.
    However, SQL*PLUS provides an AUTOTRACE facility that allows us to see the execution
    plans of the queries we╩╣ve executed, and the resources they used, without having to use
    the EXPLAIN PLAN command. The report is generated after successful SQL DML (that is,
    SELECT, DELETE, UPDATE, and INSERT) statements. This book makes extensive use of
    this facility. There is more than one way to to configure the AUTOTRACE facility. These
    are the steps that I use:
    • cd [ORACLE_HOME]/rdbms/admin
    • log into SQL*PLUS as SYSTEM
    • run @utlxplan
    • run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
    • run GRANT ALL ON PLAN_TABLE TO PUBLIC;
    If you wish, you can replace the GRANT...TO PUBLIC with a GRANT to a specific user.
    By granting the privelege to the PUBLIC role, you are effectively letting anyone trace
    using SQL*PLUS. This is not a bad thing in my opinion as it prevents each and every user
    from having to install their own plan table. The alternative is for you to run @UTLXPLAN
    in every schema where you want to use the AUTOTRACE facility.
    The next step is to create and grant the PLUSTRACE role:
    • cd [ORACLE_HOME]/sqlplus/admin
    • log into SQL*PLUS as SYS
    • run @plustrce
    • run GRANT PLUSTRACE TO PUBLIC;
    Again, if you wish, you can replace PUBLIC in the GRANT with a specific user.
  • 2. Re: ERROR:ORA-01031: insufficient privileges
    NicloeiW Journeyer
    Currently Being Moderated
    Hi Papin,

    Thanks, this is working fine,

    Now lets say i have xplain plan as follows

    SELECT STATEMENT ALL_ROWSCost: 6 Bytes: 826 Cardinality: 14                     
         6 FILTER                
              5 MERGE JOIN OUTER Cost: 6 Bytes: 826 Cardinality: 14           
                   2 TABLE ACCESS BY INDEX ROWID TABLE DEPT Cost: 2 Bytes: 80 Cardinality: 4      
                        1 INDEX FULL SCAN INDEX (UNIQUE) PK_DEPT Cost: 1 Cardinality: 4
                   4 SORT JOIN Cost: 4 Bytes: 546 Cardinality: 14      
                        3 TABLE ACCESS FULL TABLE EMP Cost: 3 Bytes: 546 Cardinality: 14


    this explain plan is taken from some other thread,
    The subscript 1,2,3 are given by michale to understand what does oracle executes first,

    but when i go explain plan i dont get these subscripts, is there any way to get these subscripts of it comes with experience
  • 3. Re: ERROR:ORA-01031: insufficient privileges
    RobvanWijk Oracle ACE
    Currently Being Moderated
    If you use "select * from table(dbms_xplan.display)", you get a nicely formatted output.
    The indentation will tell you precisely what step is executed before another, if you know how to read them (in short: from inside out).

    Regards,
    Rob.
  • 4. Re: ERROR:ORA-01031: insufficient privileges
    Satyaki_De Guru
    Currently Being Moderated
    Rob!

    Can you pls explain me what went wrong in this code --
    SQL> 
    SQL>
    SQL> select * from table(dbms_xplan.display)
      2  .
    SQL>
    SQL> ed
    Wrote file afiedt.buf

      1* select * from ops_flt_leg(dbms_xplan.display)
    SQL> /
    select * from ops_flt_leg(dbms_xplan.display)
                             *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended


    SQL> select * from ops_flt_leg(dbms_xplan.display);
    select * from ops_flt_leg(dbms_xplan.display)
                             *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    Regards.

    Satyaki De.
  • 5. Re: ERROR:ORA-01031: insufficient privileges
    NicloeiW Journeyer
    Currently Being Moderated
    Hi Rob,

    This one is great,

    but regarding reading, still naieve ;-) at it,

    can you give me one small example,
  • 6. Re: ERROR:ORA-01031: insufficient privileges
    RobvanWijk Oracle ACE
    Currently Being Moderated
    You replaced the table keyword with your table name. If you skip that and just type literally what I told, after doing an explain plan, it'll be ok.

    Regards,
    Rob.
  • 7. Re: ERROR:ORA-01031: insufficient privileges
    RobvanWijk Oracle ACE
    Currently Being Moderated
    Please post it here, and I'll have a look at it, this evening.

    Regards,
    Rob.
  • 8. Re: ERROR:ORA-01031: insufficient privileges
    Satyaki_De Guru
    Currently Being Moderated
    Oops! Ya you are right. Now, it is coming.

    Regards.

    Satyaki De.
  • 9. Re: ERROR:ORA-01031: insufficient privileges
    566473 Newbie
    Currently Being Moderated
    can you give me one small example,
    explain plan for
    select *
      from emp e, dept d
    where e.deptno = d.deptno
    explained...
    select * from table(dbms_xplan.display)
    Query finished, retrieving results...
                                    PLAN_TABLE_OUTPUT                                
    ----------------------------------------------------------------------------------------
    Plan hash value: 351108634

    ----------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |    14 |   798 |     4   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                |         |    14 |   798 |     4   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL          | EMP     |    14 |   518 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       4 - access("E"."DEPTNO"="D"."DEPTNO")
  • 10. Re: ERROR:ORA-01031: insufficient privileges
    NicloeiW Journeyer
    Currently Being Moderated
    Hi,

    So it reads as 4,3,2,1 and finally select ??

    is my understanding correct ?

    Regards
    Nic
  • 11. Re: ERROR:ORA-01031: insufficient privileges
    RobvanWijk Oracle ACE
    Currently Being Moderated
    Hi,

    So it reads as 4,3,2,1 and finally select ??

    is my understanding correct ?

    Regards
    Nic
    No, it reads as 2, 4, 3, 1 and finally select.

    When two entries are on the same level, the upper one is done first.
    So 2 and 3 are on the same level, 2 is done first. For 3 to be done, 4 has to be done first, then 3, 1 and finally 0.

    Regards,
    Rob.
  • 12. Re: ERROR:ORA-01031: insufficient privileges
    NicloeiW Journeyer
    Currently Being Moderated
    Thanks rob, i am still learning, this,

    do u some complex example, this is much help..thanks
  • 13. Re: ERROR:ORA-01031: insufficient privileges
    John Spencer Oracle ACE
    Currently Being Moderated
    Rob:

    Although I agree with your ordering of the steps in a technical sense, it does not really give the flavour of what is actually happening in the plan given. I'm sure you are aware of what the various operations mean, but for someone less experienced, an understanding of what the various operations are is crucial to deciphering an plan.

    So, as an expansion of Rob's description:

    In some senses, you could argue that the plan steps go 1, 2, 4, 3, 4, 3, 4, 3 ... and finally select (which is not really a plan step, but an identifier for the type of operation being performed.

    Given the query that was explained, the nested loop join "drives" the selection. The first table (step 2) is the one that drives the loop, and the second table (step 4) is the one that is searched for the join. Step 3 indicates that the optimizer will be using an index to find the matching row in the lookup table. So, in psuedo code, a nested loop join is equivalent to:
    FOR emprec IN (SELECT * FROM emp) LOOP
       BEGIN
          SELECT * INTO deptrec
          FROM dept
          WHERE deptno = emprec.deptno;
      
          out_rec := emprec||dept_rec;
          OUTPUT out_rec;
       EXCEPTION WHEN NO_DATA_FOUND THEN
          NULL;
       END;
    END LOOP;
    So, the full table scan of emp happens in pieces, not all as a single step. and for each row retrieved from emp, steps 4 and 3 are done to read a row from dept.

    Note that this is true only for a nested loop join, other join types would process differently, and a full scan might be done completetly before another step was performed.

    John
  • 14. Re: ERROR:ORA-01031: insufficient privileges
    RobvanWijk Oracle ACE
    Currently Being Moderated
    John,

    Thanks for adding the details of a nested loop join. It will surely help the original poster deciphering the plan.

    Although I knew this, most of the time, just knowing the access method (full table scan / table access by rowid / index range scan, et cetera) combined with cardinality numbers and predicate information are sufficient for me to solve performance problems.

    But, as already said, this is good for better understanding how a nested loop works.

    Regards,
    Rob.
1 2 Previous Next