Results 1 to 8 of 8

Thread: tkprof

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: tkprof

    So I'm learning how to create extended trace files, and I'm running into a problem. When I try to include explain plans in the tkprof output, using:

    Code:
    tkprof xxxx.trc sql.out sys=no explain=username/password
    I get the following as a part of my sql.out file:

    Code:
    Error in CREATE TABLE of EXPLAIN PLAN table: FORBESC.prof$plan_table
    ORA-00922: missing or invalid option
    But I do get what appears to be an explain plan
    Code:
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          4  HASH JOIN OUTER (cr=1681 pr=0 pw=0 time=34399 us)
          4   NESTED LOOPS OUTER (cr=51 pr=0 pw=0 time=1252 us)
          4    NESTED LOOPS OUTER (cr=23 pr=0 pw=0 time=758 us)
          4     NESTED LOOPS  (cr=21 pr=0 pw=0 time=687 us)
          4      NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=542 us)
          4       INLIST ITERATOR  (cr=9 pr=0 pw=0 time=435 us)
          4        TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=9 pr=0 pw=0 time=406 us
    )
          4         INDEX RANGE SCAN SITUS_HOME_FK_I (cr=4 pr=0 pw=0 time=155 us)(ob
    ject id 152410)
          0       INDEX UNIQUE SCAN BUS_PK (cr=6 pr=0 pw=0 time=84 us)(object id 152
    486)
          4      INDEX UNIQUE SCAN HOME_PK (cr=6 pr=0 pw=0 time=122 us)(object id 15
    2419)
          3     INDEX UNIQUE SCAN CCON_PK (cr=2 pr=0 pw=0 time=43 us)(object id 1524
    26)
          2    VIEW  (cr=28 pr=0 pw=0 time=476 us)
          2     UNION ALL PUSHED PREDICATE  (cr=28 pr=0 pw=0 time=452 us)
          2      TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=12 pr=0 pw=0 time=205 us)
          4       INDEX UNIQUE SCAN SITUS_PK (cr=8 pr=0 pw=0 time=149 us)(object id 
    152414)
          0      NESTED LOOPS ANTI (cr=16 pr=0 pw=0 time=173 us)
          1       TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=12 pr=0 pw=0 time=83 us)
          4        INDEX UNIQUE SCAN SITUS_PK (cr=8 pr=0 pw=0 time=40 us)(object id 
    152414)
          1       TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=4 pr=0 pw=0 time=68 us)
          2        INDEX RANGE SCAN SITUS_HOME_FK_I (cr=2 pr=0 pw=0 time=20 us)(obje
    ct id 152410)
         44   VIEW  (cr=1630 pr=0 pw=0 time=31838 us)
         44    SORT UNIQUE (cr=1630 pr=0 pw=0 time=31743 us)
         48     TABLE ACCESS FULL LOS_HOME_ISSUE (cr=1630 pr=0 pw=0 time=31481 us)
    Is there something going on that I'm not getting?

    Thanks,
    Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL>@${ORACLE_HOME}/rdbms/admin/utlxplan.sql
    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.

  3. #3
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    Is this 10G?

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    10g correct. I did run the utlxplan.sql, and that created the PLAN_TABLE in my schema.

    But the error message refers to 'FORBESC.prof$plan_table'.

    I don't know if this matters.
    -cf

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Is this helpful?
    If you use the EXPLAIN parameter without the TABLE parameter, TKPROF uses the table PROF$PLAN_TABLE in the schema of the user specified by the EXPLAIN parameter.
    Either specify TKPROF with the TABLE option, or create a PROF$PLAN_TABLE in the schema with the same definition as PLAN_TABLE.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I tried creating "PROF$PLAN_TABLE" and "prof$plan_table", but I didn't have success until I specified a table name on the command line, as suggested.

    So, when I got the error, the output displayed
    Code:
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          4  HASH JOIN OUTER (cr=1681 pr=0 pw=0 time=34399 us)
          4   NESTED LOOPS OUTER (cr=51 pr=0 pw=0 time=1252 us)
          4    NESTED LOOPS OUTER (cr=23 pr=0 pw=0 time=758 us)
          4     NESTED LOOPS  (cr=21 pr=0 pw=0 time=687 us)
          4      NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=542 us)
          4       INLIST ITERATOR  (cr=9 pr=0 pw=0 time=435 us)
          4        TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=9 pr=0 pw=0 time=406 us)
          4         INDEX RANGE SCAN SITUS_HOME_FK_I (cr=4 pr=0 pw=0 time=155 us)(object id
     152410)
          0       INDEX UNIQUE SCAN BUS_PK (cr=6 pr=0 pw=0 time=84 us)(object id 152486)
          4      INDEX UNIQUE SCAN HOME_PK (cr=6 pr=0 pw=0 time=122 us)(object id 152419)
          3     INDEX UNIQUE SCAN CCON_PK (cr=2 pr=0 pw=0 time=43 us)(object id 152426)
          2    VIEW  (cr=28 pr=0 pw=0 time=476 us)
          2     UNION ALL PUSHED PREDICATE  (cr=28 pr=0 pw=0 time=452 us)
          2      TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=12 pr=0 pw=0 time=205 us)
          4       INDEX UNIQUE SCAN SITUS_PK (cr=8 pr=0 pw=0 time=149 us)(object id 152414)
          0      NESTED LOOPS ANTI (cr=16 pr=0 pw=0 time=173 us)
          1       TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=12 pr=0 pw=0 time=83 us)
          4        INDEX UNIQUE SCAN SITUS_PK (cr=8 pr=0 pw=0 time=40 us)(object id 152414)
          1       TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=4 pr=0 pw=0 time=68 us)
          2        INDEX RANGE SCAN SITUS_HOME_FK_I (cr=2 pr=0 pw=0 time=20 us)(object id 1
    52410)
         44   VIEW  (cr=1630 pr=0 pw=0 time=31838 us)
         44    SORT UNIQUE (cr=1630 pr=0 pw=0 time=31743 us)
         48     TABLE ACCESS FULL LOS_HOME_ISSUE (cr=1630 pr=0 pw=0 time=31481 us)
    but without the error I got double the explain plan output, which looks like a re-itteration, with the second copy displaying actually less dignostic information. Is there something I'm missing in that addt'l info?

    Code:
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 64  (FORBESC)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          4  HASH JOIN OUTER (cr=1681 pr=0 pw=0 time=34399 us)
          4   NESTED LOOPS OUTER (cr=51 pr=0 pw=0 time=1252 us)
          4    NESTED LOOPS OUTER (cr=23 pr=0 pw=0 time=758 us)
          4     NESTED LOOPS  (cr=21 pr=0 pw=0 time=687 us)
          4      NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=542 us)
          4       INLIST ITERATOR  (cr=9 pr=0 pw=0 time=435 us)
          4        TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=9 pr=0 pw=0 time=406 us)
          4         INDEX RANGE SCAN SITUS_HOME_FK_I (cr=4 pr=0 pw=0 time=155 us)(object id
     152410)
          0       INDEX UNIQUE SCAN BUS_PK (cr=6 pr=0 pw=0 time=84 us)(object id 152486)
          4      INDEX UNIQUE SCAN HOME_PK (cr=6 pr=0 pw=0 time=122 us)(object id 152419)
          3     INDEX UNIQUE SCAN CCON_PK (cr=2 pr=0 pw=0 time=43 us)(object id 152426)
          2    VIEW  (cr=28 pr=0 pw=0 time=476 us)
          2     UNION ALL PUSHED PREDICATE  (cr=28 pr=0 pw=0 time=452 us)
          2      TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=12 pr=0 pw=0 time=205 us)
          4       INDEX UNIQUE SCAN SITUS_PK (cr=8 pr=0 pw=0 time=149 us)(object id 152414)
          0      NESTED LOOPS ANTI (cr=16 pr=0 pw=0 time=173 us)
          1       TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=12 pr=0 pw=0 time=83 us)
          4        INDEX UNIQUE SCAN SITUS_PK (cr=8 pr=0 pw=0 time=40 us)(object id 152414)
          1       TABLE ACCESS BY INDEX ROWID LOS_SITUS (cr=4 pr=0 pw=0 time=68 us)
          2        INDEX RANGE SCAN SITUS_HOME_FK_I (cr=2 pr=0 pw=0 time=20 us)(object id 1
    52410)
         44   VIEW  (cr=1630 pr=0 pw=0 time=31838 us)
         44    SORT UNIQUE (cr=1630 pr=0 pw=0 time=31743 us)
         48     TABLE ACCESS FULL LOS_HOME_ISSUE (cr=1630 pr=0 pw=0 time=31481 us)
    
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   MODE: ALL_ROWS
          4   HASH JOIN (OUTER)
          4    NESTED LOOPS (OUTER)
          4     NESTED LOOPS (OUTER)
          4      NESTED LOOPS
          4       NESTED LOOPS (OUTER)
          4        INLIST ITERATOR
          4         TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                        'LOS_SITUS' (TABLE)
          4          INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                         'SITUS_HOME_FK_I' (INDEX)
          0        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'BUS_PK' 
                       (INDEX (UNIQUE))
          4       INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'HOME_PK' (INDEX 
                      (UNIQUE))
          3      INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'CCON_PK' (INDEX 
                     (UNIQUE))
          2     VIEW
          2      UNION ALL PUSHED PREDICATE
          2       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                      'LOS_SITUS' (TABLE)
          4        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'SITUS_PK' 
                       (INDEX (UNIQUE))
          0       NESTED LOOPS (ANTI)
          1        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                       'LOS_SITUS' (TABLE)
          4         INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'SITUS_PK' 
                        (INDEX (UNIQUE))
          1        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                       'LOS_SITUS' (TABLE)
          2         INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                        'SITUS_HOME_FK_I' (INDEX)
         44    VIEW
         44     SORT (UNIQUE)
         48      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'LOS_HOME_ISSUE' 
                     (TABLE)
    -Chuck

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Chuck,
    If you are getting double information then delete from prof$plan_table
    prior to running the trace ...

    HTH
    Gregg

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Sorry, it's not double the identical information, but rather oen version shows more information per line:

    Code:
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          4  HASH JOIN OUTER (cr=1681 pr=0 pw=0 time=34399 us)
    ...
    vs
    Code:
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   MODE: ALL_ROWS
          4   HASH JOIN (OUTER)
    ...

Posting Permissions

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