Re: Trouble with multiple versions of same statement in V$SQL
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "ORACLE-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Sep 2004 19:39:39 +0100
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@xxxxxxxxxxxxxxxxx>
To: "ORACLE-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
