Re: Cusor sharing = Forced being ignored?

    I don't see any reason why, from within the same session and the same client, one statement would use cursor sharing when running stand-alone and not when running in a PL/SQL block. I assume of course that you cut and pasted the stand alone statement between "begin" and "end" to obtain your anonymous block, that all values are hard-coded in your statement and that you didn't do the classic mistake of mistaking SQL*Plus substitution variables (&var) and actually bound variables (:var). PL/SQL blocks affect the exchanges (number of roundtrips) between the client and the server, but once on the server there should not be any difference.

   Have you thought of checking what V$SQL_SHARED_CURSOR says? That's what I would do in your case.

HTH

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>



On 08/26/2011 07:27 AM, Douglas Cowles wrote:


> This is odd - at least to me.. I have a database (oracle 10.2.0.4 on 

> Solaris)  where cursor sharing is set to forced in the init file.

> I had a problem where there was an insert statement that when was 

> executed at a SQL prompt.. ran in seconds.

> When put in an anonymous block, it chugged away for more than 20 

> minutes and then threw an error on temp space.

> I did a tkprof on the situation and it turned out that cursor sharing 

> was used in the *standalone* SQL but literals were used in the PL/SQL 

> block and resulted in a bad plan.

> I had a pretty reputable source at Oracle help me to determine this. 

>  This isn't to say that cursor sharing is great or anything but in 

> this case it was used by the optimizer at the SQL prompt, but not in 

> the anonymous block and caused problems.

> My question is just .. why would the init file parameter be ignored in 

> the anonymous block or PL/SQL code and not at the standalone prompt?

> (The original problem was the statement in a stored proc)

>

> Anyone?

> Thanks,

>

> - Dc

>

>




--
http://www.freelists.org/webpage/oracle-l