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

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Thu, 23 Sep 2004 12:37:19 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660EBA@bosmail00.bos.il.pqe>

Is Connor McDonald on this list? I think I recall he ran into a similar situation. Last I heard, I think he suspected an Oracle bug.

Connor, you out there?

-Mark

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jesse, Rich Sent: Thursday, September 23, 2004 11: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

Rich Jesse                        System/Database Administrator
rich.jesse_at_quadtechworld.com      QuadTech, Sussex, WI USA

--

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

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 23 2004 - 11:40:40 CDT