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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 23 Sep 2004 19:39:39 +0100
Message-ID: <002701c4a19c$aefb9570$6702a8c0@Primary>

If you have a histogram on any of the columns in the "where" clause, then cursor_sharing=similar will cause Oracle to convert incoming literals to binds
(in the style you've quoted) and then still re-optimises
for the actual values in the binds, generating a new child cursor each new set of values.

Even in 10g, v$sql_shared_cursor shows NO for every single option when this happens.

(Session 1, slide 33 - for anyone who's in NY
or Boston for my masterclass in the next two weeks).

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated Sept 19th

Original Message ----- From: "Jesse, Rich" <Rich.Jesse_at_quadtechworld.com> To: "ORACLE-L (E-mail)" <oracle-l_at_freelists.org> Sent: Thursday, September 23, 2004 4:57 PM 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=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


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

Received on Thu Sep 23 2004 - 13:35:09 CDT