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

David Sanabria wrote:
>
> 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
>


Do a search in google here for "rebuild indexes" and read.
The results will be surprising. One thing: make sure you
are patched up to the latest level of 8.1.7, the earlier
point releases had all sorts of bugs in the CBO that caused
a lot of weird problems. And move to LMT as soon as possible
if not already there. You may also have to fiddle with the
optimizer_* parameters: the above search will provide some
good reading material on this.

"Noons" <wizofoz2k@yahoo.com.au> wrote in message
news:1116988939.617917.87910@g44g2000cwa.googlegro ups.com...
> David Sanabria wrote:
> >
> > 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
> >
>
>
> Do a search in google here for "rebuild indexes" and read.
> The results will be surprising. One thing: make sure you
> are patched up to the latest level of 8.1.7, the earlier
> point releases had all sorts of bugs in the CBO that caused
> a lot of weird problems. And move to LMT as soon as possible
> if not already there. You may also have to fiddle with the
> optimizer_* parameters: the above search will provide some
> good reading material on this.
>
I agree with Noons. Why are you rebuilding indexes? It is probably due to
a myth you heard that you must if the table gets deletes. Not true.
Jim

David Sanabria wrote:
>
> 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
>


Do a search in google here for "rebuild indexes" and read.
The results will be surprising. One thing: make sure you
are patched up to the latest level of 8.1.7, the earlier
point releases had all sorts of bugs in the CBO that caused
a lot of weird problems. And move to LMT as soon as possible
if not already there. You may also have to fiddle with the
optimizer_* parameters: the above search will provide some
good reading material on this.

"Noons" <wizofoz2k@yahoo.com.au> wrote in message
news:1116988939.617917.87910@g44g2000cwa.googlegro ups.com...
> David Sanabria wrote:
> >
> > 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
> >
>
>
> Do a search in google here for "rebuild indexes" and read.
> The results will be surprising. One thing: make sure you
> are patched up to the latest level of 8.1.7, the earlier
> point releases had all sorts of bugs in the CBO that caused
> a lot of weird problems. And move to LMT as soon as possible
> if not already there. You may also have to fiddle with the
> optimizer_* parameters: the above search will provide some
> good reading material on this.
>
I agree with Noons. Why are you rebuilding indexes? It is probably due to
a myth you heard that you must if the table gets deletes. Not true.
Jim

"David Sanabria" <david.sanabria@hartfordthe.com> wrote in message
news: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.

I haven't heard of Remedy, but I can't actually see that that is especially
important here, since it is the behaviour of Oracle optimizing sql
statements that is likely your problem.

Do you really mean that queries against this table go slow, or that queries
that join this table to others go slow? If the latter then this is exactly
what I'd expect. If at least one of the objects has stats then the CBO will
kick in, for objects that don't have stats (on your version anyway) the CBO
will use default values for the estimation of query costs, these defaults
will be way wrong
(IIRC it will assume 100 row tables of 10 blocks or so each). If you are
going to use the CBO (and generally I think you should) then its an all or
nothing thing, you'll want to give the CBO as much 'accurate' information as
possible.

> 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.

Not quite, see above. The CBO will also kick in if you have used features
that the RBO doesn't know about.

> 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.

Notwithstanding my view that the CBO is *generally* better than the RBO (and
has been since at least 815 IMO) I'm curious as to why you are gathering
stats - especially on a production system, do you already have performance
issues? If I were wanting to introduce the CBO, or to change what stats I
gathered and how, I'd start with a test system and important queries, gather
stats on all objects in the schema (using
dbms_stats.gather_schema_stats(owner, cascade => true) and possibly add in
histogram gathering for certain objects later) and see how the queries
performed.


> 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.

What makes you think the table needs to be reorganized? It sounds like a
fairly normal table, i.e one that is subject to a normal pattern of dml, and
therefore is unlikely to warrant reorganisation? Similarly regualr index
reorganisation is usually an almost complete waste of time - though in many
systems you won't notice the cost of doing it (increased downtime and slower
index performance due to leaf block splitting).


--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

On Thu, 26 May 2005 05:45:06 +0100, "Niall Litchfield"
<niall.litchfield@dial.pipex.com> wrote:

>I haven't heard of Remedy

Keep it that way. It is yet one of those many packages out there using
Oracle as a flat file system. No RI, and full table scans all over the
place, because of upper(columname) = upper (hardcoded literal)


--
Sybrand Bakker, Senior Oracle DBA

"David Sanabria" <david.sanabria@hartfordthe.com> wrote in message
news: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.

I haven't heard of Remedy, but I can't actually see that that is especially
important here, since it is the behaviour of Oracle optimizing sql
statements that is likely your problem.

Do you really mean that queries against this table go slow, or that queries
that join this table to others go slow? If the latter then this is exactly
what I'd expect. If at least one of the objects has stats then the CBO will
kick in, for objects that don't have stats (on your version anyway) the CBO
will use default values for the estimation of query costs, these defaults
will be way wrong
(IIRC it will assume 100 row tables of 10 blocks or so each). If you are
going to use the CBO (and generally I think you should) then its an all or
nothing thing, you'll want to give the CBO as much 'accurate' information as
possible.

> 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.

Not quite, see above. The CBO will also kick in if you have used features
that the RBO doesn't know about.

> 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.

Notwithstanding my view that the CBO is *generally* better than the RBO (and
has been since at least 815 IMO) I'm curious as to why you are gathering
stats - especially on a production system, do you already have performance
issues? If I were wanting to introduce the CBO, or to change what stats I
gathered and how, I'd start with a test system and important queries, gather
stats on all objects in the schema (using
dbms_stats.gather_schema_stats(owner, cascade => true) and possibly add in
histogram gathering for certain objects later) and see how the queries
performed.


> 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.

What makes you think the table needs to be reorganized? It sounds like a
fairly normal table, i.e one that is subject to a normal pattern of dml, and
therefore is unlikely to warrant reorganisation? Similarly regualr index
reorganisation is usually an almost complete waste of time - though in many
systems you won't notice the cost of doing it (increased downtime and slower
index performance due to leaf block splitting).


--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

On Thu, 26 May 2005 05:45:06 +0100, "Niall Litchfield"
<niall.litchfield@dial.pipex.com> wrote:

>I haven't heard of Remedy

Keep it that way. It is yet one of those many packages out there using
Oracle as a flat file system. No RI, and full table scans all over the
place, because of upper(columname) = upper (hardcoded literal)


--
Sybrand Bakker, Senior Oracle DBA

Niall Litchfield wrote:

> Do you really mean that queries against this table go slow, or that queries
> that join this table to others go slow? If the latter then this is exactly
> what I'd expect. If at least one of the objects has stats then the CBO will
> kick in, for objects that don't have stats (on your version anyway) the CBO
> will use default values for the estimation of query costs, these defaults
> will be way wrong

Queries from the Remedy system run slower, my reports run faster. It's a
terrible paradox.

> Notwithstanding my view that the CBO is *generally* better than the RBO (and
> has been since at least 815 IMO) I'm curious as to why you are gathering
> stats - especially on a production system, do you already have performance
> issues? If I were wanting to introduce the CBO, or to change what stats I
> gathered and how, I'd start with a test system and important queries, gather
> stats on all objects in the schema (using
> dbms_stats.gather_schema_stats(owner, cascade => true) and possibly add in
> histogram gathering for certain objects later) and see how the queries
> performed.

The cost of Remedy's queries is very high and I am trying to improve
response times with small tweaks to the DB. The target schema doesn't
have _any_ statistics. In my experience as a developer (I'm an App DBA
now by title rather than by virtue of training) the database returns
data faster and at a lower cost when there are statistics. Cost is less
important than response time so I'm not as concerned with that. I _am_
concerned that the system has such poor response to queries that run
orders of magnitude faster when I have current statistics.


>>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.
>
>
> What makes you think the table needs to be reorganized? It sounds like a
> fairly normal table, i.e one that is subject to a normal pattern of dml, and
> therefore is unlikely to warrant reorganisation? Similarly regualr index
> reorganisation is usually an almost complete waste of time - though in many
> systems you won't notice the cost of doing it (increased downtime and slower
> index performance due to leaf block splitting).

The primary motivator is that we are using dictionary managed TS and
this table has a very high churn rate and inline CLOBs. We are hoping to
reduce the size of this table and it's indexes (>700MB used by indexes
alone) by regular reorgs.

I appreciate your thoughtful response! The learning curve for all of
this is steep enough that a helpful hand is greatly appreciated.

Dave