RE: Statistical sampling and representative stats collection

> Hi Jack,
>
> > One question - you mention that an index analyze
> > provides beter data distribution. Could you discuss
> > what you found in more detail?
>
> What I meant was that the Histograms that are created during an
> ANALYZE/COMPUTE on Indexes will provide an almost perfect picture of the
> data distribution in such columns. Under _some_ circumstances,
> the CBO will
> be able to use this information to decide the best path (FTS or Indexed
> read).

And stats on the non-indexed columns can also play a large role in deciding driving table order and join methods. Ok, touched on that in an earlier email ;-)

> On the other hand, and simply stated, when bind variables
> are used in
> a cursor, this information about data distribution is not used since the
> value of the bind variable is not used during the parse prior to 9i.

In my case, and Jack's (I'm now doing some work with a DB where Jack is dealing with the analyze strategies), the bind thing isn't an issue. Everything is ad-hoc, and, literals *are* used. But, there really isn't much of an opportunity for sharing SQL even if binds were used. One user might specify 5 values for one column, 3 values for another, 2 values for five other columns. The combinations of the criteria specified, and the number of values specified for each of those columns, not to mention the tables specified, very few, if any, of the SQL statements could be shared even if using binds. Plus, in this case, with histograms being very valuable, one could live with less cursor sharing even if there were some that could be shared when using binds. In this case, the literals are needed and their use is not causing any shared pool or library cache contention.

>
> Btw: Searching for 'bucket' in the 8i SQL reference came up with the NTILE
> function (new in 8i), and I said "Wow!" because I was looking for such a
> function. Goes to say that we need to read the fine manuals more than we
> normally do!

The analytic functions are great. The analytic functions first came about in 8.1.6, a few more functions added in 8.1.7, and taken even further in 9i. A lot of the traditional ways we might have done things, often times including self joins, or, procedural code, are thrown out the window. I've found all kinds of uses for them that (1) improve performance over the old approaches, and (2) are simpler to understand. Then again, some of the analytic function examples leave my head spinning. I'm still working through a lot of them for better understanding. But yeah, analytic functions like NTILE are very, very nice.

>
> John Kanagaraj
> Oracle Applications DBA
> DBSoft Inc
> (W): 408-970-7002

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).