This discussion is archived
2 Replies Latest reply: Aug 7, 2013 12:53 PM by Jonathan Lewis RSS

how do I paste a properly formatted explain plan from sqlplus here

k1ng87 Newbie
Currently Being Moderated

When I run an explain plan in sql, the formatting is good and when I paste it in notepad it is still good, but when I try to paste it in here, the formatting gets all screwed up. How do I properly do this?

  • 1. Re: how do I paste a properly formatted explain plan from sqlplus here
    jgarry Guru
    Currently Being Moderated

    There's a code formatting, but it doesn't seem to be sorted out yet.  See Re: preserving code formatting

  • 2. Re: how do I paste a properly formatted explain plan from sqlplus here
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    My approach - from SQL*PLus set up some suitable formatting options.

    In the forum, copy the text in, switch to the advanced editor (the link is just above the top right hand corner of the edit pane) just before posting, highlight the plan, select COURIER NEW as the font.

     

    SQL> set linesize 255
    SQL> set trimspool on
    SQL> set pagesize 90
    SQL> set autotrace traceonly explain
    SQL> select * from dba_users;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2540003333

    ------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                         |    62 | 14198 |    21   (5)| 00:00:01 |
    |   1 |  NESTED LOOPS              |                         |    62 | 14198 |    21   (5)| 00:00:01 |
    |*  2 |   HASH JOIN                |                         |    21 |  4410 |    17   (6)| 00:00:01 |
    |*  3 |    HASH JOIN               |                         |    21 |  4179 |    14   (8)| 00:00:01 |
    |*  4 |     HASH JOIN OUTER        |                         |    21 |  3948 |    10   (0)| 00:00:01 |
    |*  5 |      HASH JOIN             |                         |    21 |  3150 |     8   (0)| 00:00:01 |
    |*  6 |       HASH JOIN            |                         |    21 |  2940 |     6   (0)| 00:00:01 |
    |   7 |        MERGE JOIN CARTESIAN|                         |     1 |    26 |     4   (0)| 00:00:01 |
    |*  8 |         TABLE ACCESS FULL  | PROFILE$                |     1 |    13 |     2   (0)| 00:00:01 |
    |   9 |         BUFFER SORT        |                         |     1 |    13 |     2   (0)| 00:00:01 |
    |* 10 |          TABLE ACCESS FULL | PROFILE$                |     1 |    13 |     2   (0)| 00:00:01 |
    |* 11 |        TABLE ACCESS FULL   | USER$                   |    37 |  4218 |     2   (0)| 00:00:01 |
    |  12 |       TABLE ACCESS FULL    | PROFNAME$               |     1 |    10 |     2   (0)| 00:00:01 |
    |* 13 |      TABLE ACCESS FULL     | RESOURCE_GROUP_MAPPING$ |     1 |    38 |     2   (0)| 00:00:01 |
    |  14 |     TABLE ACCESS FULL      | TS$                     |     8 |    88 |     3   (0)| 00:00:01 |
    |  15 |    TABLE ACCESS FULL       | TS$                     |     8 |    88 |     3   (0)| 00:00:01 |
    |* 16 |   TABLE ACCESS FULL        | USER_ASTATUS_MAP        |     3 |    57 |     0   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       2 - access("U"."TEMPTS#"="TTS"."TS#")
       3 - access("U"."DATATS#"="DTS"."TS#")
       4 - access("CGM"."VALUE"(+)="U"."NAME")
       5 - access("U"."RESOURCE$"="P"."PROFILE#")
       6 - access("U"."RESOURCE$"="PR"."PROFILE#")
       8 - filter("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0)
      10 - filter("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1)
      11 - filter("U"."TYPE#"=1)
      13 - filter("CGM"."ATTRIBUTE"(+)='ORACLE_USER' AND "CGM"."STATUS"(+)='ACTIVE')
      16 - filter("U"."ASTATUS"="M"."STATUS#" OR "U"."ASTATUS"="M"."STATUS#"+16-BITAND("M"."STATUS
                  #",16))

    Note
    -----
       - this is an adaptive plan

     

    Regards

    Jonathan Lewis

    http://jonathanlewis.wordpress.com

    Now on Twitter: @jloracle

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points