This discussion is archived
14 Replies Latest reply: Sep 5, 2010 4:23 PM by CharlesHooper RSS

Creating PLAN TABLE

448776 Newbie
Currently Being Moderated
Hi,
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

Raaj
  • 1. Re: Creating PLAN TABLE
    376714 Newbie
    Currently Being Moderated
    Hi,
    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:
    @?/rdbms/admin/utlxplan
    HTH,

    Yoann.
  • 2. Re: Creating PLAN TABLE
    The Human Fly Oracle ACE Director
    Currently Being Moderated
    connect user/password@connect_string

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

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


    Jaffar
  • 3. Re: Creating PLAN TABLE
    448776 Newbie
    Currently Being Moderated
    Hi,
    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

    Raaj
    ======================================
    EXPLAIN PLAN FOR
    select empno,ename from emp
    where deptno=20;
    Rem
    Rem $Header: utlxpls.sql 26-feb-2002.19:49:37 bdagevil Exp $
    Rem
    Rem utlxpls.sql
    Rem
    Rem Copyright (c) 1998, 2002, Oracle Corporation. All rights reserved.
    Rem
    Rem NAME
    Rem utlxpls.sql - UTiLity eXPLain Serial plans
    Rem
    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
    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
    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
    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
    Rem

    set markup html preformat on

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




    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Error: cannot fetch last explain plan

    1 row selected.
  • 4. Re: Creating PLAN TABLE
    376714 Newbie
    Currently Being Moderated
    Hi,

    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;
    
    Explained.
    
    Elapsed: 00:00:00.03
    09:52:07 TEST.SQL>@?/rdbms/admin/utlxpls
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    -------------------------------------------------------------------------
    | 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
    Regards,

    Yoann.
  • 5. Re: Creating PLAN TABLE
    APC Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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;

    or

    set autot trace exp

    execute your query helre ===>

    Jaffar
  • 7. Re: Creating PLAN TABLE
    448776 Newbie
    Currently Being Moderated
    Hi,
    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.

    Regards
    Raaj.K
  • 8. Re: Creating PLAN TABLE
    217938 Newbie
    Currently Being Moderated
    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
    50145 Newbie
    Currently Being Moderated
    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.

    Cheers,
    Dan
  • 10. Re: Creating PLAN TABLE
    448776 Newbie
    Currently Being Moderated
    Hi,
    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.

    Regards
    Raaj
  • 11. Re: Creating PLAN TABLE
    445198 Newbie
    Currently Being Moderated
    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
    217938 Newbie
    Currently Being Moderated
    Hi Raj,

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

    Thanks,
    Radhika.
  • 13. Re: Creating PLAN TABLE
    795894 Newbie
    Currently Being Moderated
    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

    or

    select * from table(dbms_xplan.display) dual

    ur regards
    pavan(dba)
  • 14. Re: Creating PLAN TABLE
    CharlesHooper Expert
    Currently Being Moderated
    Raaj,

    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:
    http://jonathanlewis.wordpress.com/2010/01/25/old-plan_table/

    Maybe it was a trick interview question?

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