Nooooo ... Here I am not providing a step by step procedure for Oracle performance tuning, however a framework to help a DBA with tackling Oracle performance issues. This could be seen as a reference to understand where to start, what information to collect, where to proceed, how to conclude, etc. .
Without much ado, let's go ahead.
What's the problem ?
Some of us (DBA's) get bad performance notice from users & we might tend to directly jump into trying to find a solution. Instead of trying that, a better way to do that is to first find out what that problem is & how is it seen by the user. First, start with these questions asking the requestor :
1 Is the whole application slow or only some modules are slow ?
2. When did you first notice this problem ?
3. Is the problem noticed all of sudden? Is it gradual ?
4. Is it recurring as you mentioned ?
5. Is there any recent upgrade to application ?
6. Is there any substantial increase in data ?
7. Is there any increase in number of concurrent users ?
8. Is there any increase in total number of users ?
8. Define the problem in your own words.
9. Suppose if we find the certain module of the application is causing the problem, Is it possible to change that module ?
10. Any other comments about the problem ?
PS : I recently got a request to bounce (/ restart) a PRODuction database from a user, stating that the DB was down. When I spoke to him, I discovered that just because he was not able to connect to his application & see anything, he thought the Oracle database behind his application, was down & needed a bounce. I already knew that the database was up & fine, I asked him to speak to the Application team. It was discovered as an application issue, never to be handled by a DBA :-)
Tackle the problem now
Once the problem is clear & the DBA knows that there is a real issue, one may follow these sure-shot steps. You are welcome to follow your own strategy & resolve the issue. If anyone has some strategy to go about performance issues, please share your way of doing things, through comments on this post. I will incorporate them here, as your contribution.
- Talk to the user who is facing the problem, to clarify any of the above inputs
- Install & run STATSPACK on the concerned Oracle database. By default, spauto.sql schedules the snapshots hourly.
- STATSPACK would take some time to provide some useful report. Meanwhile, look at the other aspects below.
- If the user knows some areas / schema / SQL / PL/SQL code areas of the database, which might be causing trouble, look at them once. Understand from the user, the database logic used in their "problem operation".
- Analyze the database objects involved in the above key areas, for appropriate indexing, fresh statistics.
- Look at the explain plan if there's already a SQL code pointed out , otherwise perform this step after you get STATSPACK report.
- Start tracing for the schema(s) which the users use to connect to the database.
- Have the user run their operation once, to make sure it is captured in STATSPACK & the trace.
- Generate the STATSPACK report & use my "STATSPACK post" , the "Survival guide" or a statspack analysis tool to see it in a more understandable format with recommendations too (this tool is not necessary, however, it is helpful).
- Format the recent trace (with tkprof utility), generated after the trial of the concerned operation by the user.
- Analyze the above 2 reports & find out the issues / areas to be focussed on. STATSPACK report can list out top SQL's / PL/SQL areas of the database. We can start your real, concentrated analysis here. You may repeat steps 4, 5, 6, 7 again if required (most of the times, they will be).
- If the problem area is identified as PL/SQL code, it's best to use DBMS_PROFILER to find out the problem line(s) of code. You may use "Oracle official area" to know how to use DBMS_PROFILER or try the database journal (you may just enter DBMS_PROFILER in a search engine & get 100's of results on how to use it). DBMS_PROFILER needs to be installed first as SYS user (profload.sql) & then some objects are created in the concerned schema through schema login (proftab.sql).
Let me know your case-studies - I'll post them here too, along with mine. More on tuning later ...