New tool for EXPLAIN plan


Like most DBA's (including myself) you've probably get a hideously long complicated SQL query to producing nicely formatted EXPLAIN PLAN output. The problem with this (as well as using a tool) is that as the PLAN_TABLE changes from release to release you need to update your SQL or hope that the tool you are using is correctly getting the information you need.

Oracle have now delivered a new function DBMS_XPLAN to solve this headache.

SQL> explain plan for
  2  select ename, sal
  3  from emp e , dept d
  4  where e.deptno=d.deptno
  5  and e.sal > 500;

SQL> select * from
  2  table(dbms_xplan.display);

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  NESTED LOOPS        |             |       |       |       |
|*  2 |   TABLE ACCESS FULL  | EMP         |       |       |       |
|*  3 |   INDEX UNIQUE SCAN  | PK_DEPT     |       |       |       |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."SAL">500)
   3 - access("E"."DEPTNO"="D"."DEPTNO")

Note: rule based optimization

Notice that we also get predicate and access patch information. The output is also somewhat "self-formatting", so dependent on the operation, we will get information about parallel or partition execution plan details


--
-- Index datatype mis-match example
--

SQL> explain plan for
  2  select * from T1
  3  where I_COL=10 and I_CHAR_COL=20

SQL> select * from
  2  table(dbms_xplan.display);

-----------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes
-----------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    12
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |    12
|*  2 |   INDEX RANGE SCAN          | T1X_1       |     1 |
-----------------------------------------------------------------
   2 - access("T1"."I_COL"=10)
       filter(TO_NUMBER("T1"."I_CHAR_COL")=20)


--
-- Partition example
--

SQL> explain plan for
  2  select *
  3  from PAR_TABLE
  4  where PCOL < 750

SQL> select * from
  2  table(dbms_xplan.display);

---------------------------------------------...----------------
| Id | Operation                |  Name     |... Pstart| Pstop |
---------------------------------------------...----------------
|  0 | SELECT STATEMENT         |           |...       |       |
|  1 |  PARTITION RANGE ITERATOR|           |...     1 |     2 |
|  2 |   TABLE ACCESS FULL      | PAR_TABLE |...     1 |     2 |
---------------------------------------------...----------------


--
-- Parallel example
--

SQL> explain plan for
  2  select /*+ PARALLEL(p,4) */ 
  3  from PAR_TABLE p
  4  where PCOL < 750

SQL> select * from
  2  table(dbms_xplan.display);

------- ... -------------------------------------------
| Id  | ...  Pstart| Pstop |  TQ  |IN-OUT| PQ Distrib |
------- ... -------------------------------------------
|   0 | ...        |       |      |      |            |
|   1 | ...      1 |     2 |90,00 | PCWP |            |
|*  2 | ...      1 |     2 |90,00 | P->S | QC (RANDOM)|
------- ... -------------------------------------------