| David Sanabria 2005-05-24, 8:23 pm |
| I ran into an issue today that reminded me of a long-running question
about Oracle and Remedy. I updated the statistics on one of our larger
tables (TEC_EVENT, 5GB) which I cascaded down to the indexes. I used
DBMS_STATS to compute the stats across the entire table (no samples).
Immediately after I updated the stats, one of our power users (and
sometime remedy developer) reported that his querying against this table
had slowed to a crawl when he searched from the Remedy client.
Our database is currently in CHOOSE mode, which should use the stats
when they are there or else use the Rule-based optimizer when not. The
previous DBA told me that he had updated the stats on the entire ARADMIN
schema at one point and ended up deleting them shortly after because of
performance complaints. As soon as I deleted the stats on the target
table, the user indicated that performance had gone back up to his
satisfaction.
This table is a particularly good example of a one that needs to be
reorganized and have its indexes rebuilt. The reason I was working on
this table was because I received a request to schedule an index rebuild
on this table once each week to compensate for a high turnover rate
(add/delete). I hoped that starting with the stats would give a nice
bump without having to contention issues while rebuilding the indexes.
Do you have any insights or perspective on this issue?
FYI: Oracle => 8.1.7
Oracle CBO mode => Choose
Remedy => BMC Action Request System 5.1.2
Thanks,
Dave
N. David Sanabria
Specialist, DA
The Hartford, Infrastructure Solutions Department
Online Services Information Delivery
@: david. sanabria@hartfordthe
.com (the goes before hartford)
Don't be afraid to take a big step if one is indicated; you can't cross
a chasm in two small jumps. David Lloyd George
|