Oracle : force query to use hints

alter session set tracefile_identifier='RC_CBO';
alter session set events '10053 trace name context forever, level 1';
Following these two commands, run either the problem query or an explain plan for the problem query. Look for the tracefile in the user_dump_dest directory; it can be readily identified by its tracefile_identifier (in this case,RC_CBO). Browse it, attempt to understand as much of it as you can, hint your query, and start the tracing process over again. It can be very helpful to have both tracefiles (before and after hinting.


To get a trace of your own SQL session, the following statements should be included preceding the SQL under analysis:
alter session set tracefile_identifier='RC_PROF';
alter session set sql_trace=TRUE;
To also see wait events (recommended!) use this alternative :-
alter session set tracefile_identifier='RC_PROF';
alter session set events '10046 trace name context forever, level 8';
Look for files with the tracefile_identifier (in this case, RC_PROF) in the user_dump_dest directory, and apply those files to the formatter of your choice according to its documented procedures.