WHAT IS THE SOURCE OF YOUR PERFORMANCE PROBLEMS?
15 clicks, 5 minutes — That’s how much you need to uncover the root causes of nearly all performance issues within your database
The Anomaly Monitor
Click 1: Menu Anomaly Monitor - Alerts browser
The Anomaly Monitor module which contains information about detected problems in the monitored database
The Anomaly Monitor tab presents events detected in the monitored database, divided into classes
- Change Plan
- CPU
- I/O
- Latch
- Lock
- Application
- Memory
- New SQL Statement
- Remote
- SQL Statement – increase time executions
- SQL statement – buffer gets
- SQL statement – disk reads
- SQL statement – executions
- Space
- Other
Full History of SQL Statements
Click 2: The full history of SQL statements
The contribution of individual queries to the overall duration of all queries in the database
- The top 20 queries displayed, sorted by Elapsed Time or CPU Time, allow you to focus your optimization efforts on the most likely suspects.
- Visual Identification of Query Impact: The graph visually represents how much time individual queries are consuming, helping you quickly spot which queries are the most resource-intensive.
- Detailed Query Statistics: The ‘Plus’ icon next to each query provides easy access to detailed performance statistics.
Click 3: SQL Details
The most important information about a query’s performance in the database: duration and impact on CPU utilization, whether the query changed its execution plan, the number of records returned, the number of runs, how much it read from data on disk resources, and much more..
- Targeted Problem Identification: With the ability to search for specific queries and analyze their statistics over various timeframes, you can see the exact moment, and reason, the query became problematic.
- Execution Plan Comparison: The tool allows you to compare different execution plans used by the same query.
- Real-Time and Historical Data: By toggling between stored and real-time data, you can quickly identify current performance issues and see how they compare to historical trends.
Click 4: Menu Performance - SQL 3D
The most stressful queries according to the indicated performance statistics
- Visual Identification of Problematic Queries: The 3D chart visually ranks the most resource-intensive queries, making it easy to spot the suspects.
- Targeted Investigation by Statistic: Users can select specific performance statistics (e.g., CPU utilization, Disk Reads, Buffer Gets).
- Historical and Snapshot Analysis: Grouping by different time intervals (e.g., 15 minutes, hourly, daily), providing the ability to examine query performance over periods up to 2 years.
Click 5: Menu Performance - Slow SQLs
Grouped queries statistics for a given period of time
- Target Long-Running Queries: You can identify and analyze queries with significant execution times, filtering out those whose total duration exceeds a specified threshold e.g. 5000 seconds within a month.
- Customizable Query Filtering: Users can adjust the query duration parameters and filter results by grouping literals or execution plans.
- Efficient Query Management: The ability to add slow-performing queries to a clipboard for further analysis makes the whole process much faster.
I/O Load
Click 6: Menu I/O Stats - I/O Analyze
information on the performance of disk devices of the monitored database
- Query-Level Insights: The SQL Statements tab provides a view of the queries executed at specific times, with sortable columns for detailed analysis.
- Interactive Chart Manipulation: Users can interact with the graph to hide specific data points that might clutter the analysis, restoring clarity with a simple refresh.
- Precision Disk Performance Monitoring: This screen provides a granular look at disk device performance.
OS Stat
Click 7: Menu Performance - OS Stat
Information on statistics collected from the operating system available within the v$osstat view
- Hardware Issues Detection: By tracking OS statistics like CPU usage, memory allocation, and disk I/O, DBAs can quickly identify if performance bottlenecks are rooted in hardware or operating system inefficiencies rather than the database itself.
- Early Problem Detection: Spotting issues at the OS level, such as high CPU usage or memory saturation, allows for proactive troubleshooting before these problems escalate.
Statistics Gathered Every 15 Minutes
Click 8: Menu Performance - Perf Counters
Here you'll see performance indicators, i.e. counters containing information about various operations performed in the monitored database
- Quantified Anomalies: When anomalies are detected, the module quantifies the deviation with metrics such as history average, anomaly average, and percentage growth
- Anomaly Detection: The anomaly search feature helps identify unusual performance trends by comparing current data against historical trends over the past 30 days
- Data Export for Reporting: For ease of reporting and further analysis, performance data can be exported in tabular form
Online Monitoring
Click 9: Online Monitor
The information contained in Online Monitor is downloaded every 30 seconds
Monitoring includes information on:
- the CPU utilization of the server and the monitored instance
- statistics of top waits
- statistics of disk I/O arrays
- information on the number of sessions
- Information on alerts detected in the last 2 hours
- Information on queries (top 20 in terms of duration – Elapsed Time).
- Real-Time Monitoring: The Online Monitor provides near real-time data updates every 30 seconds, giving you up-to-the-minute insights into your database’s performance, ensuring that no performance issue goes unnoticed.
Performance
Click 10: Menu Performance - Database Load
A graph of the database load for the selected period
Key pieces of information that show you what’s happening second by second:
- Elapsed Time
- CPU time
- IO, Lock, Wait Time
- Server CPU
- Alerts
- Plan Objects
- Database CPU Count
- Machine CPU Count
Waits
Click 11: Menu Performance - Waits Analyze
Here, users can analyze waits affecting performance of the monitored database
- Identify Critical Waits: By analyzing different types of waits (like I/O waits, CPU waits, and lock waits), this screen helps you understand which specific waits are impacting performance the most
- Visualize Wait Impact on Load: The “Show impact on load” feature is crucial for understanding how much specific waits contribute to the overall system load, helping prioritize which issues to tackle first.
- Spot Hidden Inefficiencies: The Waits Analyze screen uncovers inefficiencies that aren’t so obvious – waits caused by resource contention or poorly optimized queries, to name a few.
Click 12: Menu Performance - Waits Overview
The screen allows you to analyze the level of waits for all processes affecting performance running in the database during the analyzed time..
- Clickable Graph: The ability to click on specific points in time and see a summary of waits offers a precise understanding of performance issues, showing not just that something went wrong, but what specific processes or resources were under strain at that moment.
- Track Resource Utilization: The summary on the left side of the screen shows the top waits, providing insight into which resources (like CPU, memory, or I/O) are being overutilized.
Sessions Online and History
Click 13: Sessions online and history
The module presents current information about sessions running on the monitored database.
- Session Termination: The “Kill session” feature, while common, is improved here by its integration with the user’s permission settings
The screen also includes additional filtering options such as, filtering out sessions that are pending with the indicated wait, as well as based on:
- Machine – the name of the server in the OS
- Module – the name of the module from which the command is currently being executed
- Action – name of the currently executed action
- OS User – the name of the user in OS
- Client Info – additional information about the application
Click 14: Menu Sessions - Sessions/ Sort /Undo History
The module presents the history of sessions performed in the database at the indicated time.
- Granular Data Refresh: Benefit from session data collected every 60 seconds, giving you a near real-time view of database activity.
The data is presented in three basic groups:
- active sessions
- sessions using temporary space
- sessions using UNDO space
- Color-Coded Session Insights: Instantly distinguish active, sorting, and UNDO sessions, making it easy to track and prioritize issues.
Locks
Click 15: Menu Locks - Locks History
The tab allows you to analyze the locks that occurred in the monitored database during the studied period
- Interactive Lock Analysis: Instantly zoom in on specific lock periods, with the ability to click on any point for a detailed breakdown of participating sessions.
- Visual Lock Hierarchy: Easily identify blockers and waiters in a tree format, simplifying the process of pinpointing the root cause of database stalls.
- Session and Query Insights: Access the exact query and session details involved in the lock, providing the necessary context.
Click 16: Menu Locks - Table Locks
The current occurrences of locks in the monitored database
Sessions involved in a lock are displayed as a list in the form of a tree, where:
- sessions marked as BLOCKER are blocking sessions.
- in the nodes below sessions waiting WAITER blocked by sessions in the parent node
- From Insights Directly into Action: Directly kill problematic sessions (provided appropriate permissions) to immediately resolve critical lock situations.
