Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: getting EXPLAIN PLAN in 10gR1

    I've tried several iterations on the following, and I can't get an EXPLAIN PLAN in my trace output:

    Code:
    [oracle@ora4 admin]$ sqlplus forbesc@dev
    SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jan 13 08:07:54 2006
    Copyright (c) 1982, 2004, Oracle.  
    All rights reserved.
    
    Enter password: 
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> drop table forbesc.plan_table;
    Table dropped.
    
    SQL> @utlxplan.sql
    Table created.
    
    SQL> desc forbesc.plan_table 
    Name                                      Null?    Type 
    ----------------------------------------- -------- -------------------------- 
    STATEMENT_ID                                       VARCHAR2(30) 
    PLAN_ID                                            NUMBER 
    TIMESTAMP                                          DATE 
    REMARKS                                            VARCHAR2(4000) 
    OPERATION                                          VARCHAR2(30) 
    OPTIONS                                            VARCHAR2(255) 
    OBJECT_NODE                                        VARCHAR2(128) 
    OBJECT_OWNER                                       VARCHAR2(30) 
    OBJECT_NAME                                        VARCHAR2(30) 
    OBJECT_ALIAS                                       VARCHAR2(65) 
    OBJECT_INSTANCE                                    NUMBER(38) 
    OBJECT_TYPE                                        VARCHAR2(30) 
    OPTIMIZER                                          VARCHAR2(255) 
    SEARCH_COLUMNS                                     NUMBER 
    ID                                                 NUMBER(38) 
    PARENT_ID                                          NUMBER(38) 
    DEPTH                                              NUMBER(38) 
    POSITION                                           NUMBER(38) 
    COST                                               NUMBER(38) 
    CARDINALITY                                        NUMBER(38) 
    BYTES                                              NUMBER(38) 
    OTHER_TAG                                          VARCHAR2(255) 
    PARTITION_START                                    VARCHAR2(255) 
    PARTITION_STOP                                     VARCHAR2(255) 
    PARTITION_ID                                       NUMBER(38) 
    OTHER                                              LONG 
    DISTRIBUTION                                       VARCHAR2(30) 
    CPU_COST                                           NUMBER(38) 
    IO_COST                                            NUMBER(38) 
    TEMP_SPACE                                         NUMBER(38) 
    ACCESS_PREDICATES                                  VARCHAR2(4000) 
    FILTER_PREDICATES                                  VARCHAR2(4000) 
    PROJECTION                                         VARCHAR2(4000) 
    TIME                                               NUMBER(38) 
    QBLOCK_NAME                                        VARCHAR2(30)
    
    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    [oracle@ora4 admin]$ cd /u01/app/oracle/admin/dev/bdump
    [oracle@ora4 bdump]$ tkprof dev_s001_22566.trc chuck_tkprof3.out EXPLAIN=forbesc/******@dev TABLE=forbesc.plan_table
    
    TKPROF: Release 10.1.0.3.0 - Production on Fri Jan 13 08:09:03 2006
    Copyright (c) 1982, 2004, Oracle.  
    All rights reserved.
    
    [oracle@ora4 bdump]$ more chuck_tkprof3.out 
    
    TKPROF: Release 10.1.0.3.0 - Production on Fri Jan 13 08:09:03 2006
    
    Copyright (c) 1982, 2004, Oracle.  
    
    All rights reserved.
    
    Trace file: dev_s001_22566.trc
    Sort options: default
    
    ********************************************************************************
    count    = number of times OCI procedure was executed
    cpu      = cpu time in seconds executing 
    elapsed  = elapsed time in seconds executing
    disk     = number of physical reads of buffers from disk
    query    = number of buffers gotten for consistent read
    current  = number of buffers gotten in current mode (usually for update)
    rows     = number of rows processed by the fetch or execute call
    ********************************************************************************
    
    Error in CREATE TABLE of EXPLAIN PLAN table: forbesc.plan_table
    ORA-00922: missing or invalid option
    
    parse error offset: 1052
    EXPLAIN PLAN option disabled.
    ********************************************************************************
    ...
    Any tips?
    -Chuck

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The only thing I did differently from you is that my first statement in SQL*Plus was

    SQL> ALTER SESSION SET SQL_tRACE = TRUE;

    Tkprof gave a correct output file.

    Now, this is what Tom Kyte uses to get AUTOTRACE work; you could try it and see will it help (unfortunately, I lost the link, but I also guess one could find this page using search option on Asktom. It seems (to me, and I'm really NOT an expert here) that PLUSTRACE role might be missing.
    Quote Originally Posted by Tom Kyte

    Here is what I like to do to get autotrace working:

    cd $oracle_home/rdbms/admin
    log into sqlplus as system
    run SQL> @utlxplan
    run SQL> create public synonym plan_table for plan_table
    run SQL> grant all on plan_table to public
    exit sqlplus and cd $oracle_home/sqlplus/admin
    log into sqlplus as SYS
    run SQL> @plustrce
    run SQL> grant plustrace to public

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I've already got the tracefile, I am now trying to format it using TKPROF - and generate EXPLAIN PLANS into the newly formatted file.

    I found something about the above being a bug in 10gR1 (Metalink 293481.1), but the solution was to create the PLAN_TABLE in my schema. I've done that, but I still am unable to add EXPLAIN PLANs to the output.

    I do have the PLUSTRACE role

    Code:
    SQL> select * from session_roles where role = 'PLUSTRACE';
    
    ROLE
    ------------------------------
    PLUSTRACE
    -Chuck

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suspect the PLAN_TABLE must exist for [EXPLAIN=]forbesc/******@dev
    and NOT for you (local user).

    FWIW - I usually create only a single PLAN_TABLE per instance & grant all to public on it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suspect the PLAN_TABLE must exist for [EXPLAIN=]forbesc/******@dev
    and NOT for you (local user).

    FWIW - I usually create only a single PLAN_TABLE per instance & grant all to public on it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I was logged in as forbesc when I created the table (from above):

    Code:
    [oracle@ora4 admin]$ sqlplus forbesc@dev
    
    SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jan 13 08:07:54 2006
    Copyright (c) 1982, 2004, Oracle.  
    All rights reserved.
    
    Enter password: 
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> drop table forbesc.plan_table;
    Table dropped.
    
    SQL> @utlxplan.sql
    Table created.
    ...
    I can try adding the PLAN_TABLE to the SYS schema and see what happens.

    -Chuck

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    There already was a PLAN_TABLE in SYS, so I granted ALL to PUBLIC, and granted PLUSTRACE to PUBLIC, and the following worked

    Code:
    [oracle@ora4 bdump]$ tkprof dev_s001_22566.trc chuck_tkprof3.out EXPLAIN=forbesc/******@dev TABLE=sys.plan_table
    My next question is (and I feel dumb now that I've gotten here), is what does the explain plan tell me that I didn't have already.

    I originally received

    Code:
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          9  NESTED LOOPS OUTER (cr=453033 pr=9554 pw=0 time=6296934 us)
          9   NESTED LOOPS  (cr=452997 pr=9550 pw=0 time=6275593 us)
      94673    TABLE ACCESS BY INDEX ROWID PA_EMPLOYER (cr=10902 pr=8803 pw=0 time=1708180 us)
     372409     INDEX FULL SCAN PA_EMPLOYER_PK (cr=749 pr=16 pw=0 time=372456 us)(object id 160436)
          9    TABLE ACCESS BY INDEX ROWID PA_NAME (cr=442095 pr=747 pw=0 time=4275618 us)
     207492     INDEX RANGE SCAN PA_NAME_EMPLOYER_NO_IX (cr=284460 pr=742 pw=0 time=1864799 us)(object id 160619)
          9   TABLE ACCESS BY INDEX ROWID PA_MAILING_ADDR (cr=36 pr=4 pw=0 time=21285 us)
          9    INDEX RANGE SCAN PA_MAILING_ADDR_PK (cr=27 pr=4 pw=0 time=21048 us)(object id 160443)
    and now, in addition I'm getting

    Code:
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   MODE: ALL_ROWS
          9   NESTED LOOPS (OUTER)
          9    NESTED LOOPS
      94673     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                    'PA_EMPLOYER' (TABLE)
     372409      INDEX   MODE: ANALYZED (FULL SCAN) OF 'PA_EMPLOYER_PK' 
                     (INDEX (UNIQUE))
          9     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'PA_NAME' 
                    (TABLE)
     207492      INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                     'PA_NAME_EMPLOYER_NO_IX' (INDEX)
          9    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                   'PA_MAILING_ADDR' (TABLE)
          9     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'PA_MAILING_ADDR_PK' 
                    (INDEX (UNIQUE))
    They look the same, formatted differently.

    -Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •