RE: Burned by DBMS_STATS **AGAIN**

OK, I've saved the histograms generated by each method using:

CREATE TABLE cf_histograms_dbms_stats AS SELECT *
FROM dba_tab_histograms
WHERE owner = 'CHARLIE_FOXTROT'
ORDER BY table_name,column_name,endpoint_number

and, likewise:

CREATE TABLE cf_histograms_analyze AS
SELECT *
FROM dba_tab_histograms
WHERE owner = 'CHARLIE_FOXTROT'
ORDER BY table_name,column_name,endpoint_number

I then compared the two tables using a MINUS operator. After scanning the vast differences (cf_histograms_dbms_stats has 3384 rows, cf_histograms_analyze has 1958), I noticed that all the rows "missing" from cf_histograms_analyze seem to be indexed columns. So, I ran this to prove my point:

SELECT hi.table_name, hi.column_name
FROM dba_ind_columns di,
(

SELECT table_name,column_name
FROM cf_histograms_dbms_stats
MINUS
SELECT table_name,column_name
FROM cf_histograms_analyzed
) hi
WHERE di.index_owner = 'CHARLIE_FOXTROT'

AND di.table_name(+) = hi.table_name
AND di.column_name(+) = hi.column_name
AND di.index_name IS NULL;

Sure enough, the query returns no rows, leading me to believe that ANALYZE TABLE...COMPUTE STATISTICS generates histograms of SIZE 1 for all NON-indexed columns. Does this conclusion sound correct? If so, then it doesn't appear from the 8i docs that there is a method in DBMS_STATS to emulate this. So, now what? I'm a little hesitant to initially gather stats using ANALYZE, then rely on DBMS_STATS afterwards because of the differences in the stats they gather. It would seem to me that there's a very good chance that the stats that aren't regularly updated with DBMS_STATS will become stale and at some point could negatively alter explain plans.

BTW, I forgot an earlier posted question: There are no partitioned tables.

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Monday, April 07, 2003 4:39 PM
To: Multiple recipients of list ORACLE-L

I agree.

I wonder if some of the problems are to do with changes in the number of bytes used for storing details of character columns ? I don't recall which version the big change took place.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).