It's neat and useful

Things change rapidly.
In world of Oracle Database technologies things change so rapidly so it's hardly possible to keep track of them all. But I believe that one should learn something new about Oracle each day - it's the only way to become successful developer or DBA.

Recently, I was reading and enjoying new Jonathan's book, an outstanding work of art for everyone who wants to explore Oracle CBO in detail. While reading it, I noticed small but pretty fascinating remark about a feature I never saw before.

How many ways to obtain statement execution plan do you know? I'm not going to discuss them all, but I think that EXPLAIN PLAN statement and AUTOTRACE feature are most widely used. For sure, they are simple and convenient but one should be careful when using them, because they can be misleading to some extent. The most important thing to remember is that EXPLAIN PLAN is always doing hard parse. Therefore, for example, side-effects of bind variable peeking will be probably missed.

And if we want to look at "real" execution plan, which opportunities do we have? I think these are most familiar ones:
1. Using SQL_TRACE and TKPROF, and then analyzing produced trace files.
2. Querying V$SQL and V$SQL_PLAN dynamic views.

Both options are good, but they require some extra efforts. Well, maybe I'm too lazy, but from Jonathan's book I knew exactly what I was dreaming about. In first release of Oracle 10g, several important enhancements were made to DBMS_XPLAN package, and in particular, new function DISPLAY_CURSOR was added.

It's nice, and easy to use:

SQL> show release
release 1001000200
SQL> 
SQL> create table test(x primary key, y) as
  2  select rownum, rpad('*', 100, '*')
  3    from dual
  4  connect by level <= 1000;
 
Table created.
 
SQL> begin
  2    dbms_stats.gather_table_stats
  3    (ownname => user,
  4     tabname => 'TEST',
  5     estimate_percent => null,
  6     cascade => true);
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> var x number
SQL> 
SQL> exec :x := 1000
 
PL/SQL procedure successfully completed.
 
SQL> explain plan for
  2  select * from test
  3   where x < :x;
 
Explained.
 
SQL> -- Now, EXPLAIN PLAN tells us
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------
Plan hash value: 633647876
 
------------------------------------------------------------ ...
| Id  | Operation                   | Name        | Rows  |  ...
------------------------------------------------------------ ...
|   0 | SELECT STATEMENT            |             |    50 |  ...
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |    50 |  ...
|*  2 |   INDEX RANGE SCAN          | SYS_C005782 |     9 |  ...
------------------------------------------------------------ ...
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("X"<TO_NUMBER(:X))
 
14 rows selected.
 
SQL> -- Ok, let's run it...
SQL> select * from test
  2   where x < :x;
 
         X Y
---------- -------------------------------------------------- ...
         1 ************************************************** ...
         2 ************************************************** ...
       ... ...
       ... ...
       999 ************************************************** ...
 
999 rows selected.
 
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID  5r7q3102zwvh3, child number 0
-------------------------------------
select * from test  where x < :x
 
Plan hash value: 3836831076
 
------------------------------------------------------------- ...
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU ...
------------------------------------------------------------- ...
|   0 | SELECT STATEMENT  |      |       |       |     6 (100 ...
|*  1 |  TABLE ACCESS FULL| TEST |   999 |   101K|     6   (0 ...
------------------------------------------------------------- ...
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("X"<:X)
 
  
18 rows selected.

However, one thing to remember: you should disable SERVEROUTPUT before using this feature or else you'll be slightly surprised:

SQL> set SERVEROUTPUT ON
SQL> 
SQL> select * from test
  2   where x < :x;
 
         X Y
---------- -------------------------------------------------- ...
         1 ************************************************** ...
         2 ************************************************** ...
       ... ...
       ... ...
       999 ************************************************** ...
 
999 rows selected.
 
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------- ...
SQL_ID  9babjv8yq8ru3, child number 0
 
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
 
NOTE: cannot fetch plan for SQL_ID 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify SQL_ID and CHILD_NUMBER;  maybe the plan is
      no longer available in V$SQL_PLAN
 
 
 
8 rows selected.

Neat feature. I liked it.

Andrew.