-
Continue reading →: Controlling AWR and ASH Access with CONTROL_MANAGEMENT_PACK_ACCESS
It’s important to recognize that a license for Oracle Database Enterprise Edition does not automatically grant access to Automatic Workload Repository (AWR) and Active Session History (ASH). These features are part of the Oracle Diagnostics Pack, an additional paid option that requires a separate license on top of the Enterprise…
-
Continue reading →: Identifying Transient Performance Issues via SQL Runtime Deduction from ASH Data (Part 3/3)
Finding Outlier Executions for a Specific SQL You may already know the problematic SQL ID but want to find which execution deviated from the norm. Use this query: The output is: This shows that the execution starting at 15:24:02 was an outlier, running over 3 minutes, while other executions were…
-
Continue reading →: Identifying Transient Performance Issues via SQL Runtime Deduction from ASH Data (Part 2/3)
Identifying Culprit SQL for Transient Slowdowns This method is very useful to locate culprit SQL statements behind random and transient slowdown. Image the following dialog: User: The application suddenly took a long time to process a request! DBA: Can you show me what happened? User: No, it was a random…
-
Continue reading →: Identifying Transient Performance Issues via SQL Runtime Deduction from ASH Data (Part 1/3)
Identifying sporadic performance issues caused by outlier SQL statements is critical yet challenging in database performance monitoring. Traditional tools often rely on periodic snapshot and aggregated metrics, which mask transient spikes and outliers. This article presents a technique to deduce the individual runtime of specific SQL statement using ASH raw…
-
Continue reading →: Creating Timelines of Anything with Oracle ASH
The Oracle ASH raw data captures session activities and timestamps, enabling timeline creation for insightful analysis. A SQL query examines prior day’s session activity, categorizing it by wait class and hourly grouping. Results indicate a CPU-intensive workload with peak activity in clinical hours, while adaptations for specific analyses can be…
-
Continue reading →: Summarizing Session Activity across Any Dimensions with ASH
ASH raw data can be summarized across various dimensions, offering multiple perspectives for analyzing session activity. This allows you to observe workload distribution from different angles. Below are several SQL query examples demonstrating how to summarize ASH data across various dimensions. Session State Breakdown by Category The SQL query below…
-
Continue reading →: Identifying Top Entities by ASH (Part 4/5): Top PGA or Temp Space Consumers
The PGA_ALLOCATED column in ASH records the amount of PGA memory consumed by the session at the time the sample was taken, so if PGA memory usage is excessive, the following query can identify the offending SQL statements: Another useful column in ASH is TEMP_SPACE_ALLOCATED which indicates the temporary tablespace…
-
Continue reading →: Identifying Top Entities by ASH (Part 5/5): General Query Formula
Here is a reusable template for summarizing ASH session activity by any column or combination of columns: Explanation: By leveraging this formula, you can analyze session activity using any of the 112 columns available in ASH views. You can group by a single column or a combination to gain deeper…
-
Continue reading →: Identifying Top Entities by ASH (Part 3/5): Top Accessed Objects
This query lists the top 10 accessed objects in the last 30 minutes, including their access counts and percentage of total accesses: Sample output:
-
Continue reading →: Identifying Top Entities by ASH (Part 2/5): Top Wait Events
This query identifies the top 10 wait events in the last 30 minutes, showing their frequency and percentage of total wait time: Sample output: As shown, CPU usage dominates the session activity.
Hello,
I’m Yuan
Welcome to the blog of a performance maven in Oracle & MySQL. Feel free to contact me to share and discuss database knowledge.
My 20 Oracle Certifications
A screenshot from certview.oracle.com
My Book
MySQL 8.0 Operations and Optimization
Let’s connect
Stay updated with my latest blogs.
Yuan Yao, An Oracle ACE's Blog 