Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Question for 10g enthusiasts

Re: Question for 10g enthusiasts

From: Rick Denoire <>
Date: Tue, 06 Nov 2007 00:54:06 +0100
Message-ID: <>

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

Rick Denoire wrote:

>On Sat, 03 Nov 2007 00:51:22 +0100, Rick Denoire
><> wrote:
>>DA Morgan <> 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.
>>>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
>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
>>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.
>>Rick Denoire
>Sybrand Bakker
>Senior Oracle DBA
Received on Mon Nov 05 2007 - 17:54:06 CST

Original text of this message