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

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

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