* A simple example of event 10053 tracing
Event 10053 can be used to understand the decision making process of the CBO.

* Event 10053 tracing output

SQL> 
SQL> -- This is not a particularly effective BJI. Just to see how ORACLE handles it
SQL> -- and what event 10053 tracing shows.
SQL> 
SQL> drop table test1_a;
drop table test1_a
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> drop table test2_a;
drop table test2_a
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> drop table test3_a;
drop table test3_a
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> create table test1_a as select * from test1;

Table created.

SQL> create table test2_a as select * from test2;

Table created.

SQL> alter table test2_a add constraint test2_a_pk primary key(c1);

Table altered.

SQL> create table test3_a as select * from test3;

Table created.

SQL> 
SQL> -- Need unique or primary "constraint"
SQL> alter table test3_a add constraint test3_a_u unique (c2);

Table altered.

SQL> 
SQL> create bitmap index test1_a_bji on test1_a(t3.c2)
  2  from test1_a t1, test2_a t2, test3_a t3
  3  where t1.c1=t2.c1 and t2.c2=t3.c2;

Index created.

SQL> 
SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL> 
SQL> select sum(sumc2) from test1_a t1, test2_a t2, test3_a t3
  2  where t1.c1=t2.c1 and t2.c2=t3.c2
  3  and t3.c2 in (1,2,3);

SUM(SUMC2)                                                                      
----------                                                                      
      1060                                                                      

SQL> 
SQL> -- This one works
SQL> select/*+ index (t1 test1_a_bji) */ sum(sumc2) from test1_a t1, test2_a t2, test3_a t3
  2  where t1.c1=t2.c1 and t2.c2=t3.c2
  3  and t3.c2 in (1,2,3);

SUM(SUMC2)                                                                      
----------                                                                      
      1060                                                                      

SQL> 
SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=1;

Session altered.

SQL> select sum(sumc2) from test1_a t1, test2_a t2, test3_a t3
  2  where t1.c1=t2.c1 and t2.c2=t3.c2
  3  and t3.c2 in (1,2,3);

SUM(SUMC2)                                                                      
----------                                                                      
      1060                                                                      

SQL> 
SQL> alter session set events '10053 trace name context off';

Session altered.

SQL> -- Restore the default
SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=100;

Session altered.

SQL> 
SQL> drop table test1_a;

Table dropped.

SQL> drop table test2_a;

Table dropped.

SQL> drop table test3_a;

Table dropped.

SQL> 
SQL> spool off