Trouble with multiple versions of same statement in V$SQL

  • From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
  • To: "ORACLE-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 23 Sep 2004 10:57:27 -0500

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@xxxxxxxxxxxxxxxxx      QuadTech, Sussex, WI USA



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

Other related posts: