RE: Statistical sampling and representative stats collection

John are you saying to create histograms on all indexed columns, or just the ones with distributions which are skewed and also for ones which although symmetric in distribution have some values much more prevalent than others?

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu

-----Original Message-----
Sent: Friday, May 24, 2002 3:01 PM
To: Multiple recipients of list ORACLE-L

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). 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 other words, the access plan is built without considering the value of bind variables that would have otherwise influenced the plan when histograms (and thus information about data distribution) is avialable. However, 9i kinda rectifies this and I quote from the Fine Manual: (Oracle9i: Database Performance Guide and Reference)

Cursor Sharing Enhancements
The CBO now peeks at the values of user-defined bind variables on the first invocation of a cursor. This lets the optimizer determine the selectivity of any
WHERE clause condition, as well as if literals had been used instead of bind variables. When bind variables are used in a statement, it is assumed that cursor
sharing is intended and that different invocations are supposed to use the same
execution plan.

This gives us the best of both worlds (in some cases). Of course, it all depends on the number of buckets defined for Histograms and the width of the data spread (and that is why I emphasized _some_ and 'simply stated' above).

I haven't tested this extensively, and I would appreciate any further inputs from the Gurus! (For the rest of us: A 10053 trace should show up what's happening. There was a _great_ presentation from Wolfgang Breitling on this topic at the recent IOUG).

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!

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

The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net

• The opinions and statements above are entirely my own and not those of my employer or clients **

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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).