2 Replies Latest reply: Aug 7, 2013 2:53 PM by Jonathan Lewis RSS

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

    k1ng87

      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

          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

            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