Raw Oracle Tracing
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.