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 -> Re: fragmentation analysis

Re: fragmentation analysis

From: Ricky Sanchez <rsanchez_at_more.net>
Date: Wed, 30 Jan 2002 15:05:24 GMT
Message-ID: <3C580BEF.23A1447C@more.net>


a-

Forget about fragmentation. Hardly likely to be a performance problem. Instead, find the actual worst bottleneck in your system and proceed from there. Follow these steps.

  1. First, make sure your machine / os is performing correctly, that you don't have a hardware resource deficit. On UNIX, run SAR or some such, looking for evidence of swapping (not paging), cpu run queues greater than 1 (average) and IO average reads / writes greater than around 20-30 milliseconds. If all that seems reasonable, proceed. If not, fix the issue (add faster disk, more/faster cpu, memory, whatever), then test again.
  2. Install Statspack, run it at 30 minute intervals during periods of slow performance. Having done that, run a report for one of those periods and read it.
  3. Look at the top wait events. The top wait event is your worst bottleneck. If it is an IO event, that is pretty normal, but you might have contention on one or more files. See the files section of the report for an analysis of each file, look for "spikes" of average read/write times. Again, greater than 20-30 milliseconds is generally bad, but look for the worst cases there. Spread busy objects over different tablespaces / files to spread and smooth out the IO.
  4. If a non-IO wait event is on top, say "latch free" or "buffer busy", look to the detail sections of each and figure out what the worst latch or block type is. Measure bad latches by the most "sleeps", examine buffer busy by block type. Ignore ratios and such, focus in wait time and, in the case of latches, sleeps. (sleeps are actually wait events)
  5. Fix the worst bottleneck and get a new statspack report after the fix is applied.
  6. Repeat until performance is back to "normal", whatever you think normal means.

Post the reports here for qualified comments and remedial suggestions.

Obviously, performance analysis cannot be thoroughly explained this quickly, and my rules of thumb are rather broadly stated. Nevertheless, this is an effective method that works. It requires a bit of reading and research on your part, but it focusses on the worst bottleneck and avoids such black magic nonsense as "hit ratios" and "defragmentation".

A Wong wrote:
>
> what can I run to determine if the database is fragmented?
> I've used TOAD, and analysed the tablespaces, and it seems okay...but not
> sure how indepth this goes into everything... performance is slow, and I
> can't figure out why.
Received on Wed Jan 30 2002 - 09:05:24 CST

Original text of this message

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