Re: Question for 10g enthusiasts

DA Morgan <damorgan_at_psoug.org> wrote:

>Rick Denoire wrote:
>
>> After migration I found out that some SQL queries generated by the use
>> of the application are being executed diferently depending on the DB
>> account used. This is an Oracle based commercial application with its
>> own client, no way to tune the text of SQL queries. I would like to
>> force Oracle to use the better execution plan for all users.
>
>You made one huge assumption that is not valid when you wrote:
>"This is an Oracle based commercial application with its own client, no
>way to tune the text of SQL queries"
>
>There are numerous ways to tune those queries or, in fact, replace them.
>
>There are, of course, histograms and stored outlines, but if those don't
>work consider using advanced rewrite.
>
>DBMS_ADVANCED_REWRITE
>http://www.psoug.org/reference/dbms_adv_rewrite.html
>
>Try my demo and if you like what it does ... read the docs at tahiti.

Thanks, I am taking a close look at it, but that is just for general interest. There are some constraints (not only of technical nature) preventing me from interfering too deep (between others, support by the vendor would be questionable).

The idea behind my question is: If Oracle already found a good execution plan which is applied for some users, why not just apply the same for other users? I don't need to even understand the execution plan itself to do that. My question is how to actually influence Oracle to choose the right existing plan. This is a legitime intention, since it makes no sense to me why the same query on the same data (same DB etc.) is executed differently depending on the DB account.

Perhaps it would be sufficient to let Oracle see the query as being new when executed by the impacted users, and then reparse it correctly or reuse the "good" cursor. But how can I do that?

Bye
Rick Denoire Received on Fri Nov 02 2007 - 18:51:22 CDT