Trouble with multiple versions of same statement in V$SQL

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Thu, 23 Sep 2004 10:57:27 -0500
Message-ID: <FBE1FCA40ECAD41180400050DA2BC54004E93DA9@qtiexch2.qgraph.com>

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=SIMILAR 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 = HEXTORAW('C00000004E1BB2A0')
AND vssc.address = vs.child_address
AND vssc.kglhdpar = 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 = :"SYS_B_0",
       end_datetime = :"SYS_B_1"
 WHERE docserver_id = :"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=S 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

Received on Thu Sep 23 2004 - 11:24:56 CDT