Re: Question for 10g enthusiasts

On Sat, 03 Nov 2007 00:51:22 +0100, Rick Denoire <educacion.superior_at_online.de> wrote:

>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.

Synonyms. If the access is through private synonyms, the query will considered to be different. Access privileges are also checked again. Oracle actually does use the same execution plan, provided you do use bind variables. If you don't use bind variables your query will be parsed
>
>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?

session_cache_cursors has been set.
Apart from session_cache_cursors, this is an application issue. If the app closes and releases the cursor, the query will be reparsed. What you ask is what you get.
>
>Bye
>Rick Denoire

Sybrand Bakker
Senior Oracle DBA Received on Sun Nov 04 2007 - 06:51:17 CST