14 Replies Latest reply: Sep 5, 2010 6:23 PM by Charles Hooper RSS

    Creating PLAN TABLE

      I am Rajkumar.I faced some interview questions in TKPROF and EXPLAIN PLAN.I am a fresher in Oracle DBA.Could any one explain me about Creating PLAN TABLE.
      1)Where to create the PLAN TABLE?
      2)How to Create the table.?
      I 've tried to create plan table as per documentation

      SQL>connect hr/pwd
      SQL>******path for utlxplan.sql******.
      It's not working
      3)How to execute the script.?
      4)Where to execute the script?

      Thanks in Advance

        • 1. Re: Creating PLAN TABLE
          1)Where to create the PLAN TABLE?
          The table must exist in the schema of the user who issue the EXPLAIN PLAN FOR.
          2)How to Create the table.?
          Connect to sqlplus with the user who'll own the PLAN_TABLE table and issue:

          • 2. Re: Creating PLAN TABLE
            The Human Fly
            connect user/password@connect_string

            sql> @D:\oracle\ora92\rdbms\admin\utlxplan.sql

            you have to use @ symbole to run the sql script.

            • 3. Re: Creating PLAN TABLE
              I have created plan table and executed the following script.It shows me some errors.I have created plan table in scott and sys.

              Help me

              EXPLAIN PLAN FOR
              select empno,ename from emp
              where deptno=20;
              Rem $Header: utlxpls.sql 26-feb-2002.19:49:37 bdagevil Exp $
              Rem utlxpls.sql
              Rem Copyright (c) 1998, 2002, Oracle Corporation. All rights reserved.
              Rem NAME
              Rem utlxpls.sql - UTiLity eXPLain Serial plans
              Rem DESCRIPTION
              Rem script utility to display the explain plan of the last explain plan
              Rem     command. Do not display information related to Parallel Query
              Rem NOTES
              Rem Assume that the PLAN_TABLE table has been created. The script
              Rem     utlxplan.sql should be used to create that table
              Rem With SQL*plus, it is recomended to set linesize and pagesize before
              Rem running this script. For example:
              Rem     set linesize 100
              Rem     set pagesize 0
              Rem MODIFIED (MM/DD/YY)
              Rem bdagevil 02/26/02 - cast arguments
              Rem bdagevil 01/23/02 - rewrite with new dbms_xplan package
              Rem bdagevil 04/05/01 - include CPU cost
              Rem bdagevil 02/27/01 - increase Name column
              Rem jihuang 06/14/00 - change order by to order siblings by.
              Rem jihuang 05/10/00 - include plan info for recursive SQL in LE row source
              Rem bdagevil 01/05/00 - add order-by to make it deterministic
              Rem kquinn 06/28/99 - 901272: Add missing semicolon
              Rem bdagevil 05/07/98 - Explain plan script for serial plans
              Rem bdagevil 05/07/98 - Created

              set markup html preformat on

              Rem Use the display table function from the dbms_xplan package to display the last
              Rem explain plan. Force serial option for backward compatibility
              select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

              Error: cannot fetch last explain plan

              1 row selected.
              • 4. Re: Creating PLAN TABLE

                I don't know what you did exactly but try doing it like:
                09:51:54 TEST.SQL>EXPLAIN PLAN FOR
                09:52:03   2  SELECT * FROM EMP;
                Elapsed: 00:00:00.03
                09:52:07 TEST.SQL>@?/rdbms/admin/utlxpls
                | Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
                |   0 | SELECT STATEMENT     |             |     1 |    30 |     3  (34)|
                |   1 |  TABLE ACCESS FULL   | EMP         |     1 |    30 |     3  (34)|
                7 rows selected.
                Elapsed: 00:00:02.98

                • 5. Re: Creating PLAN TABLE
                  Error: cannot fetch last explain plan
                  At a guess, the PLAN table is empty because you haven't issued an EXPLAIN PLAN statement.

                  Cheers, APC
                  • 6. Re: Creating PLAN TABLE
                    The Human Fly
                    I have created plan table and executed the following script.
                    I think you have messed-up. Once you create plan_table throu utlxplan.sql, you must use the following to get execution plan.

                    Assuming, you have created plan_table in scott user.

                    connect scott/password.

                    explain plan for select * from emp where deptno =10;

                    to see the explain plan,

                    select * from table (dbms_xplan.display) dual;


                    set autot trace exp

                    execute your query helre ===>

                    • 7. Re: Creating PLAN TABLE
                      Because of my memory problem(think so) i'm unable to create a plan table under HR.It showed me the error that Oracle memory not available,realm does not exist.I have uninstalled Oracle and installed Oracle server again.At present i'm getting it now.Could anyone pls explain how to invoke TKPROF utility.
                      Thanks for your help.

                      • 8. Re: Creating PLAN TABLE
                        Hi Raj,
                        It is a utility used to determine CPU and execution times for SQL statements. First set the timed_statistics=true and alter the session for sql_trace =true using alter session command. The trace file is generated in the UDUMP directory. once the trace file is generated, you run the TKPROF tool against the trace file and then look at the output from the TKPROF tool at O/S level.
                        The sql trace requested will be dumped into the destination mentioned as user
                        dumpdest in the parameter file.

                        $tkprof ora_<Server processid>.trc <outputfilename>
                        • 9. Re: Creating PLAN TABLE
                          If you are running 10g, the PLAN_TABLE is automatically created as a global temporary table under the sys schema. This means that once you disconnect your session, the rows in the plan_table are removed.

                          This is not documented (at least in the first version of the docs).

                          I found this out while working with a project that wanted to get explain plans for quite a few queries that they could keep as part of their change management. I 'explained' them and let them know that they were in the plan_table. Imagine my surprise when the called me to tell me that they were not in the table. So I ran the script again, sure enough, they could not see them. It took some looking around before I found the call to create the plan_table as a gtt. I was a little irritated..but at least the project team did not think I was crazy anymore.

                          • 10. Re: Creating PLAN TABLE
                            Currently i am using Oracle9.2 version.I have created a TKPROF TABLE under a schema.I have generated a trace file in OS prompt.And it worked succesfully.While quering the tkprof table in sql*plus it shows me "no rows selected".
                            I have done this with oracle9i documentation.Help me.

                            • 11. Re: Creating PLAN TABLE
                              ave created a TKPROF TABLE under a schema.I have
                              You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file.
                              • 12. Re: Creating PLAN TABLE
                                Hi Raj,

                                As Akshay said, you can see the results in the readable out file.

                                • 13. Re: Creating PLAN TABLE
                                  we should remeber that
                                  a plan table gets trucated prior to generate another execution plan
                                  and moreover to see the execution plan first run
                                  @\rdbms\admin\utlxplan.sql what ever the address it depends on your operating system. after running it you can write
                                  explain plan for
                                  select ...command
                                  then you will have to run utlxplp.sql OR utlxpls.sql(depending on whether parallel query statistics are required)
                                  these scripts show the most commonly selected columns of plan_table
                                  to view the plan table
                                  set autotrace on exp


                                  select * from table(dbms_xplan.display) dual

                                  ur regards
                                  • 14. Re: Creating PLAN TABLE
                                    Charles Hooper

                                    As mentioned by Dan Fink, if you are using a recent release of Oracle you do not need to manually create a PLAN_TABLE. The following article by Jonathan Lewis mentions that Oracle 9i eliminated the need to manually create the PLAN_TABLE:

                                    Maybe it was a trick interview question?

                                    Charles Hooper
                                    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                                    IT Manager/Oracle DBA
                                    K&M Machine-Fabricating, Inc.