11gR2: More than thousand child cursors for an update

  • From: Martin Klier <usn@xxxxxxxxx>
  • To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 11 Jul 2010 20:29:20 +0200

Hi list,

I've upgraded an Oracle 10gR2 to 11gR2 yesterday.

Now one UPDATE statement (means one single SQL ID) produces a huge
amount of child cursors, about 500-1500 within ten minutes. All of them
are re-used for 5-20 times, then the statement is reparsed and a new
child cursor generated, and so on. All of them are SHAREABLE, but
neither bind aware nor bind sensitive.

cursor sharing is EXACT here.

There's one UPDATE statement seems to be affected in an extreme way,
causing heavy "cursor: mutex S" latches due to excessive reparsing of
the same SQL, and due to that, CPU load is excessive.

Other statements are not affected, or only too less to be notified
within this thunderstorm. We are talking about 45 sessions issuing the
same UPDATE statement about 30-100 times per second.

I am grateful for every suggestion where to look and what to do. An
Oracle SR has already been issued, but I want not miss your collected
experience for some OSS engineer...  ;)
Usn's IT Blog for Linux, Oracle, Asterisk


Other related posts:

  • » 11gR2: More than thousand child cursors for an update - Martin Klier