Fwd: 11gR2: More than thousand child cursors for an update
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 12 Jul 2010 02:49:33 +0800
What does the V$SQL_SHARED_CURSOR say for all these child cursors?
You can use my script @nonshared.sql <sqlid> to format this nicely:
http://www.tanelpoder.com/files/scripts/nonshared.sql
Youl'll see output like this (only probably much more of it):
SQL> *@nonshared 5phajvmtd7wsy*
SQL_ID : 5phajvmtd7wsy
ADDRESS : 3175A618
CHILD_ADDRESS : 3175B56C
CHILD_NUMBER : 0
-----------------
SQL_ID : 5phajvmtd7wsy
ADDRESS : 3175A618
CHILD_ADDRESS : 2FD2D6DC
CHILD_NUMBER : 1*CURSOR_PARTS_MISMATCH :
Y*-----------------
SQL_ID : 5phajvmtd7wsy
ADDRESS : 3175A618
CHILD_ADDRESS : 3343C208
CHILD_NUMBER : 2*CURSOR_PARTS_MISMATCH :
Y*-----------------
PL/SQL procedure successfully completed.
SQL>
--
Tanel Poder
http://tech.e2sn.com
http://blog.tanelpoder.com
On Mon, Jul 12, 2010 at 2:29 AM, Martin Klier <usn@xxxxxxxxx> wrote:
> 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
> http://www.usn-it.de
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
