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

Home -> Community -> Usenet -> c.d.o.tools -> Re: Database Performance

Re: Database Performance

From: MBruinsma <bruintje_at_cable.a2000.nl>
Date: 2000/06/27
Message-ID: <39591D1E.26A7E421@cable.a2000.nl>#1/1

Kasi,

Saying that your system is slow doesn't really mean that much. Slow compared to what? I hear these comments every now and then during my work as a DBA and I usually ask what they mean by slow, what are they doing, what processes are they running, was it this slow yesterday, or the day before, etc. What I am saying is, you will need to qualify your problem a bit more, because let's

face it, 50K records for a table is not exactly the end of the world as we
know it, so there must be something else wrong here. It's not the number of
extents, because that, up to a point way beyond a few thousand extents,  has
virtually no impact on performance for a number of reasons (like Brain stated before me).

What sort of processes are you running the moment you find that things are slowing down, perhaps you're inserting a large number of records into a table which has about 12 indexes on it ( I've seen that happen and believe me, you can almost count the bytes dropping on the disk one by one), or perhaps your SQL is not as crisp as it should be. That, by the way, is one area where you can really score big, because if there is anything that can kill performance (or increase it!), it's inefficient (PL/)SQL.

What you could try is tracing your instance for a while, TKPROF the output and analyze it (make sure TIMED_STATISTICS = TRUE). Look for full table scans in the explain plans where you would expect access by rowid's, or very high elapsed times compared to the number of rows returned, things like that and see if anything jumps out at you. That should give you an idea of where to start looking.

Good luck,

Marc Bruinsma
Oracle DBA
CMG. Brian Peasland wrote:

> Kasi,
>
> 7-10 extents for a segment is not that many extents. If you are doing
> full table scans, then you will search 50,000 records no matter if it is
> in one extent, or many extents. It's not uncommon for some of my tables
> to have around 1,000 extents without seriously impacting performance.
> Many DBAs are subscribing to the theory that multiple extents are not
> that serious of a problem. There are many reasons for this which I won't
> go into here.
>
> The first place that you should look into is to follow Jacques post and
> tune your SQL statements.
>
> HTH,
> Brian
>
> 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
>
> --
> ========================================
> Brian Peasland
> Raytheons Systems at
> USGS EROS Data Center
> These opinions are my own and do not
> necessarily reflect the opinions of my
> company!
> ========================================
Received on Tue Jun 27 2000 - 00:00:00 CDT

Original text of this message

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