RE: Trouble with multiple versions of same statement in V$SQL

  • From: "Steve Adams" <steve.adams@xxxxxxxxxxxx>
  • To: <Rich.Jesse@xxxxxxxxxxxxxxxxx>, "'ORACLE-L (E-mail)'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 24 Sep 2004 08:09:55 +1000

Hi Rich,

If multiple sessions attempt to parse the same statement simultaneously =
then they can each get their own child cursor. However, that
is unlikely to give you hundreds of children unless you also have =

My guess would be that CURSOR_SHARING =3D SIMILAR is to blame (again). =
Try FORCE and the problem is likely to disappear.

@   Regards,
@   Steve Adams
@         - For DBAs
@  - For all=20

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jesse, Rich
Sent: Friday, 24 September 2004 1:57 AM
To: ORACLE-L (E-mail)
Subject: Trouble with multiple versions of same statement in V$SQL

Hey all,
I'm trying to pin down multiple (sometimes hundreds) versions of the =
same statement in V$SQL on a 64-bit DB with
CURSOR_SHARING=3DSIMILAR set.  I used the address from a SELECT * FROM =
V$SQL to join V$SQL to V$SQL_SHARED_CURSOR like this:

SELECT vssc.*
FROM v$sql_shared_cursor vssc, v$sql vs
WHERE vs.address =3D HEXTORAW('C00000004E1BB2A0')
AND vssc.address =3D vs.child_address
AND vssc.kglhdpar =3D vs.address;

...but every column (other than the address columns obviously) for every =
row is "N".  I checked for invalidations in V$SQLAREA, but
there are none for this statement.  I also looked for TRUNCATEs on the =
table in the statement, but there have been none (I turned
auditing on for all TRUNCATEs).  This is an update statement that is =
only run from a third party product, always run from the same
persistent session/schema:

UPDATE ds_pending_job
   SET job_status =3D :"SYS_B_0",
       end_datetime =3D :"SYS_B_1"
 WHERE docserver_id =3D :"SYS_B_2"
AND    expiration_date < :"SYS_B_3"
AND    expiration_date > :"SYS_B_4"
AND    job_status IN (:"SYS_B_5", :"SYS_B_6");

It looks like the product does not use bind variables, but I haven't =
been able to find how or if the CS=3DS init.ora parameter would
affect that.

I've been looking around MetaLink (a few of Jonathon's replies in the =
Forums), Steve's 8i Internals book (any changes in 9i that
would account for this?), Google, even the Oracle Docs, but haven't been =
able to find out why this particular statement has so many
children in V$SQL.

Any thoughts?



Other related posts: