Cusor sharing = Forced being ignored?
- From: Douglas Cowles <dcowles@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 26 Aug 2011 01:27:55 -0400
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
Other related posts:
