Skip to main content

The art and science of Oracle DB performance tuning

Back to square one, where I started this blog ? ...

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.

  1. Talk to the user who is facing the problem, to clarify any of the above inputs
  2. Install & run STATSPACK on the concerned Oracle database. By default, spauto.sql schedules the snapshots hourly.
  3. STATSPACK would take some time to provide some useful report. Meanwhile, look at the other aspects below.
  4. 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".
  5. Analyze the database objects involved in the above key areas, for appropriate indexing, fresh statistics.
  6. Look at the explain plan if there's already a SQL code pointed out , otherwise perform this step after you get STATSPACK report.
  7. Start tracing for the schema(s) which the users use to connect to the database.
  8. Have the user run their operation once, to make sure it is captured in STATSPACK & the trace.
  9. 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).
  10. Format the recent trace (with tkprof utility), generated after the trial of the concerned operation by the user.
  11. 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).
  12. 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 ...

Popular posts from this blog

Permitting a user on UNIX / Linux to use sqlplus

We encountered an interesting scenario worth mentioning.

Oracle is installed on a Linux server newly & another ftp user wants to just use sqlplus also.Just setting the ORACLE_HOME, LD_LIBRARY_PATH, ORACLE_SID, PATH in the user's profile did not let him run the "sqlplus" command at all. The error shown was :
$ sqlplus sqlplus: fatal: /app/oracle/product/10.2/lib/ Permission
The files under "lib" directory under home didn't have permission for anybody other than "oracle" user. -rwxr-x--- 1 oracle oinstall 1320016 Jun 28 2005 /app/oracle/product/10.2/lib/
When we permit the other users (other than oracle) to read/execute…

HWM (high watermark) of a tablespace ?

How many times have you thought of calculating HWM (high watermark) of a tablespace as a DBA ? Have you ever found out that a tablespace is overallocated in a database, as compared to the data being used in it ? In such a case, you'd want to reduce the wasted space & resize the tablespace to a smaller size, although greater than the current usage.
HWM is at the maximum blocks ever occupied by a SEGMENT (unless we perform a truncate on it). Segments span over datafiles however remain within a tablespace. All segments are physically allocated by way of extents, which are contiguous blocks of data. Now, when we talk about contiguous blocks, 2 datafiles (even from same tablespace) are highly unlikely to have contiguguos data blocks. Hence, theoretically, it's possible to calculate high watermark within a datafile, not a tablespace directly, as the latter is only a logical unit. Although, we can resize a tablespace, however the resize value needs to be based on exact calculati…

Intereseting --- 249 days bug for Oracle on Linux servers

--contributed by Manjunath Ulthi, Senior DBA at Infosys Technologies limited

1 Introduction

Problem: OCI client spins when machine uptime >= 249 days in Linux machines. Once the uptime crosses 249 days the database client hangs and leads to high CPU utilization and server hung state. The user will not be able to connect to the databases This is oracle known bug 4612267.

Solution: This can be handled by applying the patch 4612267.

The patch has been applied in the servers server126, server041 & server120 and tested. After the patch has been applied the client connections were normal after 249 days and all the operations of databases/applications were normal and didn’t encounter any problems.

For further information refer the Metalink Notes

Note: 338461.1: SQL*Plus With Instant Client Hangs, When System Uptime Is More Than 248 Days

Note:4612267.8 : Bug 4612267 - OCI client spins when machine uptime >= 249 days

2 General Instructions

Procedure to apply the patch

1. Download the …