Re: Tuning approach question

On 09/24/2005 12:37:36 AM, Orlando L wrote:
> Hi Oracle Gurus,
>
> I am learning DBA work. I went to an interview recently. I was asked how I
> would a tune a database if it was running slow, also assume that there was
> no I/O contention. ie, A whole application with multiple users is running
> slow and I have to tune it. He wanted to know how I would figure out the
> problem.

Fair enough. I also ask such questions. The real question goes like this: An end-user contacts you with the complaint that the "database is slow". What do you do next?

>
> Here is my approach to it:
>
> 1) Check the SQLs and the Oracle processes running through Enterprise
> manager or something similar; Look for long running or odd processes. Will
> any of them make the whole thing slower? If so How do I find out?

First of all, how do you know that the problem is in SQL? Your user might be connected to a bad port on the local ethernet switch. It happened to me. The first thing to do is to identify and isolate user's session. There are many tools to do so, but sometimes, it is downright impossible. The developers should be helping you by putting calls to DBMS_APPLICATION_INFO into the application. Another thing to do is to figure out what is user trying to do and get SID from V$ACCESS table. On Unix, you can trace processes through sockets (using netstat) but that is an uncommon form of black magic. Once you identified the session or group of sessions to observe, you do the following things: 1) Immediately turn on event 10046, level 8 on all of the sessions in question.

   You can use oradebug, DBMS_SUPPORT or DBMS_MONITOR to do that. 2) Take a look at V$SESSION_WAIT and V$SESSION_EVENT for the given sessions

   and see what are they waiting for. It may be SQL and it may not be SQL. 3) Always take a look at your OS to see how hard it works. You should have monitoring,

   with "sar" for instance, turned on while the database server is in operation.    Significant amount of kernel time usually signifies paging. Check log files for    messages about device problems or spurious interrupts. 4) If the user is waiting on db scattered/sequential read, you have a SQL to tune.

   If user is waiting for log file switch, increase redo log files and if there is    something else, tune appropriately.

Basically, you always need to find out where is the time lost, what is the user waiting for. You are tuning response time, not an oracle instance. All Oracle instances run perfectly until you add users and data. They are the source of all problems. Oracle instances seldomly complain, users do that instead. The way of making users happy has something to do with reducing the time they have to wait for their data and has little or nothing to do with buffer cache hit ratio or similar indicators of IT superstition. It is the response time! You don't jump into SQL, and start observing which queries use indexes and which do not use indexes (very popular mambo jumbo among the junior DBA staff) you first determine what the user is waiting for. Then you write a Perl script to fix it.

-- 
Mladen Gogala
http://www.mgogala.com


--
http://www.freelists.org/webpage/oracle-l