<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=204641&amp;fmt=gif">

A DBA's guide to monitoring Oracle database performance

Posted by Chad Pabalan on Jun 7, 2022 4:51:42 AM
About the Author: 

Chad Pabalan is a Pre-Sales Engineer for dbWatch and a DBA specializing in SQLServer high availability setups and disaster recovery planning and configurations. He is an AWS Certified Solutions Architect Professional, a cloud enthusiast specializing in architecture and designing scalable, high available, and fault-tolerant systems on AWS Cloud. 

 
1-2

 

Performance tuning in Oracle is a repetitive process of searching for and identifying  bottlenecks in your Oracle database. After identifying the culprit of the Oracle performance issue, you resolve it by modifying and optimizing the problematic query or adding indexes, if is necessary. 

As an Oracle database administrator, firefighting is one of your tasks, and it is your duty to 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 that you should be focusing on.  

 

What are the key performance metrics that you should watch out for?  

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

 

1. CPU Usage/Load

 

 

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

 

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 all 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 in monitoring 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, which is 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 are being executed. The 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 which 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 – which are 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 important 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 can analyze how your Oracle database is performing. 

   

Try it now!  dbWatch Control Center free license     

   

If you have any questions or you would like to know more on how dbWatch can assist you in your current enterprise database monitoring and management situation, feel free to contact me directly:  chadwick@dbwatch.com  or email presales@dbWatch.com  

    

For more information, visit www.dbWatch.com or the dbWatch wiki pages    

   

#OracleDatabaseMonitoring #OracleDatabasePerformance #OracleDatabaseOptimization #DatabaseOptimization #PerformanceTuningInOracle  #OracleDatabase  

 

----------------------

Other blogs: 

Working with MySQL vs MariaDB - What's different?

Why do Developers Automate Database Processes?

 

 

 

Topics: database monitoring, sqlmonitor, sqlperformance, sqlmonitoring, indexmaintenance