Tuning doctor was called. What is his plan?

[newbie5]

A client reports that his database is running very slow. The queries take too long. Creating of indexes on suspected queries seems to make the performance worse. The database server is on its last legs about to collapse anytime. Tuning doctor was called.

How to go about tuning the database? Where to look? What are the first steps? run statspack? What next? Any readymade scripts? Any road map to tuning is appreciated.

[rajabalachandra]

Originally posted by newbie5
The queries take too long.

What are the first steps?

check what is the execution plan?

-Raja

[DaPi]

The manual includes a "road map" - worth thinking about even if you decide to skip a lot of the steps.
http://www.csee.umbc.edu/help/oracle...a67775/toc.htm

[newbie5]

I read the 9.2 manual side to side. However, I believe reading a manual and try to tune first time is like trying to prepare a dinner for 100 guests from a recipe book or attempting brain surgery from a step-by-step instruction manual.

This requires finesse that can come only from previous experience. I will take my chances with metalink but I am looking for more learned suggestions.

[tamilselvan]

After killing 100 patients, a person becomes doctor.

Tamil

[DaPi]

Originally posted by newbie5
. . . or attempting brain surgery from a step-by-step instruction manual.

Is there any other way?


I'm sure I can't do any better than the manual, except to summarise:

- be methodical, keep good records of action & result.
- analyse (that means to break up into smaller pieces - opposite of synthesise), "tuning the db" is too big for my small brain.
- gather evidence, statspack, user complaints etc.
- prioritise, go for whatever is hurting the most.
- make your own simple tools (test cases, benchmarks) tkprof will tell you what they are doing.
- prepare to be wrong, esp. recognising if you're in a dead-end.

- let us know how you get on.

[hacketta1]

My suggestion in approaching this would be try and take one poorly performing query at a time. Don't try and find a "cure all" for everything; there might not be one. Find out from the users what the top, say 5, most time critical queries are and then perform a 10046 trace for each one in turn. Use Trace Analyzer to analyze the trace file and find out where all the time is going, and then try and eliminate that time spent waiting. This might be SQL tuning, or it might be tuning system wide parameters. Once one query is dealt with then you may have the good luck of a resolving an issue that was effecting all the other queries, but then again you might not. So move onto the next...

You can download Trace Analyzer from

here.

To perform a 10046 trace find the spid with the following query:

SELECT p.spid
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.username = 'USER YOU WANT TO TRACE'

alter system set max_dump_file_size = unlimited;
oradebug setospid SPID FROM QUERY ABOVE;
oradebug unlimit;
oradebug event 10046 trace name context forever, level 12;

The resulting trace file will appear in user_dump_dest and can be analyzed by Trace Analyzer.

IMHO, I'd avoid the temptation to concern yourself with systemwide stats, just focus on the individual user actions.

[newbie5]

Thanks hacketta1. I have one week to tune a beast of the database that is running a java app on linux with multiple tables having more than 50 million rows. I will start with the queries. I will also look at shared pool and I/O issues and go from there.

I will keep this post updated with feedback.

[hacketta1]

I will also look at shared pool and I/O issues and go from there

If these are the problem then you'll find this out when you trace your queries. It can be very difficult to determine whether a problem exists when you just look at these things in isolation. Worse still, you might spend a lot of time tuning something that has little tangible benefit for end users. Go wherever the trace files take you!

[marist89]

Ever heard of statspack?