Tuning - where to start

[goodhealth]

To Tuning Experts,

In general if databases run slowly, what will be some of the things to start looking into? My initial thought will be Unix monitoring, running bstat and estat, check init.ora parameters(which parameters worth tuning?), explain plan etc. Can some of you share some real life tuning experience?

[sreddy]

buffer cache,library cache, redolog, rbs, sort area, i/o contention, fragmentation, to name few but not limited to. It goes with where you have problem and what to fix and depends on the resources available and kinda activity on the database.

[launchpad]

hi , i think the best thing to start for tuning is the typical users .they are an invaluable feedback ina way system is working .. tune their sql codes first before delving into the details .then you look for the contention in memory (buffers/adaquete redo logs etc ..), and check out whether you needa have partitioning of tables/indexes in order to speed up the performance .you can always have an eye on your hardware in order to gear up with upcoming issues .
hope that helps ..

[mbaliga]

Hi,

First I look at the memory allocation to various components such as OS, DB, Application, Other tools etc. I have found that these areas are handled by different groups and you tend to overallocate memory thus resulting in heavy paging at OS level.

Having got the memory allocation for DB, then you can size your SGA, PGA and latches. Then look at RBS & Temp usage. Tune your sort areas. Finally look at the database layout and improve IO bottlenecks.

Look at the application and optimize SQL queries.

At the OS level look at Asynch IO configuration, UNIX file cache etc.

Hope this helps.

Baliga

[Halo]

Sometimes network bottleneck can cause problems too.

[apfaunts]

Start by getting a good book :-)
Oracle Performance Tunning Tips and Techniques. By Oracle Press. ISBN 0-07-882434-6
It will give answers and scripts:
Page 4 starts Five Quick Goals to Instantly Improve Performance. It states what previous answers already told you but will also explain why and will give setting, scripts and parameters to start with.

[yrajesh]

Hi,
The best place to start tuning is,of course,the sql code.This is what gives maximum results when compared to all other areas of tuning.However, a look into the various buffer caches and os parameters would also help.

Rajesh.

[Mr.Hanky]

Just remeber D'AMICO. Sure it's an italian last name but according to Oracle ILT materieal you tune in order.
Design (usually too late for this one)
Application (SQL apps)
Memory (SGA, ect..)
In & Out
Contention
Operating System

I also reccomend the oracle press Performance Tuning book bt Richard Niemiec.

[url]http://www.bookpool.com[/url]

Good Luck MH