Analyzing your Oracle Database Performance

oracle database performance
Oracle database performance analysis and tuning can be quite a complex subject. This blog will teach you the most common culprits that can affect your Oracle database performance. 

In a typical Oracle database environment, the usual problems that an Oracle database administrator encounters daily are long-running queries, multiple blocking processes and deadlock transactions.  They may also face high resource consumption of a query that causes performance degradation, and other causes such as poor query design and poor index usage. 

oracle database performance

Productive Oracle DBAs, use a good proactive Oracle database monitoring solution in and work to analyze the performance of your Oracle databases, identify the root cause of the problem and respond with changes to improve the process/query that caused the performance degradation. 

In this example, we will use dbWatch Control Center to demonstrate how Oracle DBAs, like you, can quickly analyze those different factors mentioned that cause impact in your environment; using the different performance counters and statistics available; you can form a solution to improve poor performing processes. 

1. Oracle Long-running Queries

One of the most common problems that Oracle database administrators encounter daily is long-running queries. Here you can view currently running queries by going to the instance you want to monitor.

Under the management module, choose the instance. Expand performance, and select active sessions. (Figure 1)

Showing the instances with long-running queries in an Oracle Database.
Figure 1

The view displays the active processes where it shows the initial login time of the query or the start time. To view, the SQL explain plan and other statistical information, right-click on the session and choose the information you wish to see.

You can also check if any long-running queries happened during the day or week by opening the long-running queries job. (Figure 2)

Checking when long-running queries occur in Oracle.
Figure 2

It shows you information about the SQL query that was executing at that moment in time. (Figure 3)

Report of a long running queries in SQL
Figure 3

Historic Long-running Query Visualization

dbWatch Control Center (CC) also allows Oracle DBAs to look at issues from today, last week, or last month using the Oracle performance pack feature.  

CC provides a historical visualization of what queries have been performing poorly in the past few weeks. You can drill down and select the specific time stamp where the problematic query has been executed by pointing to the line graph. 

Historic view of a long-running query.
Figure 4

Working with Query Statistics

Oracle DBAs can also look at query statistics by expanding the SQL menu on the left side. It will display the top 20 queries by elapsed time, average IO, CPU, or executions. (Figure 5)

expanding the SQL menu to see statistics
Figure 5

Filter out the top processes based on their query text, execution count, max elapsed time, average elapsed time, and the log create date.   

With the following data available, Oracle database administrators’ will have a good idea about which query needs to be optimized based on its performance regarding its execution time and resource consumption.   

2. Multiple Blocking Processes

Another problem that Oracle DBAs encounter is multiple blocking processes.  

In this example, we will simulate a blocking transaction in a test machine.


We have a simple insert statement :

Execute the query: 

We now have an active blocking process.

Using dbWatch Control Center, you can view blocking processes within your Oracle database. Go to the management module. Under the performance option, choose the blocking session. 

This view shows the simulated blocking process; it displays various information regarding the processes that block each other. We have the session ID, username/OS user, blocker, status, command, and additional relevant information. If you right-click the session, you have an option to kill the process and view the SQL query of the session.   

How to view the SQL query of a session
Figure 6

To view the blocking history of your instance   

Go to the Monitoring module. Select instance(P001-ORCL19C) and choose performance. Select the blocking detector, right-click and select details.

Figure 7
The blocking detector
Figure 8

This window shows additional information about the previous blocking processes.   

It displays the process which was blocked and the process that blocked it, session history login info, program name, database name, and SQL command for both processes.   

With the detailed report coming from the dbWatch Control Center monitor, Oracle DBAs can act to optimize queries that frequently block other processes to avoid multiple blocking scenarios that can cause database timeouts in the future.   

And last on the list is CPU Intensive queries; usingdbWatch Control Center, DBAs can analyze - the CPU usage trend and determine when the instance is at its peak regarding resource usage.    

Go to the Monitoring module and choose an instance. Expand performance, right-click CPU Load to view more information regarding the instance CPU peak hours.  

view instance CPU peak hours
Figure 9
The Aggregate CPU usage view
Figure 10

This window shows the CPU utilization history chart , which shows CPU usage for the last 2 hours, aggregated CPU usage divided into CPU Max Night Hours between 12midnight to 8 am, CPU max working hours between 8 am to 4 pm, CPU Max evening hours between 4 pm to 12midnight.   


With dbWatch Control Center (CC), Oracle DBAs can now monitor their resource usage trend and analyze the peak hours in their database environment based on available charts.   

(CC) assists Oracle DBAs in efficiently running their databases, from proactive database performance monitoring to managing Oracle tasks. 

(CC) also helps Oracle DBAs play back their historical performance to drill down and pinpoint problematic queries during the past few weeks. 


Try Control Center today, download a trial version of dbWatch that monitors up to 5 instances for 6 months. 


DBA’s Guide: Monitor Oracle Database Performance

oracle database performance
UPDATE:  dbWatch Control Center now has additional advanced performance tools. Read about these the updated blog:  “Analyzing Oracle  performance with the new performance tools in dbWatch Control Center”. 
Performance tuning in Oracle is a repetitive process of searching for and identifying bottlenecks in your Oracle database.
After you identify the culprit of the performance issue, you can fix it by optimizing the problem query or adding indexes.

As an Oracle database administrator, firefighting is one of your tasks, and you must prevent fire from happening in the first place if you can.  

As a responsible and proactive Oracle database administrator, you should put in place “alerts and notifications” to keep track of the key performance metrics you should focus on.  

What are the key performance metrics needed in Oracle?  

Here is a list of performance statistics that you should track:  

1. CPU Usage/Load

The image below shows the query we executed in dbWatch Control Centers’ SQL worksheet and your database’s top CPU usage per session.  

As an example, below is the dbWatch Control Center monitoring module, where you can select the CPU usage performance job to find out if there are any unusual spikes in your CPU usage throughout the day or week.  

You can configure this job to send you an alarm if a certain threshold has been reached. Proactive monitoring keeps alerts in place and is essential to your Oracle database.  

2. Long-Running Queries

To find long-running queries executing in your Oracle database, you can execute the following command:  

 In the monitoring module of dbWatch Control Center, you have your “long-running queries” job that you can configure to help you get notified if a query is running longer than it should.  

3. User memory usage rate  

To identify the top memory usage rate per session, execute the command below:  

dbWatch Control Center has a job monitoring feature for your memory usage, also called the “Top user memory usage” job. This report helps you determine if there is an abnormal memory usage spike throughout the working day 

This report also shows information about the specific session ID, memory usage, and the SQL statement that is being executed. A report such as this is beneficial when performing your query optimization, as you know where to start fixing the root cause of your performance issues.  

4. Blocking Sessions

Long-running queries can cause blocking sessions in our databases, especially, if a query from one session is trying to access the resource of an object in another session that is being locked for too long because of an operation such as updating of table data. Execute this query to find out if any blocking session is happening in your database:   

In the monitoring module, you have a job named Blocking detector, where you will find more details about the blocking session along with the username and type of lock – the blocker and the waiter to kill the session.   

This job is also configurable so that you can set the max threshold of the blocking session duration. If the duration threshold has been reached, you will get notified through email or SMS.   

5. Wait Statistics

One of the most critical performance statistics is your Oracle “Wait Statistics” job. As an Oracle DBA, you need to identify if there is any performance degradation while you check for the wait statistics, as it gives information for slow response time in your system. 

This report shows you a detailed chart of your different Oracle “wait” types so that you will see if there is a sudden unusual spike in your wait.  

With dbWatch Control Center, you can now keep track of Oracle database performance and analyze how your Oracle database is performing. 

UPDATE: Since we published this blog, we have added new advanced performance tools to dbWatch Control Center. You can find out more about these in this blog: “Analyzing Oracle  performance with the new performance tools in dbWatch Control Center”

Understand more about database management, book a demo today.