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

Home -> Community -> Usenet -> c.d.o.server -> Oracle database statistics... Remedy doesn't seem to like them very much

Oracle database statistics... Remedy doesn't seem to like them very much

From: David Sanabria <david.sanabria_at_hartfordthe.com>
Date: Tue, 24 May 2005 21:02:58 GMT
Message-ID: <6EMke.364$QE6.234@newssvr31.news.prodigy.com>


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

Original text of this message

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