Forum Stats

  • 3,838,702 Users
  • 2,262,394 Discussions
  • 7,900,739 Comments

Discussions

Run explain plan on full resultset in SQL Developer

User_1871
User_1871 Member Posts: 244 Red Ribbon

A novice question about SQL Developer 18:

When I run the explain plan (either by clicking the button in the toolbar or with dbms_xplan), it seems like the explain plan is only run on a subset of the resultset. The cost is very low.

Whereas, in the SQL query, if I were to force the query to return the full resultset (by using ORDER BY, or something like that) then the cost would much higher. (Sure, sorting is expensive, but I think most of the cost comes from returning the full resultset.)

Is there a way to run the explain plan on the full resultset by default? Or have I misunderstood something?

Thanks.

Best Answer

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,700 Employee

    Explain Plans don't include ANY results.

    You want the AutoTrace - and then make sure in preferences you have 'Fetch all rows' enabled. Note...that'll get expensive obviously for large query results.

    Using 'ORDER BY' changes the query, of course the plan and cost is going to change.

    User_1871

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,700 Employee

    Explain Plans don't include ANY results.

    You want the AutoTrace - and then make sure in preferences you have 'Fetch all rows' enabled. Note...that'll get expensive obviously for large query results.

    Using 'ORDER BY' changes the query, of course the plan and cost is going to change.

    User_1871