My favourite 11g thing
For too long I’ve muddled my way through using the built-in “explain plan” feature of the IDE-that-shall-remain-nameless provided by the client; but now that they’ve successfully upgraded from 9i to 11g, I’m now in a much better place. No longer will I wonder which table each step in a plan refers to, or what filters are being applied where.
DBMS_XPLAN is the best!
--get plan for a query set pagesize 0 linesize 999 heading off feedback off echo on explain plan for SELECT bla FROM mytable WHERE bla; set echo off select * from table(dbms_xplan.display(format=>'ALL'));
--get plan for cursor in cache set pagesize 0 linesize 999 heading off feedback off select * from table(dbms_xplan.display_cursor( sql_id=>'fnumywukcm2ng', cursor_child_no=> 0, format=>'ALL'));