10 Replies Latest reply: Feb 5, 2014 5:07 AM by Dom Brooks RSS

SQL Developer , does it show actual execution plan or estimated one?

orausern Explorer
Currently Being Moderated

Hi,

 

We are on Oracle 11.2.0.3 on Linux. I am development DBA and we have team of J2EE developers. Our developers are using SQL Deverloper , the free tool by Oracle. I allways use command line: DBMS_XPLAN.DISPLAY_CURSOR. to verify query plans.

There is an option of execution plan in SQL Developer, but I dont know what it uses behind the scene - does it show estimated plan or the actual plan. I think it is best to allways see the actual plan and not the estimated one (as a best practice).

Can someone help to clarify whether sql developer shows the actual plan or the estimated one?

Thansk,

OrauserN

  • 1. Re: SQL Developer , does it show actual execution plan or estimated one?
    Harmandeep Singh Journeyer
    Currently Being Moderated

    SQL Developer gives the estimated plan (dbms_xplan.display) not actual runtime execution plan (dbms_xplan.display_cursor)

     

    Thanks,

    Harman

  • 2. Re: SQL Developer , does it show actual execution plan or estimated one?
    orausern Explorer
    Currently Being Moderated

    Thanks Harmandeep. I think the best practice is to use DBMS_XPLAN.DISPLAY_CURSOR. So shouldn't that be used allways , since DBMS_XPLAN.DISPLAY shows estimated plan and not the real/actual play. And so that may be actually mieleading. Shouldn't Oracle be providing some way in SQL Developer (as it is their own tool) so that the actual plan could be queried?

     

    Thansk,

    OrauserN

  • 3. Re: SQL Developer , does it show actual execution plan or estimated one?
    Dom Brooks Guru
    Currently Being Moderated

    SQL Developer 4 can show the actual execution plan.

    See the third button along next to "Run Script" Button.

    It has a drop down to allow you to pick the actual plan from a specific child cursor.

     

    I've been meaning to investigate further but this recursive lookup that SQL Developer does to look up the prev_sql_id for the session can interfere with your explicit call to DBMS_XPLAN - which itself references that prev_sql_id from v$session - because the prev_sql_id by that time is of the recursive lookup.

    But in my limited experience with SQL Dev 4, this behaviour is not 100% consistent and I've not investigated.

    For me for some of many scripts which run:

    1. alter session set statistics_level = all

    2. run sql

    3 call dbms_xplan.display_cursor

    Some always work fine, some get affected by the behaviour described.

  • 4. Re: SQL Developer , does it show actual execution plan or estimated one?
    rp0428 Guru
    Currently Being Moderated

    Dom is correct (and Harmandeep incorrect) - SQL Developer can show the actual plan.

    I allways use command line: DBMS_XPLAN.DISPLAY_CURSOR. to verify query plans.

    You can also have your developers just use that same command in sql developer also. That will allow them to specify the parameters they want to have control over just what plan elements will display (just as you can).

     

    See this recent thread from the SQL developer forum.
    https://community.oracle.com/message/11330215#11330215

    after you run a query, we run the v$sql query to get the sqlid for cached execution plans - a new feature in v4

    That was Jeff Smith's abbreviated answer. Read the entire thread for the details.

     

    Post any followup regarding sql developer in that other forum.

  • 5. Re: SQL Developer , does it show actual execution plan or estimated one?
    Dom Brooks Guru
    Currently Being Moderated

    The behaviour as described by Jeff is not 100% consistent (in my experience)

    I don't know why, I haven't investigated any further, but I have some sets of scripts which work and some which don't, even when run as script.

    I like the fact that SQL Developer is getting more mature.

    I don't like the fact that I potentially have to do different things with my scripts depending on what tool I'm using.

    Unfortunately I haven't had the time/motivation to come up with some clear and isolated examples of the inconsistent behaviour to send to Jeff for feedback.

    It's a bit like SQLDeveloper 3 and timestamp columns - some queries did recursive lookups on nls setting views/tables, some didn't.

  • 6. Re: SQL Developer , does it show actual execution plan or estimated one?
    orausern Explorer
    Currently Being Moderated

    Hi Dom,

     

    Thanks again! What is your advice for me to tell my developers? Do you think we can use sql developer 4 and get the output of dbms_xplan.display_cursor()); - I am not very techy on this and not following all the detailed discussions here!

    Regards

    OrauserN

  • 7. Re: SQL Developer , does it show actual execution plan or estimated one?
    jgarry Guru
    Currently Being Moderated

    I think the lesson is: Don't be dependent on one tool.  In this case, in my opinion sqldeveloper is perfectly fine as a first approximation.  Since Oracle can make and promote different plans in production anyways, whatever developers come up with may not be the final say.

     

    I think a lot of people may have problems with that kind of attitude, since they are still thinking in rule-based terms.

  • 8. Re: SQL Developer , does it show actual execution plan or estimated one?
    rp0428 Guru
    Currently Being Moderated
    The behaviour as described by Jeff is not 100% consistent (in my experience)

    I don't know why, I haven't investigated any further, but I have some sets of scripts which work and some which don't, even when run as script.

    Which 'behaviour' are you referring to? I quoted that particular passage only to support that sql developer 4 DOES support using actual execution plans.

     

    Jeffs reply marked ANSWERED was this:

    select ....

    select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST ALIAS'));

     

    Run this together as a script.

     

    Otherwise if you run

     

    select ...

     

    We immediately then query the DD for the SQLID of that query so we can make the plan available.

    Are you saying that you have used two SELECT queries like that and then executed 'run as a script' and it did NOT produce the same results you get if you do that in sql*plus?

     

    If so - that would be a bug in sql developer.

  • 9. Re: SQL Developer , does it show actual execution plan or estimated one?
    Dom Brooks Guru
    Currently Being Moderated

    RP - That's exactly what I'm saying.

    The recommended process works 95% of the time but not 100%.

    I can't provide you with a standalone test case for SQL Dev 4 to "prove it" because all my scripts which show the problem are application specific and I've not had time/etc to drill down into the whys and the wherefores.

     

    All I can say is that I have loads of such scripts testing app-specific sql statements - I spend large parts of the day doing this.

    In SQL Developer 3, some run fine using "run as script". Some don't. Usually if they don't work as "run as script", then work with "run" on the individual parts (which is a pain for supplying binds) and vice versa.

    In SQL Developer 4, some run fine using "run as script". Some don't. Usually if they don't work as "run as script", then work with "run" and vice versa.

    The ones with the problems in version 3 aren't the same as the ones with problems in version 4.

    If I have a problem in 4 and I run the same script verbatim in version 3, then it usually works as expected.

     

    However, to show the type of problem, there's a particular issue with timestamps in some versions of SQL Developer 3 which highlight a similar type of problem (but not the same problem).

     

    If I run this script (I need to have data in the table) using "run as script":

     

    drop table t1;
    create table t1
    (col1 number
    ,col2 timestamp);
    insert into t1 values (1,systimestamp);
    commit;
    alter session set statistics_level = all;
    select *
    from   t1;
    select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

    Then the output is:

    SQL_ID  0gzt83m5pxufx, child number 0
    -------------------------------------
    /*+ NO_SQL_TRANSLATION */SELECT VALUE FROM V$NLS_PARAMETERS WHERE
    PARAMETER = 'NLS_TIMESTAMP_FORMAT'
    Plan hash value: 1805486652
    -------------------------------------------------------------------------------------
    | Id  | Operation        | Name             | Starts | E-Rows | A-Rows |   A-Time   |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |                  |      1 |        |      1 |00:00:00.01 |
    |*  1 |  FIXED TABLE FULL| X$NLS_PARAMETERS |      1 |      1 |      1 |00:00:00.01 |
    -------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
    ---------------------------------------------------                                                                                                                                                                                                                                                         
       1 - filter(("PARAMETER"='NLS_TIMESTAMP_FORMAT' AND
                  "PARAMETER"<>'NLS_SPECIAL_CHARS' AND "INST_ID"=USERENV('INSTANCE')))

     

    To workaround this particular example, I need to just run each bit individually not run as script..

  • 10. Re: SQL Developer , does it show actual execution plan or estimated one?
    Dom Brooks Guru
    Currently Being Moderated

    Actually, as it happens. I've got a script in front of me right now from yesterday's testing and which ran exactly into this issue.

     

    I can't post it because it's app specific sql.

     

    The script is:

    alter session set statistics_level = all;
    alter session set current_schema = xxx;
    var dt number
    exec :dt := 20140204;
    var ps number
    exec :ps := 436945;
    SELECT ....;
    select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    
    
    

     

    I'm testing the effects of two different ways of writing the same sql statement.

     

    For the original SQL, I f5/"run as script", I get the expected output including the execution plan from the DBMS_XPLAN.

    For the slightly changed SQL, I get the same results from the SQL statement but output from DBMS_XPLAN of:

     

     

    SQL_ID  gypnfv5nzurb0, child number 2

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

    select child_number from v$sql   where sql_id = :1     order by

    child_number

     

    Plan hash value: 4252345203

     

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

    | Id  | Operation                | Name                      | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |

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

    |   0 | SELECT STATEMENT         |                           |      1 |        |      1 |00:00:00.01 |       |       |          |

    |   1 |  SORT ORDER BY           |                           |      1 |      1 |      1 |00:00:00.01 |  2048 |  2048 | 2048  (0)|

    |*  2 |   FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) |      1 |      1 |      1 |00:00:00.01 |       |       |          |

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

     

    Predicate Information (identified by operation id):

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

     

       2 - filter(("KGLOBT03"=:1 AND "INST_ID"=USERENV('INSTANCE')))

     

    For this particular example, it doesn't seem to matter whether I run as script/f5 or run/f9 each bit individually (supplying the binds in the dialogue box).

    The output is the same.

     

    If I execute verbatim in SQL Developer 3, again using "Run as script"/f5, then I get the expected execution plan output.

Legend

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