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

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Fri, 24 Sep 2004 08:09:55 +1000
Message-ID: <002e01c4a1ba$0eb5c900$ae8735cb@ixora.com.au>

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 = invalidations.

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
@ http://www.ixora.com.au/ - For DBAs
@ http://www.christianity.net.au/ - For all=20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] 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 9.2.0.5.0 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?

TIA,
Rich

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 23 2004 - 17:47:54 CDT