CODEWITHSUNDEEP
oracle-sqldevelopersql-execution-plan
oramas
oramas

Reputation: 911

Different Explain Plans in SQL Developer vs Explain Plan for command

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 enter image description here

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 enter image description here

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.

Upvotes: 1

Views: 4911

Answers (2)

BobC
BobC

Reputation: 4416

What you are seeing is the artifact of the adaptive plan; you are actually seeing both versions of the plan in the output. The greyed out lines are the version of the plan that were not executed.

Upvotes: 1

thatjeffsmith
thatjeffsmith

Reputation: 22427

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

Explain Plan enter image description here

Cached Plan enter image description here

DBMS_XPLAN enter image description here

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,.

Upvotes: 3

Related Questions