Re: CBO & different execution plans

Charles Hooper wrote:

> I experimented a bit some time ago with dbms_advanced_rewrite. I had
> a SQL statement in a packaged application that was taking excessively
> long to execute - roughly 3.4 seconds per execution and the packaged
> application was trying to execute the statement thousands of times.
> The report that should have required 15 seconds to complete required
> 12+ minutes. The problem in this case had to do with Oracle picking
> the wrong index for a table access. Providing a hint to Oracle to use
> the correct index dropped the execution time down to roughly 0.04
> seconds per execution, allowing the report to again complete in 15
> seconds (bad news is that, if the application were specifically coded
> for Oracle, it could have built the report in less than 2 seconds).
> During my experimentation, I could not make dbms_advanced_rewrite
> recognize the presence of the hint and to have Oracle act on the hint
> that was embedded.

Every time I have seen this behaviour it came down to one of two issues:

1. query_rewrite not set to trusted
2. a database character set not supported

If anyone has an example of this not working I would appreciate receiving it off-line.

Thank you.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org