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:I'm stumped - removing statistics does the trick

Re:I'm stumped - removing statistics does the trick

From: <dgoulet_at_vicr.com>
Date: Fri, 18 Aug 2000 10:20:35 -0400
Message-Id: <10593.114960@fatcity.com>


Cherie,

    I've seen similar antics from Oracle when you try to recompile statistics. The general answer I've come up with, that works every time, is:

analyze index/table <name> delete statistics; analyze index/table <name> compute statistics;

Otherwise old, stale statistics seem to hang around forever.

Dick Goulet

____________________Reply Separator____________________
Subject: I'm stumped - removing statistics does the trick Author: Cherie_Machler_at_gelco.com
Date: 8/18/00 5:54 AM

I'm at a bit of a loss. I've inherited a data warehouse app that has been having performance
problems that have steadily increased in the last month or so. Upon investigation, I realized
that some of tables/columns/indexes have old statistics and the existing histograms hadn't
been refreshed since I'd come here a month and a half ago.

So I set about refreshing and re-analyzing hoping that performance would improve. It didn't
in most cases. If anything, things got worse in a lot of areas. I tried to add a few more histograms to
critical indexed columns but there was little improvement.

Looking at the explain plan of some problem queries (huge nested explains using mostly
b-tree indexes and some small full- table scans), we isolated the problem in each query
to a single, different table. Another DBA recommended removing all the statistics on one of the small dimension tables to see what would happen. It completely changed the explain plan to one using a bunch of
unnested bit-mapped index reads. We tried the same thing on the other, large dimension table giving
us trouble and the exact same thing happened. The explain plan changed from nested loop ten levels
deep utlizing regular indexes to unnested bit-mapped index utilization.

Last night's performance was great on our batch jobs for the first time in a long time. I suspect performance
will also be very good today.

This just runs contrary to all that I understand. I would think that the statistics would help the cost-based
analyzer make the best decision and that the histograms would eradicate any problems we would have
with skewed data (which we do have).

I should note that this is not a case of switching from cost-based to rule-based
(we use CHOOSE) in

these queries because all other tables in the same query still have statistics on them. I also should note
that bit-mapped indexes seem to be being misused because they are used on columns with very high
cardinality such as date fields (they worked so well on low-cardinality fields that they also used them
on their date fields as well as others). Would this make any difference? Perhaps the statistics were
revealing the high level of selectivity in the bit-mapped columns and the cost-based optimizer was
telling the database to use the regular indexes instead of the existing bit-mapped indexes. Perhaps
without the statistics, the optimizer was able to recommend the use of bit-mapped indexes instead.

We are UNIX-based, this database is version 8.0.4, the schema design is an imperfect star schema.

I am truly at a loss as to why removing the statistics would help in this situation. If anyone can give
me an idea on which path to go down, I'll research that avenue and try to figure out better what is
really going on.

Thanks for your feedback,

Cherie

-- 
Author: 
  INET: Cherie_Machler_at_gelco.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
Received on Fri Aug 18 2000 - 09:20:35 CDT

Original text of this message

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