Re: Oracle 11.2: Cursor Mutex S wait event and too many (2^30) child cursors

  • From: Martin Klier <usn@xxxxxxxxx>
  • To: Michael Dinh <mdinh@xxxxxxxxx>
  • Date: Wed, 04 Aug 2010 21:29:10 +0200

Hi Michael,

cursor_sharing was set to exact.

Aftrer searching MOS and the web I also suspected that
NLS_LENGTH_SEMANTICS=CHAR that we had set in the beginnig) might play a
role, and in a way, it did:
NLS_L_S=CHAR means total run-dead on CPU with Mutex S
NLS_L_S=BYTE means CPU run-dead up to 1800 cursors, then they reduce by
magic to zero, and the circle begins again.


Michael Dinh schrieb:
> Thanks for sharing.
> What was cursor_sharing set to in your environment?

> Summary: If you use JDBC and set numeric values with setInteger or
> setNumber or setNull alternately, you can get excessive numbers of child
> cursors for your DML statement.
> Solution: Use setNull.NUMBER or setNull.INTEGER or whatever your
> datatype is.
> Why? setNull defaults to VARCHAR2() and this will invalidate your child
> cursor...
> By the way, the same happens if you are subsequently extending string
> bind variable lenghts, until you reach the maximum length!
> Best regards and thanks for all your help in the past, not only for this
> issue
Usn's IT Blog for Linux, Oracle, Asterisk


Other related posts: