Re: Question for 10g enthusiasts

The hint with session_cache_cursor might be the solution, indeed. I will check it tomorrow at the office.

Thanks
Rick Denoire

sybrandb_at_hccnet.nl wrote:

>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 Mon Nov 05 2007 - 17:54:06 CST