Cusor sharing = Forced being ignored?
From: Douglas Cowles <dcowles_at_us.ibm.com>
Date: Fri, 26 Aug 2011 01:27:55 -0400
Message-ID: <OFFBB66171.F6EB55E7-ON852578F8.001D24F8-852578F8.001E0561_at_us.ibm.com>
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)
Date: Fri, 26 Aug 2011 01:27:55 -0400
Message-ID: <OFFBB66171.F6EB55E7-ON852578F8.001D24F8-852578F8.001E0561_at_us.ibm.com>
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-lReceived on Fri Aug 26 2011 - 00:27:55 CDT