Can someone list the steps to undertake a perf tuning process of a
database thats running slow. If you have any code snippets or tools to
diagnose that would hel me tremendously too.
Oracle's tuning methodology is a ten-step process:
1. Tuning the business rules
2. Tuning the data design
3. Tuning the application design
4. Tuning the logical structure of the database
5. Tuning database operations
6. Tuning the access paths
7. Tuning memory allocation
8. Tuning I/O and physical structure
9. Tuning resource contention
10. Tuning the underlying platform(s)
Any one of these areas can affect other areas. Where exactly would you like me to start?
Looks like our problem is with the sql. What steps do i need to take for sql tuning? This is a web application. Thanks...
All the resources that you are interested in
There are a lot of different aspects that could result in database slow down.
For SQL tuning, some of the steps that could be taken are:
1. Check contention for db_blocks.
2. Buffer Cache Hit Ratio
3. SGA memory usage, may be you need to increase shared_pool_size
4. Make sure you have optimizer_mode = CHOOSE
5. Tables and indexes should be analyzed.
6. Generate explain plans for SQL statements and try to tune them, to reduce cost, force SQL to use indexes and avoid full table scans.
You will find a usefull scripts at this URL http://www.think-forward.com/sqltips.htm[/url]
Click Here to Expand Forum to Full Width