but, can you be a little more detail and speak out your most 'exciting experience' and the most useful ref./doc. to be read ??

thanks a lot. 

Thats right, Really its nice outline,

That will be a great for us to understand the basis of tuning methods. Please share the details about your tuning experience where we can learn more from you.

Best,

Mohammed Yousuf

Sr.Oracle DBA ( Team Lead)

IBM, Bangalore.

India,

Start with the hardware.  Are drives fast enough, do you have enough memory, etc.

After hardware, tune the OS.  Do you have sufficient swap space, have you turned OFF disk cache, do you have enough processes, files, etc. set for the Oracle user

Next tune Oracle, tune your init file, looking at things like cursor sharing, index_cost_ parameters, and anything else in the init file that has a direct effect on the optimizer.  Do not forget to look at things like sufficient buffers, etc.

Finally, tune the sql statements.  To make sure you are tuning the correct statements, use statspak or AWR to give you a list of the top 5 statements and top 5 wait events.  Don't waste time trying to tune something that will only give you a marginal return, be sure of what needs to be tuned.

HTH

Peter 

Tuning should ALWAYS be performed in a top down approach.  This will give you the biggest bang for the buck.

Start with the hardware.  Are drives fast enough, do you have enough memory, etc.

After hardware, tune the OS.  Do you have sufficient swap space, have you turned OFF disk cache, do you have enough processes, files, etc. set for the Oracle user

Next tune Oracle, tune your init file, looking at things like cursor sharing, index_cost_ parameters, and anything else in the init file that has a direct effect on the optimizer.  Do not forget to look at things like sufficient buffers, etc.

Finally, tune the sql statements.  To make sure you are tuning the correct statements, use statspak or AWR to give you a list of the top 5 statements and top 5 wait events.  Don't waste time trying to tune something that will only give you a marginal return, be sure of what needs to be tuned.

HTH

Peter 

Most production systems over a period of time are most optimised in terms of hardware/cache etct etc...

Tuning init usually results in upping parameter values which in turns needs more resources and this causes a loop.

The biggest bang comes from tuning your SQl's- with SP /AWR  it is easy to isolate the most costly SQL's/ AWR also gives you an impact assesment for each SQL.

Once you have isolated your expensive SQL - then it would make sense you plan it / index / statistics / histograms et al to arrive at a reaonable cost.

But an top SQL from the AWR normally would not lead you to the right SQL for an particular instance - because the busninees group who is complaining about performance might be using a different one. Talk to them first, see their business rules, see if you can isolate their problem SQL and then try to tune it

If you are not tuning the right SQl then any benefits you derieve are useless.

hotsos profiling helps. Extended tracing is crucial; but the trick is to isolate the problem SQL first.

Nizar

 Newbie User95935 is right. If you find the best statistics for your objects, (which sometimes means no statistics at all), you will improve times for the all the sql and not only one sql... In my experience, I get better performance with less estimate percent when using DBMS_STATS in 9i and 10g (sometimes I generate stats with estimate of 5 and get better times), and surprisingly in 11g too, starting with 20 - 25 estimate percent... now I am testing to generate histograms only in columns that are part of an equijoin...

I don't like profiles, I think oracle by itself is enough smart to find out the best execution plan and some dba's are too scared from full scan tables, you just give oracle the right stats...

Regards.