AutoTrace in sql Developer

Question

What is the basic difference between EXPLAIN PLAN and AUTOTRACE in SQL Developer. Can anybody please elaborate?

This is the query on which I am trying to get the difference inbetween Autotrace and Explain Plan.

SELECT name, address, latest_in
  FROM ( SELECT name, address, latest_in
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY address DESC) AS rowrank    
         FROM avrajit
       )
  WHERE rowrank <> 1 AND rowrank > 3;

Answer 1

No better explanation than from Tom Kyte...

the plan from autotrace is an explain plan.

there is no statistic regarding hard parse and optimization - not sure where you are going with that, but the optimizer estimates cardinalities and those in turn with other statistics like clustering factor of indexes, blocks in tables and such - are used to derive IO estimations.

the autotrace statistics reported however are FACTS, after executing the statement, not guesses, not part of optimization.

So, autotrace used explain plan to show the plan that probably, likely will be used and autotrace shows the ACTUAL number of resources expended to execute the query.

Full and long thread is here: https://asktom.oracle.com/pls/asktom/asktom.search?tag=difference-of-explain-plan-autotrace-and-tkprof