When Oracle performance becomes a mystery and you need to get under the hood of the car, falling upon raw oracle tracing is usuablly a good start. From SQLPLUS or your favorite Oracle Client tool, run through the following steps. What you end up doing is to dump a raw trace of what Oracle is doing. I find this to be very useful information in addition to using an Explain Plan.

alter session
set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 12';

SQL STATEMENT

alter session set events '10046 trace name context off';

From the raw trace file is generated in your oracle instance path to UDUMP. “C:\oracle\product\10.1.0\admin\[INSTANCE NAME]\udump” From there open up DOS PROMPT and run,

tkprof [dump file name]

Open command line and tkprof.