Different Explain Plans in SQL Developer vs Explain Plan for command

Question

When I get the explain plan of the following query, I see that, both table has full table scan.

SELECT *
  FROM employees e,
       departments d
 WHERE e.employee_id = d.manager_id;

Here is the explain plan I got with the explain plan command. by EXPLAIN PLAN FOR command

https://i.hizliresim.com/JZdB2o.jpg https://hizliresim.com/JZdB2o https://pasteboard.co/HO9ARcl.jpg

But, if I get the explain plan of the same query with SQL developer, I see a significantly different explain plan. Especially, it writes table access full at the buttom but over it, it writes table access by index rowid.

Here is the explain plan I got from the SQL Developer. by SQL Developer Explain Plan button

https://i.hizliresim.com/DYoYbv.jpg https://hizliresim.com/DYoYbv https://pasteboard.co/HO9BxfA.jpg

My question is, could anyone explain the explain plan of the SQL developer step by step? And why SQL Developer and explain plan command generate different explain plans?

Thanks in advance.

thatjeffsmith thatjeffsmith · Accepted Answer · 2018-11-20T15:28:48

There are several different ways to get the plan for a query in SQL Developer.

Explain Plan

Cached Plan

DBMS_XPLAN

Which method are you using? You've cropped your picture such that we can't tell if this is a cached plan from V$SQL_PLAN or an Explain Plan.

Now, onto the crux of your question - do not use Explain Plan. It can be unreliable. It shows you a plan that could be run - it doesn't show you the actual plan that was or will be used.

Also note this in your plan output -

-- this is an adaptive plan

Adaptive Plans in Oracle Database 12c allow runtime changes to execution plans. It generally happens because the statistics are lying to the optimizer. The DB thinks there are 5 rows but when it goes to read them from an index or a table, it finds 50,000 rows instead. So the database goes, to heck with this, we're going to do something else.

So my advice -

Collect statistics on your two tables:

BEGIN
    dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'EMPLOYEES', estimate_percent => 100);
    dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'DEPARTMENTS', estimate_percent => 100);
END;

Then, run your plans again. Except this time, don't use EXPLAIN PLAN FOR - use the 2nd or 3rd option I show above,.

Different Explain Plans in SQL Developer vs Explain Plan for command

2 Answers