-
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.
-
Continue reading →: Identifying Top Entities by ASH (Part 1/5): Top SQL Statements
The following query retrieves the top 10 SQL statements executed in the last 30 minutes, including their execution count and percentage of total activity: Sample output:
-
Continue reading →: Hidden Parameters That Control ASH Behavior in Oracle
There are 6 hidden parameters that control ASH behavior, and there are no non-hidden parameters that control ASH behavior. The following SQL query retrieves these parameters: Sample output (default values): The description column provides sufficient explanation for each parameter. These defaults are appropriate for nearly all environments and typically do…
-
Continue reading →: Three Ways to Detect CPU-Noisy Neighbors in Oracle Database Environments with AWR Report
In Oracle database environments, CPU-noisy neighbors are non-Oracle processes that consume significant CPU resources on a server, potentially degrading Oracle database performance. By comparing Oracle’s CPU usage with the host’s overall CPU consumption, you can identify these interfering processes. Below are three methods to detect CPU-noisy neighbors in Oracle database…
-
Continue reading →: Quick Disk Performance Testing with dd and hdparm
While many disk performance tools like fio, orion, and iometer provide in-depth analysis, the dd and hdparm commands stand out for their simplicity and universal accessibility on Linux systems. In this article, I’ll demonstrate how to use these tools for quick and effective disk performance evaluations, drawing from real-world scenarios.…
-
Continue reading →: Unmasking CPU Starvation in Oracle (Part 3/3): ASH to the Rescue
This article explores how CPU starvation manifests in Oracle AWR reports. The discussion is divided into three parts: ASH to the Rescue Theoretically, we can estimate CPU starvation by comparing two metrics: Oracle’s instrumentation cannot distinguish between a process actively running on the CPU and one waiting in the CPU…
-
Continue reading →: Unmasking CPU Starvation in Oracle (Part 2/3): Don’t Be Fooled by CPU Utilization
This article explores how CPU starvation manifests in Oracle AWR reports. The discussion is divided into three parts: Don’t Be Fooled by CPU Utilization You might think low CPU utilization means the system isn’t CPU-bound. However, in many cases, CPU utilization can be misleading. Consider the following “Host CPU” section…
-
Continue reading →: CPU Hyper-Threading Explained (Part 3 of 3): Does Hyper-Threading Boost CPU Performance—and by How Much?
This is Part 3 of a 3-part article series to explore CPU Hyper-Threading. Does Hyper-Threading Boost CPU Performance—and by How Much? Multiple-thread applications—such as video editing, 3D rendering, virtualization, and some scientific computing tasks—benefit the most from Hyper-Threading (HT). For instance, on the aforementioned server, with HT disabled, it can…
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 