4 Replies Latest reply on Nov 18, 2017 12:02 AM by Vadim Tropashko-Oracle

    text explain plan

    Scott Swank

      Is there any way to get a basic text explain plan from SQL Dev? E.g. if I wanted to email my plan or paste it into a forum post.

       

      SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

       

      Thanks.

        • 1. Re: text explain plan

          Is there any way to get a basic text explain plan from SQL Dev? E.g. if I wanted to email my plan or paste it into a forum post.

          What seems to be the problem?

           

          Just use Ctrl-A to select all of the text and then Ctrl-C to copy it to the clipboard and then Ctrl-V to paste it into your email editor.

          • 2. Re: text explain plan
            Gaz in Oz

            explain plan for <your query>;

             

            select * from table(dbms_xplan.display);

             

            Hit F5 (Default "Run Script" key on windows)

            Copy/paste from "Script Output" window. Use a fixed font, say Courier New to format the pasted code.

            If you are going to post

            • 3. Re: text explain plan
              Scott Swank

              I understand how to create a plan, either via "explain plan for ..." or with the /*+ gather_plan_statistics */ hint. I was asking whether this is available as a view of SQL Dev's explain plan, the one you get when you hit F10.

              • 4. Re: text explain plan
                Vadim Tropashko-Oracle

                Sql Developer explain plan/autotrace has only one option of exporting the report: html. Here is how copied-and-pasted html formatted explain plan looks like on this very forum:

                 

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                OPERATIONOBJECT_NAMEOPTIONSCARDINALITYCOSTDISTRIBUTIONOTHER_TAGOBJECT_NODE
                SELECT STATEMENT
                &nbsp&nbsp 1 2 &nbsp&nbsp&nbsp
                PX COORDINATOR
                &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
                PX SEND
                SYS.:TQ10001 QC (ORDER) 1 2 QC (ORDER) PARALLEL_TO_SERIAL :Q1001
                SORT
                &nbsp ORDER BY 1 2 &nbsp PARALLEL_COMBINED_WITH_PARENT :Q1001
                PX RECEIVE
                &nbsp&nbsp&nbsp&nbsp&nbsp PARALLEL_COMBINED_WITH_PARENT :Q1001
                PX SEND
                SYS.:TQ10000 RANGE &nbsp&nbsp RANGE PARALLEL_TO_PARALLEL :Q1000
                NESTED LOOPS
                &nbsp&nbsp&nbsp&nbsp&nbsp PARALLEL_COMBINED_WITH_PARENT :Q1000
                NESTED LOOPS
                &nbsp&nbsp 1 2 &nbsp PARALLEL_COMBINED_WITH_PARENT :Q1000
                PX BLOCK
                &nbsp ITERATOR &nbsp&nbsp&nbsp PARALLEL_COMBINED_WITH_CHILD :Q1000
                TABLE ACCESS
                EMP FULL 1 2 &nbsp PARALLEL_COMBINED_WITH_PARENT :Q1000
                Filter Predicates
                E.ENAME='hermann'
                INDEX
                SCOTT.PK_DEPT UNIQUE SCAN 1 0 &nbsp PARALLEL_COMBINED_WITH_PARENT :Q1000
                Access Predicates
                E.DEPTNO=D.DEPTNO
                TABLE ACCESS
                SCOTT.DEPT BY INDEX ROWID 1 1 &nbsp PARALLEL_COMBINED_WITH_PARENT :Q1000
                Other XML
                {info}
                info type="db_version"
                12.1.0.1
                info type="parse_schema"
                "HR"
                info type="plan_hash"
                1500604541
                info type="plan_hash_2"
                3029530263
                {hint}
                PQ_DISTRIBUTE(@"SEL$1" "D"@"SEL$1" NONE BROADCAST)
                NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")
                USE_NL(@"SEL$1" "D"@"SEL$1")
                LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
                INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
                FULL(@"SEL$1" "E"@"SEL$1")
                OUTLINE_LEAF(@"SEL$1")
                ALL_ROWS
                DB_VERSION('12.1.0.1')
                OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
                IGNORE_OPTIM_EMBEDDED_HINTS

                 

                Please don't hesitate to suggest what kind of amendments in the output html may improve its usability (for example I had to adjust column width %ages to make them appear).

                 

                Just for the reference, here is how this html looks as an individual page in the browser

                export plan.png