Dear Tom,
as far as I've read, Autotrace uses the output of an EXPLAIN PLAN as a part of it's own output. But now I've stumpled upon an example where they differ, at least when using the appropriate buttons in SQL Developer. I hope it's not just some special behaviour of SQL developer but rather a database mechanic which we can learn something from.
The following setting:
CREATE TABLE SHIPMENT (SNO NUMBER, PNO VARCHAR2(20) NOT NULL);
CREATE TABLE SUPPLIER (SNO NUMBER NOT NULL, SNAME VARCHAR2(20));
INSERT INTO SHIPMENT (SNO, PNO) VALUES(1, 'P1');
INSERT INTO SHIPMENT (SNO, PNO) VALUES(2, 'P2');
INSERT INTO SUPPLIER (SNO, SNAME) VALUES(1, 'S1');
INSERT INTO SUPPLIER (SNO, SNAME) VALUES(2, 'S2');
SELECT sname
FROM supplier
WHERE sno NOT IN (
SELECT sno
FROM shipment
WHERE pno='P1'
)
In either case, the execution plan looks like:
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 12 | 8 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| SUPPLIER | 3 | 18 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| SHIPMENT | 1 | 6 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
But the filter in operation 1 reads for the EXPLAIN PLAN-Button:
NOT EXISTS (SELECT /*+ */ 0 FROM "SHIPMENT" "SHIPMENT" WHERE "PNO"='P1' AND LNNVL("SNO"<>:B1))) for the Autotrace-Button it is:
IS NULL
and finally, and even more confusing, when using an sqlplus-Shell with SET autotrace traceonly explain and sending the query, I get the same plan as I got from the EXPLAIN PLAN-Button.
Can you help me with this one? Are there more differences between EXPLAIN PLAN and the corresponding part in Autotrace?
Thanks in advance,
~Malte
sqldeveloper, when it "autotraces" uses dbms_xplan.display_cursor - which displays the plan of the last query used in the session - the plan as displayed from v$sql_plan - NOT explain plan.
You can see this in just sqlplus, in this rare case - explain plan is more accurate than v$sql_plan:
ops$tkyte%ORA10GR2> set serveroutput off
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT sname
2 FROM supplier
3 WHERE sno NOT IN (
4 SELECT sno
5 FROM shipment
6 WHERE pno='P1'
7 );
SNAME
--------------------
S2
ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID 1h0vtxmx97w8v, child number 0
-------------------------------------
SELECT sname FROM supplier WHERE sno NOT IN ( SELECT sno FROM
shipment WHERE pno='P1' )
Plan hash value: 1124598503
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| SUPPLIER | 2 | 50 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| SHIPMENT | 1 | 25 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - filter(("PNO"='P1' AND LNNVL("SNO"<>:B1)))
Note
-----
- dynamic sampling used for this statement
26 rows selected.
<b>that is the real true plan used at runtime, it
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from plan_table;
0 rows deleted.
ops$tkyte%ORA10GR2> explain plan for
2 SELECT sname
3 FROM supplier
4 WHERE sno NOT IN (
5 SELECT sno
6 FROM shipment
7 WHERE pno='P1'
8 );
Explained.
ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1124598503
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| SUPPLIER | 2 | 50 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| SHIPMENT | 1 | 25 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SHIPMENT" "SHIPMENT"
WHERE "PNO"='P1' AND LNNVL("SNO"<>:B1)))
3 - filter("PNO"='P1' AND LNNVL("SNO"<>:B1))
Note
-----
- dynamic sampling used for this statement
21 rows selected.
they are both saying the same thing - just in this one strange case - explain plan says it better.