RE: Trouble with multiple versions of same statement in V$SQL
- From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, "ORACLE-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Sep 2004 16:10:01 -0500
So it doesn't matter how many buckets are in each column's histogram? That
seems wasteful at best, since each column in the DS_PENDING_JOB table has only
one bucket (I think -- there's only ENDPOINT_NUMBERs "0" and "1" for each
column in DBA_TAB_HISTOGRAMS). Since this is the default histogram for both
ANALYZE and DBMS_STATS.GATHER, the optimizer procedure you described doesn't
seem to be "right".
Or am I way off here? Think this is TAR worthy or a waste of time?
Rich
-----Original Message-----
Sent: Thursday, September 23, 2004 1:40 PM
Subject: Re: Trouble with multiple versions of same statement in V$SQL
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
