<Icon> Name of Role
A Transaction is Running Very Slowly (Oracle)
As a database administrator, end users will often ask you to investigate why a particular transaction or set of transactions are running slow. There are many factors to take into account when tracking down these types of problems. As this search may take a considerable amount of your time, you should gather as much background information as possible from the responsible parties before you begin.
Questions you should ask the end users include:
- Has the transaction always been slow or did you only recently notice the slowdown?
- Is it a new program or transaction?
- Is the slowdown only during peak periods or is it fairly constant?
- Has the user workload changed recently?
- Does it appear that just this one transaction is slow or are other transactions/applications also now performing poorly?
Having gathered this information, the DBA can try to identify where the performance bottleneck resides. Keep in mind that performance tuning is an iterative process and you will probably have to involve the end users at some point to determine whether the tuning steps you have taken have alleviated their problems.
If you feel this issue may be isolated to one particular transaction, program or application, you may also need assistance from the application developers. They will better understand the process flow of the application and can help to change and test statements in the program. It is not unusual for a program to function correctly in a development environment and then to perform poorly in production. A production system frequently has more data and more users than a test system.
There are three major areas which you should check when the above symptoms are reported.
- Check for poorly programmed SQL statements which are utilizing a disproportionate amount of system resources, sometimes causing other transactions to slow down as well.
Monitoring the shared SQL area (Oracle) Monitoring the Shared SQL Area (Oracle)
- Find out whether exclusive lockwait situations have occurred, in which one or more processes are competing for the same object.