"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<b3kevq$1mkuks$1_at_ID-152732.news.dfncis.de>...
> Hi Mark,
>
> yes I did analyze the table (it was only one, but a big one) and its indexes
> right after
> rebuilding, with compute statistics as always. If You would know something
> more
> in this case, please let me know.
>
> Some people answered directly to me, thank You very much.
>
> Jan
>
>
> "Mark D Powell" <Mark.Powell_at_eds.com> schrieb im Newsbeitrag
> news:2687bb95.0302260602.12982332_at_posting.google.com...
> > "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
> news:<b3ht1n$1m3glk$1_at_ID-152732.news.dfncis.de>...
> > > Hello,
> > >
> > > I would like to know if index compression can have *negative*
> performance
> > > impacts
> > > to applications.
> > >
> > > The background is this:
> > > On thursday 13th I reorganized some indexes which the COMPRESS clause
> > > of a certain user schema; I was hoping that besides of space savings on
> the
> > > disks, queries in the schema
> > > may become a little faster by doing this; of course, I did analyze the
> whole
> > > schema right afterwards
> > > (to be clear: it was not SYSTEM).
> > >
> > > The COMPRESS clause was used without any following number, and only if
> the
> > > index
> > > had a cardinality/rows ratio that afaik made index compression an
> > > appropriate action.
> > > My main reference was Guy Harrisons tuning book.
> > >
> > > Today (26th) I am hearing for the first time
> > > that users in that schema are recognizing performance loss
> > > "since about one week"
> > > with *any* app they are using in that schema. And the apps have not been
> > > changed
> > > .
> > > On the other hand, most of the users do not *dare*
> > > to call the admins about what is happening ... so it is not very
> reliable
> > > what they say ...
> > >
> > > Because I am not too experienced as many gurus here, I would like to ask
> for
> > > an eventual confirmation
> > > about index compression risks,
> > > or if still some more informations are needed to judge the situation.
> May I
> > > have to rebuild the indexes
> > > with NOCOMPRESS again ? It is yet not a "hot issue", I just want to be a
> > > little more sure ...
> > >
> > > My system:
> > > Oracle EE 8.1.7.3 on AIX 4.3.3 on a Bull server which is now 3 years old
> ...
> > > Oracle Forms & Reports inhouse app on Windows clients
> > >
> > > TIA
> > >
> > > Jan
> >
> > Jan, yes I believe index compression sometimes hurts performance.
> > Sometimes it helps. It depends on the data and how it is accessed.
> >
> > But first, did you remember to re-analyze the table and its indexes
> > after conversion of the one index to being compressed? Failure to do
> > so rather than the compression itself could be the problem.
> >
> > HTH -- Mark D Powell --
Jan, I sent an email with a presentation on compressed indexes that
might be of interest to you. -- Mark --
Received on Thu Feb 27 2003 - 12:10:35 CST
