Platform:	Oracle	App:		Tool:
DB Ver:	9.2	App Ver:		Tool Ver:
Revision Date:	22-Feb-2006	Keywords:	HOWTO, SQL TUNING, CBO
Goal
To get the CBO to find a better execution path
Facts
Solution
Find the SQL query that is causing the problem. Then in your own investigative session execute the following:
alter session set optimizer_dynamic_sampling=9; Rerun the query. If the performance improves, then include the following hint in the SQL:

SELECT /*+ dynamic_sampling(x) */ *
FROM ...

where X is a number 0-10

The reason this works is that normally the CBO only checks a number of potential execution paths. This hint forces it to look harder for a better path, but has the downside that it takes longer to parse the SQL, so by default it is not set.

By including this simple hint on the single slow SQL, it will improve the performance of the statement without adversely affecting the other SQL statements.
For additional info on dynamic sampling hint see:[1]