Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Burned by DBMS_STATS **AGAIN**

RE: Burned by DBMS_STATS **AGAIN**

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 08 Apr 2003 11:24:12 -0800
Message-ID: <F001.0057D1B8.20030408112412@fatcity.com>


Why don't you save the statistics from each analyze method using dbms_stats.export_table_stats, using different statids?

At 08:14 AM 4/8/2003 -0800, you wrote:
>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).

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com


This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier electronique est une communication privee a l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'etes pas le destinataire prevu, vous etes avise, par les presentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'etes pas specifiquement autorise a recevoir ce courriel ou si vous croyez l'avoir recu par erreur, veuillez en aviser l'expediteur original immediatement. Nous respectons les demandes similaires qui touchent la confidentialite des communications par courrier electronique.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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).
Received on Tue Apr 08 2003 - 14:24:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US