Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Oracle database statistics... Remedy doesn't seem to like them very much
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_at_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
Received on Tue May 24 2005 - 16:02:58 CDT