Fun with DBMS_Xplan.Display

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.

Advertisements

8 thoughts on “Fun with DBMS_Xplan.Display

  1. I think I found it :

    SQL> CREATE USER DBMS_XPLAN IDENTIFIED BY DBMS_XPLAN ;

    User created.

    SQL> GRANT UNLIMITED TABLESPACE TO DBMS_XPLAN;

    Grant succeeded.

    SQL> CREATE TABLE DBMS_XPLAN.DISPLAY (PLAN_TABLE_OUTPUT VARCHAR2(80));

    SQL> insert into DBMS_XPLAN.DISPLAY values (‘forgot TABLE() again you idiot’);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> grant select on DBMS_XPLAN.DISPLAY TO PUBLIC;

    Grant succeeded.

    SQL> REVOKE UNLIMITED TABLESPACE FROM DBMS_XPLAN;

    Revoke succeeded.

    SQL> conn scott/tiger
    Connected.
    SQL> SELECT * FROM DBMS_XPLAN.DISPLAY;

    PLAN_TABLE_OUTPUT
    ——————————————————————————–
    forgot TABLE() again you idiot

    Et voilà !

      • I think a more appropriate use would be:

        Cover the stupid syntax of a TABLE(…) vith a view that is named DISPLAY – actually giving the user back what he was expecting. And the next time one has to remeber a OCP-certified constant to DISPLAY could this be build into the view shielding the poor users. A futher extension would off-cause be to provide a logging feature that collects poor stupid users misuse – and the politely inform them about a the proper use…

        Yes I know this will only work for the most recent explained plan – but….

        Lars

      • Lars,

        Yes, I have wrapped Select * from Table(DBMS_Xplan.display) in a view in the past. Works pretty well. I seem to remember providing a series of views that passed different parameters to the function, such as to show parallelism etc..

        I like the logging idea.

  2. Pingback: Log Buffer #171: a Carnival of the Vanities for DBAs | The Pythian Blog

  3. Pingback: Bloemlezing 49 « De Kadenzer Courant

  4. A very nice one!

    If the developers at my side at least knew about dbms_xplan.display would be a bless. :(

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s