Execution Plan of a Subquery inside CASE statement

I found that in our 9.2.0.8 database the execution plan of a subquery inside CASE statement can not be seen in the SQL_TRACE/TKPROF report or from the excution plan extracted from the shared pool. However, it can be seen from Autotrace explain plan. I constructed a test case to demonstrate this as follows:

rem script: xplan_diff.sql
rem   execution plan for the subquery in the CASE statement not shown in SQL_TRACE/TKPROF in 9i
rem
rem

spool xplan_diff.log
set echo on

drop table t;
drop table t2;

create table t 
as 
select rownum id,
       'GOOD' val
from all_objects
where rownum <=20;
 
update t set val='BAD' where mod(id,2) = 0;
commit;

create table t2
as
select rownum id,
       'TEST' val
from all_objects
where rownum <=20; 


alter session set tracefile_identifier = test;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';
set autotrace traceonly
select id,
       case when  val = 'GOOD' then 
  (select t2.val 
  from t, t2
  where t.id = t2.id
    and rownum = 1
         )
       else 'NO TEST'
       END  as IS_TEST
from t;
set autotrace off
alter session set events '10046 trace name context forever, level 12';
spool off
exit;


set doc off
doc

---- 9.2.0.8 Autotrace xplan  ---------------------------

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'T2'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'T'
   7    0   TABLE ACCESS (FULL) OF 'T'


----- 9.2.0.8  tkprof  -----------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
     20  TABLE ACCESS FULL T
 
 

------ 10.2.0.4  Autotrace  xplan -------------------------------------------
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    20 |   380 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY      |      |       |       |            |          |
|*  2 |   HASH JOIN         |      |    20 |   640 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |    20 |   260 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |    20 |   380 |     2   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL  | T    |    20 |   380 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   2 - access("T"."ID"="T2"."ID")

------ 10.2.0.4  tkprof ------------
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=6 pr=0 pw=0 time=1435 us)
      1   HASH JOIN  (cr=6 pr=0 pw=0 time=1417 us)
     20    TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=109 us)
      1    TABLE ACCESS FULL T2 (cr=3 pr=0 pw=0 time=64 us)
     20  TABLE ACCESS FULL T (cr=4 pr=0 pw=0 time=72 us)

#

Note: in 10.2.0.4 no such problem.


I was puzzled the other day about a similar sql to that in the above test case in our 9i  production database, which has two cursors with very different  sql_plan_hash_value and gets_per_exections when I checked it from v$sql. But when I pulled the execution plan out from v$sql_plan, the execution plan from both cursors are exactly same. Now I understand the subquery execution plan parts are missing. The different cost is due to the join order swapped in the subquery.