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.
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)
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)
It shows you information about the SQL query that was executing at that moment in time. (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.
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)
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.
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.
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; using dbWatch 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.
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.
