Re: oracle 8.1.5 performance problems

"tractor boy" <gavin_balaam_at_yahoo.co.uk> wrote in message news:17048108.0401270345.539a0832_at_posting.google.com...
> I have a problem with an Oracle 8.1.5 database in that
> recently the database seems to be running very slowly.
>
> I have tried
> 1) rebuild indexes
> 2) Analaysis NT performance
> 3) checking fragmentation
>
> But it would seem that connecting, selecting data is
> slow. I spent a couple of days on the net trying find
> solutions but have not had much success.
>
> Currently exporting a database is horrendously slow,
> and the tables returned are in a random order.
>
> I have also has suggested that maybe the sys user is
> casuing problems.
>
> How can I find out if the sys user is causing
> problems, and how do I fix it?
>
> Any help or guidance would be greatly appreciated

Hi TB,

This is such a "classic" case of tuning on a dark night, in a dark room, blindfolded and with your eyes closed. This method is still *the* method of choice by many DBAs and *the* method documented in various (so-called) tuning books.

System runs slow, Ummmmm what to do;

Ummmmmmm, I know, rebuild all my indexes, that must help 'cause it says so in this book.....

One day later, all indexes rebuilt, no good, performance still crap.

Ummmmmmm, I know, rebuild all my tables into one extent, that must help 'cause it says so in this book ....

Several days later, all tables down to one extent, no good, performance still crap.

Ummmmmmm, I know, increase my buffer hit rate, that must help 'cause it says so in this book .....

Next day, buffer cache increased, no good, performance still crap.

Ummmmmmm, I know, upgrade to a new 28 CPU, 100G, shiny silver shaded box with cute little flashing green lights, that must help 'cause I've run out of suggestions in this book ...

Next month, 1 million dollars later, no good, performance still crap.

You get my point.

Rather than shooting blind and occasionally getting "lucky" (which is sometimes the "worst" thing to happen because then one thinks that lucky shot is always the right "cure"), rather than *guessing* what *might* be causing the performance issue, why not *determine* exactly what *is* causing you the issues.

It kinda makes sense when you think about it. So rather than guessing, or listening to other peoples guesses, why not actually find out.

And the easiest and most accurate way of determining this is by tracing a session that is causing you grief with the good old 10046 event and find out *exactly* what you are waiting on:

alter session set events '10046 trace name context forever, level 12; or
dbms_support.start_trace_in_session(:sid, :serial, waits=>true, binds=>true) or
dbms_system.set_ev(:sid, :serial, 10046, 12, '')

The *answer* to your problem is found in the generated trace files because the reason why it's taking so long is because it's either busy doing "something" or it's busy waiting for "something". Find out what those "somethings" are and you know "why" it's taking so long. Once you know why it's taking so long, you'll (hopefully) know *what* to do to alleviate these somethings or at least be in a position to do something about it (no pun intended).

For any lurkers out there in Oracle Land, please learn to stop guessing and learn to start solving. Tuning a database is not some lucky dip contest where the "prize" is some mysterious secret that requires a series of "let me 'ave a go, I got an idea that might do the trick" or a tuning checklist you sequentially go through in the hope you'll eventually get to uncover the secret.

It's starts with finding out what's actually going on and that starts out by tracing a session that is important to your business requirements and that is experiencing performance issues.

1
0
0
4
6

Cheers

Richard

Good luck !!

Richard Received on Tue Jan 27 2004 - 06:55:57 CST