The latest cloud infrastructure announcements, technical solutions, and enterprise cloud insights.
-
Product News
March 16, 2021
Available now: Performance Hub for on-premises databases
We’re excited to announce the immediate availability of Performance Hub in Oracle Cloud Infrastructure (OCI) Database Management service for on-premises Databases. This feature allows you to have a single view of the database performance and perform the rapid diagnosis of its problems.
What is Performance Hub?
Performance Hub provides a consolidated view of Oracle Database performance data for a given time range. You can select a time range using a time picker at the top of the page. The detail tabs display the available performance data for the selected time range. Performance Hub is designed in a master-detail format, where the time picker at the top of the page drives the contents of the details displayed in the tabs. The time picker displays average active sessions over time, and if the time picker shows peaks, you can move the selected time range to the period of interest to get more information.
Performance Hub provides holistic performance management capabilities providing a single view of the database performance using a varied set of features, such as Active Session History Analytics, Real-time SQL Monitoring, Automatic Database Diagnostics Monitoring (ADDM), Blocking sessions, and so on.
Figure 1: Performance Hub Components
In this blog, we look at how to identify application performance issues using Performance Hub features.
ASH Analytics
Active Session History Analytics (ASH) Analytics allows you to analyze and resolve transient performance problems that can last for only a short period, such as why a particular job or session is not responding when the rest of the instance is performing as usual. It also allows you to perform scoped and targeted analysis by various dimensions and their combinations, such as time, session, module, action, or SQL identifier.
ASH Analytics gives you the following capabilities:
-
Filter and analyze database activity interactively along various dimensions on the same page
-
Identify top pluggable databases being throttled because of CPU contention, corresponding sessions, and SQL
-
Diagnose top offending sessions and SQL, based on their activity
-
Identify hot objects like tables, indexes, and files, or blocks within files
Figure 2: ASH Analytics in Performance Hub
Real-Time SQL Monitoring
With Real-Time SQL Monitoring, you can perform complex run-time application SQL analysis, identity, and guide optimization of application calls in the data tier.
Real-Time SQL Monitoring gives you the following capabilities:
-
Observe and analyze important SQL executions in progress (parallel and long-running queries)
-
Perform detailed and comprehensive execution analysis
-
Resource footprint per query plan step
-
Parallel plan skew across query workers
-
Visualize query plans interactively
-
Perform real-time and historical analysis
Figure 3: Real-Time SQL Monitoring in Performance Hub
Automatic Database Diagnostics Monitoring
When problems occur with an application, performing an accurate and timely diagnosis of the problem before making any changes to a system is important. Often, a database administrator (DBA) looks at the symptoms and immediately starts changing the system to fix those symptoms. However, long-time experience has shown that an initial accurate diagnosis of the actual problem significantly increases the probability of success in resolving the problem.
The statistical data needed for accurate diagnosis of a problem is saved in the Automatic Workload Repository (AWR) of the Oracle Database. The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data regularly, locates the root causes of performance problems, provides recommendations for correcting any problems, and identifies non-problem areas of the system. You can use ADDM to analyze performance issues after the event, often saving time and resources reproducing a problem.
ADDM gives you the following capabilities:
-
Identify the root cause of a problem, not symptoms
-
Get recommendations for treating the root causes of problems
-
Identify non-problem areas of the system
Figure 4: ADDM in Performance Hub
Blocking sessions
The Oracle Database uses locks to ensure that at most one transaction is modifying a given piece of data at any given time. Locks are the mechanism that allows for concurrency. Without a locking model to prevent concurrent updates to the same row, for example, multiuser access isn’t possible in a database. However, if overused or used improperly, locks can inhibit concurrency. If you or the database itself locks data unnecessarily, fewer sessions can concurrently perform operations. Understanding what locking is and how it works in your database is vital if you want to develop a scalable, correct application.
The Performance Hub blocking sessions tab displays the current blocking and waiting sessions in a hierarchical display. You can view detailed information about each blocking session and view the sessions blocked by each blocking session. You can also use the tab to inspect or deeper analyze the SQL involved to determine the cause of the blocking. You can perform several operations in the tab, including stopping one or more of the listed sessions to resolve a waiting session problem.
Figure 5: Blocking Sessions tab in Performance Hub
Conclusion
Performance Hub provides you with a tuning methodology and rich set of features to help pinpoint database performance problems in your application. It takes time and experience to develop the necessary skills to accurately pinpoint critical bottlenecks in a timely manner. Performance Hub implements parts of the performance improvement method and analyzes statistics to provide an automatic diagnosis of major performance issues.
Sign up for an Oracle Cloud Infrastructure trial account! For more information about this feature and how you can use it, see the documentation.