1 2 Previous Next 19 Replies Latest reply: May 2, 2007 5:15 AM by Satyaki_De RSS

    ERROR:ORA-01031: insufficient privileges

    NicloeiW
      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
          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
            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
              Rob van Wijk
              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
                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
                  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
                    Rob van Wijk
                    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
                      Rob van Wijk
                      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
                        Oops! Ya you are right. Now, it is coming.

                        Regards.

                        Satyaki De.
                        • 9. Re: ERROR:ORA-01031: insufficient privileges
                          566473
                          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
                            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
                              Rob van Wijk
                              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
                                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
                                  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
                                    Rob van Wijk
                                    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