Re: database slow

On 27 Nov 2004 23:27:43 -0800, n_kselva_at_yahoo.com (Kumar) wrote:

>Hello friends,
>
>I am a startup dba.I would like to know where do i start with incase
>my users complain of slow database.
>
>where to check first?. I know there exists a statspack report too.But
>i am confused what could be first step and how to drill down?.Could
>any of you give me some detailed hint.
>
>Thanks in advance for your help.
>
>
>Regards,
>Kumar.

Hi Kumar,

You are on the road of the noble art of system tuning. So here goes :

1)Your users complain of a slow database. How do they know it's the database ? Isn't it just something they assume (it's common to blame the database)? But it could be the client, the network or a dozen other things.

2)So now you've checked all these things and let's assume you fond out it's the database (and not the butler).

3)We know by experience that 90% of possible performance gain is fetched from the application ( badly written SQL and lacking indexes, unnecessary sorts etc). When did the slow response start ? Any new application release?

4)Consult the programmers about these things, and when they appear not to remember anything, being busy with other things or having left the company, you use statspack or other tools to find the most resource consuming SQL-statements. Tune these with indexes in right places etc.

5)Now the users will probably already stop complaining. If not, you go on tuning the db server in accordance with the fine guidelines in Oracle online doc. Statspack is

6) Having done this and result is still not satisfying, you may move to the next step : Upgrade the hardware.

7)If you learn to follow these steps, call yourself a tuning expert and go for a raise , because most companies do the things in exactly the opposite and (very expensive) order.

• Kenneth Koenraadt