DBO is successful at tuning queries that the native optimizer fails to optimizer correctly.
For a query to have a sub-optimal plan the database optimizer has to miscalculate the cost of the various possible access paths and mistakenly choose a bad path. The access path calculations can be miscalculated because
A clearly bad path will require enough data to make the paths sufficiently different.
The easiest case of having a good path and a bad path is using and index or not.
If the optimizer to either uses the index when it shouldn't or when
the optimizer doesn't use an index and it should (more dramatic).
select E.EMPLOYEE_ID , E.LAST_NAME , E.SALARY ,
There are two predicate filters
Salary > 9000
These two predicates filters are the ones that would be the easiest for trying to get the optimizer to go wrong.
For example if there is an index on salary then we could mess up the optimizer by trying to get it to think that > $9000 will be most of the employees and so it doesn’t use the index when actually the result set is small and it should use an index.
Oracle tracks the high value, low value and number of distinct, so I’d guess set the high value super high, set the low value just under $9000 and put all but 1 value say under $9000. Oracle will think there is an even distribution of values across the range and not use the index , I think.
I have to try.
Do you have the creation scripts?
Do you have data generation scripts?