Daily Archives: 2009-11-27

Fun with DBMS_Xplan.Display

Posted on by

Database Administrators,

Why not amuse and insult your developers at the same time by setting up the following situation:

explain plan for select * from dual;

 

explain plan succeeded.

 

select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

 

8 rows selected

 

select * from dbms_xplan.display;

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
forgot TABLE() again you idiot

 

1 rows selected

I’m sure that readers will be able to work out just how this miracle of modern science comes about.