| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re:I'm stumped - removing statistics does the trick
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
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-LReceived on Fri Aug 18 2000 - 09:20:35 CDT
(or the name of mailing list you want to be removed from). You may
 |
 |