Re: Database Performance

Might be worth analysing the whole schema before re-creating

Dave

Dave Lane (dlane_at_pt.lu)
In article <3965FCFB.5A6B149F_at_euclidsys.com>, randy_at_euclidsys.com (Randy DeWoolfson) wrote:

> I also dont like to see more than 10 extents...
> Although I bet your performance is query related and not storage
> related...
>
> Randy
>
> sybrandb_at_my-deja.com wrote:
>
> > In article <39570A25.33DB2FBC_at_hotmail.com>,
> > kskasi_at_hotmail.com wrote:
> > > Hello everyone
> > >
> > > The problem is that, the biggest table we have in our system has
> > > got
> > > only about 50,000 records. But the system seems to be slow even with
> > > those many records. Our DBA feels that enough space wern't alloted
> > > to
> > > the tables and they might be sitting in multiple extends. I had a
> > > look
> > > at the extends and I did notice that there are about 7 - 10 extends
 for
> > > the big table and some of the indexes. According to him, if there
> > > are
> > > more number of extends then oracle has to search through all the
 extends
> > > which takes more time. I had a look at Oracle documentation and it
> > > dosen't mention about this at all. I just want a second opinion on
 this
> > > before I drop the tables and recreate them with enough space. I
> > > guess

 I
> > > can rebuild the indexes with enough space but the tables has to be
> > > dropeed and reloaded. Any help would be appreciated
> > >
> > > Cheers...kasi
> > >
> > >
> >
> > Searching through extents applies to full table scans and likely fast
> > full index scans. Oracle will never read more than one extent at a
> > time. IMO, have a fragmented index is much more harming your
> > performance than a table in a few extents, assuming you have
> > infrequent
> > full table scans.
> > However, the critical limit is for me 10 extents. Whenever your object
> > has more, defragment it.
> >
> > Hth,
> > --
> > Sybrand Bakker, Oracle DBA
> >
> > All standard disclaimers apply
> >
> >