-
Continue reading →: Understanding ASH Data Retention in Oracle Database
Oracle’s Active Session History (ASH) is a powerful performance diagnostic tool that enables real-time and historical analysis of session-level activity. However, one common question DBAs and performance engineers often ask is: “How long is ASH data retained?” The answer depends on whether you’re referring to in-memory ASH or persisted ASH…
-
Continue reading →: Auditing AWR Usage in Oracle
You can track whether AWR-related feature have been used by querying the DBA_FEATURE_USAGE_STATISTICS view: Sample output: This output clearly shows which AWR features have been used and how often, aiding compliance audits.
-
Continue reading →: How to Avoid Unlicensed Use of AWR/ASH in Oracle
Without an Oracle Diagnostics Pack license, using AWR and ASH is illegal, even with an Enterprise Edition license. To ensure compliance with Oracle’s licensing requirements and prevent avoid unintentional use of AWR and ASH, consider the following methods: Note: Some Oracle features, like Automatic Segment Advisor and Undo Advisor, rely…
-
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…
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 26 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 