Analyzing database performance on Oracle is quite a complex issue. In dbWatch Control Center, our primary aim is first to provide structure and toolsets to avoid running into performance problems in the first place.
Consistency in deploying preventative maintenance routines should take care of many problems that occur over time, such as index and table fragmentation. Bad statistics are a foundation that will ensure we don't waste time investigating preventable causes.
As most DBAs will know, unexpected performance issues can occur even with the best foundation, and we will need to be able to investigate the underlying causes.
For this reason, dbWatch Control Center has a lot of monitoring jobs focused on monitoring the behavior of different performance metrics. At its core, all performance issues occur when resource limits are met. It can be the CPU, disk, memory, or internal database limits.
The first we need to do to start our investigation is to find the time window where the performance issues occurred. The typical problem is that one or more SQL statements cause an overload of the database resources in the time period in which they were running. In this blog, we will briefly investigate performance issues due to SQL statements.
In the SQL Performance section in the management module of dbWatch Control Center, you can look at historical resource usage, together with the SQL statements that were performed in that time period.
In this section, we can see spikes in logical/physical reads and writes. This often, but not always, coincide with performance loads. We can also look at other metrics in the same time frame, such as Time, the elapsed time for all queries in a time frame, which could indicate slow query response.
The Application waits could indicate spikes where the sessions are waiting on user application code, such as locks. Concurrency waits for when the sessions are waiting on internal database resources, such as latches. Cluster waits are when the sessions are waiting for RAC resources, such as global cache resources. User I/O waits are waiting for user io, such as reading or writing data files.
The session graph show session activity and load in the timeframe, and all the other waits, such as administrative, commit, configuration, idle, network, other, scheduler, and system io. They are less common to be the problem and thus grouped. Seeing all these metrics in the same timeframe as the SQLs are executing gives an excellent indication of what SQL statements are causing and what the underlying issue was.
Once a peak has been identified, you can use the mouse to zoom in on the time period.
The SQL performance dashboard will then zoom in, and looking at the SQL handle statistics, it's easy to see what SQL handle is responsible for this peak.
You can then right-click and select show statistics, to open the SQL handle and view the SQL code, statistics, and when the SQL statement is inside that SQL handle.
In this example, we saw the peak was caused by statistics gathering.
If we are interested in the SQL statement query plan for one of the statements, you can right-click on the statement and select "Show query plan."
This shows us how the query plan is for these statements and could help us identify bottlenecks, such as Table access full-on tables that cause high costs. This can sometimes be fixed by creating indexes that help the query access the table.
The SQL Performance module in dbWatch Control Center allows us to quickly see what is going on and find the select statement or procedure that leads to a high load on the database instance.
If this was interesting, we go more in-depth on the SQL performance dashboard on the wiki page, wiki.dbwatch.com (https://wiki.dbwatch.com/ControlCenter/1.0/en/topic/using-sql-performance-package
With dbWatch Control Center, Oracle DBAs can now monitor their resource usage trend and analyze the peak hours in their database environment based on available charts.
dbWatch Control Center assists Oracle DBAs in efficiently running their databases, from proactive database performance monitoring to managing Oracle tasks.
Control Center also helps Oracle DBAs play back their historical performance to drill down and pinpoint problematic queries during the past few weeks.
Control Center offers the following:
- Farm overview - consolidated views for performance, capacity, and maintenance for your entire database farm
- Monitor in-depth all your instances and platforms
- Performance – identify performance bottlenecks within the farm
- Scalability – ability to adapt to your growing database farm
- Alerting – alerting and third-party extensions tailored to your business requirements
- Security – role-based access controls, Active Directory, and Kerberos integration encrypts connections and support security certificates
You can try dbWatch Control Center today. Use this link to download a version of Control Center that can monitor up to 5 instances for free: Download dbWatch Control Center
If you have any questions or you would like to know more about how dbWatch Control Center can assist you in your current enterprise database monitoring and database management situation, feel free to contact me directly at firstname.lastname@example.org
For more information, visit www.dbWatch.com or the dbWatch wiki pages
#DatabaseMonitoring #Oracle #OracleDatabaseMonitoring #DatabasePerformance #OracleDatabasePerformance #DatabaseManagement #OracleDBA