14 Replies Latest reply on Jan 19, 2018 4:34 PM by Vadim Tropashko-Oracle

    How does SQL Developer do an explain plan?

    John_K

      Sorry for all the recent questions - I'm genuinely trying to move onto SQL Developer "full time" but I keep hitting snags.

      Anyway, I was wondering - what is SQL Developer doing when it does an explain plan? Version 17.4.0.355.

       

      SD.png

       

      Why do I need to commit my prior transaction to do an explain plan?

       

      Same test in sqlplus works fine:

       

      SQL> create table a(b number);
      
      
      Table created.
      
      
      SQL> insert into a values (1);
      
      
      1 row created.
      
      
      SQL> explain plan for select * from dual;
      
      
      Explained.
      
      
      SQL> select * from table(dbms_xplan.display);
      
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
      Plan hash value: 272002086
      
      
      --------------------------------------------------------------------------
      | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
      |   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
      --------------------------------------------------------------------------
      
      
      8 rows selected.
      
      
      SQL> select * from a;
      
      
               B
      ----------
               1
      
      
      SQL> rollback;
      
      
      Rollback complete.
      
      
      SQL> select * from a;
      
      
      no rows selected
      

       

      Also works fine in Toad.

        • 1. Re: How does SQL Developer do an explain plan?
          John_K

          Ah - I have just seen that this is already filed here (F10 doesn't work on sqldeveloper 17.4 ).

           

          Although I don't have any XML columns displayed and my plan table is owned by SYS - so I can't do anything with that.

           

          2018-01-16 16_16_19-Preferences.png

           

            1* select owner, table_name from all_tables where table_name = 'PLAN_TABLE'

          SQL> /

           

           

          OWNER      TABLE_NAME

          ---------- --------------------

          SYS        PLAN_TABLE

          • 2. Re: How does SQL Developer do an explain plan?
            Vadim Tropashko-Oracle

            Even if you exclude Other XML, SQL Dev would still attempt accessing it to figure out rowsource execution order (17.4 functionality). This is a bug which is fixed for 18.1

             

            Having said that, other_xml column has been there since rdbms version 10.2; are you on version earlier than that? If not, then the workaround is dropping or fixing the plan table object in your local schema.

            • 3. Re: How does SQL Developer do an explain plan?
              John_K

              No, I'm using 12.2. I've just started using SQL Developer that's all.

               

              I don't have a plan table in my local schema. That's why I think this might be slightly different to the filed bug (or at least a different manifestation of it). If I do an explain plan for "select * from dual" normally then it works fine - however if I do the same when I have an outstanding commit then I get the above message.

              • 4. Re: How does SQL Developer do an explain plan?
                Vadim Tropashko-Oracle

                I have overlooked the insert command in your script, which apparently caused this. The reason why we ask if you want to commit is because we rollback after the explain plan command and query to clean the plan table. This rollback is something that may nullify your prior transaction.

                • 5. Re: How does SQL Developer do an explain plan?

                  I don't believe sql*plus does that.

                   

                  If the plan table needs 'cleaned' why not just delete the rows in it?

                  • 6. Re: How does SQL Developer do an explain plan?
                    Vadim Tropashko-Oracle

                    This functionality has been introduced in SQLDev 3.x.x

                    I'm not entirely sure, but it seem to be bugfix for the forum issue:

                    30EA4: Invoking Explain Plan Causes Uncommitted Session

                     

                    Now, that behavior has been unchanged since then, and nobody complained. Here is the test case:

                     

                    1. Run the statement

                    Insert Into MyTable values (2);

                     

                    2. F10

                    select 1 from dual;

                     

                    Here the dialog pops out. Please give me one reason why can't you decide if you want to commit (Yes) or not at this point. If you say no then no commit is executed (nor rollback either).

                     

                    There is final rollback at the end of the explain plan command, and I can remove it for 18.1 if everybody insists.

                    • 7. Re: How does SQL Developer do an explain plan?

                      There is final rollback at the end of the explain plan command, and I can remove it for 18.1 if everybody insists.

                      I don't think it is an issue of 'insisting'.

                       

                      It is more an issue of 1) sql plus doesn't do it and I'm not aware of NOT doing it being an issue in sql plus and 2) you say Sql dev DOES do it.

                       

                      IMO it is really a design decision for your team to make as to the degree of compatibility with sql plus you want to maintain.

                       

                      Personally I think asking is appropriate. If a user wants a query explained why did they execute it first and not commit/rollback?

                      • 8. Re: How does SQL Developer do an explain plan?
                        John_K

                        I don't think anything should be doing an implicit rollback on the client side. If the database enforces a rollback (due to an exception etc), or a commit (due to DDL etc) then fair enough, however it shouldn't be the client tool making those decisions when the database doesn't enforce it. If I specifically want to rollback, I'll do that myself in the tool. I can't see what benefit doing a rollback here has anyway, other than the plan table potentially has some old plans in it if not. The plan table by default is a GTT anyway so it's only session persistent.

                        It's not really about explaining the query you're currently executing (i.e. executing it first, then explaining it after) - it becomes a nuisance if you're doing several updates, have run say three statements and then want to explain something before running the remaining ones. Your only option is to either rollback the whole transaction or commit it halfway through. Any sane person would obviously have to opt for the former which is a pain to say the least.

                         

                        I agree it should be a design decision made by the development team. However, I'd ask them to consider that..

                         

                        -) sqlplus doesn't do this.

                        -) Toad doesn't do this.

                        -) sqlcl doesn't do this ( tested in v4.2.0).

                         

                        If the dev team do insist on retaining this functionality, can we at least have it as a preference? "Rollback after explain plan" or similar?

                        • 9. Re: How does SQL Developer do an explain plan?
                          Buntoro

                          I also don't think the neccesity of enforcing commit should happen when the explain plan is going to be invoked.

                          First of all there is SAVEPOINT that we can use prior doing any DML for the explain plan table and ROLLBACK TO SAVEPOINT afterwards.

                          By doing that, IMO any uncommited changes by the end user won't be affected.

                           

                          Regards,

                           

                          Buntoro

                          • 10. Re: How does SQL Developer do an explain plan?
                            thatJeffSmith-Oracle

                            Agreed. This is the solution for the next update. We won't rely on a transaction to clean the plan table. It'll be removed via statement ID.

                            • 11. Re: How does SQL Developer do an explain plan?
                              Buntoro

                              As what Vadim's mention regarding the forum link. It was my old post.

                              Instead of removing via statement ID, why not using transaction capabilities.

                              I still think the best solution is using the savepoint as shown in this link.

                              https://www.tutorialspoint.com/javaexamples/jdbc_rollback.htm

                               

                              However it is up to the developer to choose.

                              • 12. Re: How does SQL Developer do an explain plan?
                                Vadim Tropashko-Oracle

                                As John mentioned with global temporary table why bother deleting from plan_table at all? So, we'll just do the explain plan followed by the query (as it was before version 3.x.x), and all plans are distinguished by statement id.

                                • 13. Re: How does SQL Developer do an explain plan?
                                  John_K

                                  Personally I‘d prefer to keep it in the plan table because there might be occasions when you want to query that directly (for whatever reason) - however if the statement is rremoved automatically after doing an explain plan then I think that would be impossible?

                                  I presume the issue comes when you explain a plan that already exists? In which case, can you not just delete BEFORE INSERT into the plan table (i.e a merge)? For that statement ID only of course. So you never end up with duplicates but you can have any number of plans in your table for a given session?

                                   

                                  Whatever sqlplus does is probably going to be the option most widely accepted I guess.

                                  • 14. Re: How does SQL Developer do an explain plan?
                                    Vadim Tropashko-Oracle

                                    Just to remove possible confusion, if you run automatic explain plan (F10), statement_id is generated as a timestamp. Therefore, plans are readily distinguished even for identical statements.