Re: Fwd: 11gR2: More than thousand child cursors for an update
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: Martin Klier <usn@xxxxxxxxx>
- Date: Tue, 13 Jul 2010 02:28:41 +0800
So, how many bind variables do you have in that update statement?
Is the application Java/JDBC? JDBC does recalculate the bind buffer sizes
based on the input variable lengths as far as I know, thus causing the bind
buffer lengths fluctuate at different executions. When bind buffer length
(max length of bind variable) crosses a threshold (32,128,1000 or 2000
bytes), then Oracle will compile a new child cursor by default. If you have
20 varchar2's where the bind buffer fluctuates between the threshold of <=32
bytes and >32 bytes, then you'll have 20^2 = 1048576 different possible
child cursorst to cover all possible bind variable buffer size threshold
combinations :)
This doesn't explain though why you didn't see that problem in 10.2, maybe
you're hitting some new bug.
One thing you could do, is compare the current MAX_LENGTHs of bind variables
of your cursor, with past pre-upgrade lengths.
The V$SQL_BIND_METADATA would help you to see current bind buffer lengths
and if you use AWR then DBA_HIST_SQL_BIND_METADATA shows you old bind
metadata.
If the bind buffer lengths for your cursor differ between 10gR2 / 11gR2
(like perhaps in 11.2 there are more or lower bind buffer size thresholds
compared to earlier), then that's something to drill in to.
There's an event 10503 which is supposed to allow you to specify the minimum
bind buffer length for variable-width buffers:
ORA-10503: enable user-specified graduated bind lengths
However, it doesn't seem to work in most versions and I recall someone
mentioning that it even caused some ORA-600s...
Btw, did you upgrade your client libraries during the database upgrade too
by any chance?
--
Thanks,
Tanel Poder
http://blog.tanelpoder.com
http://tech.e2sn.com
On Tue, Jul 13, 2010 at 12:45 AM, Martin Klier <usn@xxxxxxxxx> wrote:
> Hi Tanel,
>
> most of the cursors are BIND_MISMATCH=Y, sometimes OPTIMIZER_MISMATCH as
> well (sometimes both).
>
> Tanel Poder schrieb:
> > So, to stop guessing, what does V$SQL_SHARED_CURSOR say? (it may not
> > show a reason at all, if hitting some bug, but that's the first thing to
> > check).
>
> Regards
> Martin
>
> --
> Usn's IT Blog for Linux, Oracle, Asterisk
> http://www.usn-it.de
>
>
Other related posts:
