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.
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à !
Indeed. Isn’t object name resolution fun?
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.
Pingback: Log Buffer #171: a Carnival of the Vanities for DBAs | The Pythian Blog
Pingback: Bloemlezing 49 « De Kadenzer Courant
A very nice one!
If the developers at my side at least knew about dbms_xplan.display would be a bless. :(
A very nice one!
Oracle Tuning