Oracle-L: RE: Burned by DBMS

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Tue, 08 Apr 2003 09:13:37 -0800
Message-ID: <F001.0057CE91.20030408091337@fatcity.com>

Jesse,

Let me ask what may be a dumb question, but why would you want histograms on un indexed columns for? The purpose of generating statistics is to assist the optimizer to make better decisions on index usage vs. full table scans. If your queries are on non indexed columns then there is no alternative to a fts.

Dick Goulet

-----Original Message-----
Sent: Tuesday, April 08, 2003 12:14 PM
To: Multiple recipients of list ORACLE-L

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Goulet, Dick
INET: DGoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

RE: Burned by DBMS_STATS **AGAIN**

RE: Burned by DBMS_STATS AGAIN