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.
- 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.
- 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.
- 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.
- 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)
- Fix the worst bottleneck and get a new statspack report after the fix
is applied.
- 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