Yuan Yao, An Oracle ACE's Blog

ASH

Identifying Top Entities by ASH (Part 3/5): Top Accessed Objects

Published by

YuanOracle

on

7th May 2025

This query lists the top 10 accessed objects in the last 30 minutes, including their access counts and percentage of total accesses: Sample output: Top SQL statements Top Wait Events Top Accessed Objects Top PGA or Temp Space Consumers General Query Formula
Continue reading →: Identifying Top Entities by ASH (Part 3/5): Top Accessed Objects
ASH

Identifying Top Entities by ASH (Part 2/5): Top Wait Events

Published by

YuanOracle

on

7th May 2025

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. Top SQL statements Top Wait Events Top Accessed Objects Top PGA or Temp Space Consumers General Query Formula
Continue reading →: Identifying Top Entities by ASH (Part 2/5): Top Wait Events
ASH

Identifying Top Entities by ASH (Part 1/5): Top SQL Statements

Published by

YuanOracle

on

7th May 2025

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: Top SQL statements Top Wait Events Top Accessed Objects Top PGA or Temp Space Consumers General Query Formula
Continue reading →: Identifying Top Entities by ASH (Part 1/5): Top SQL Statements
Uncategorized

Hidden Parameters That Control ASH Behavior in Oracle

Published by

YuanOracle

on

6th May 2025

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 →: Hidden Parameters That Control ASH Behavior in Oracle
AWR

Three Ways to Detect CPU-Noisy Neighbors in Oracle Database Environments with AWR Report

Published by

YuanOracle

on

6th May 2025

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 →: Three Ways to Detect CPU-Noisy Neighbors in Oracle Database Environments with AWR Report
Linux, Uncategorized

Quick Disk Performance Testing with dd and hdparm

Published by

YuanOracle

on

6th May 2025

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 →: Quick Disk Performance Testing with dd and hdparm
ASH, AWR

Unmasking CPU Starvation in Oracle (Part 3/3): ASH to the Rescue

Published by

YuanOracle

on

30th Apr 2025

This article explores how CPU starvation manifests in Oracle AWR reports. The discussion is divided into three parts: Why Doesn’t % DB Time Add Up to 100%? Don’t Be Fooled by CPU Utilization ASH to the Rescue ASH to the Rescue Theoretically, we can estimate CPU starvation by comparing two…
Continue reading →: Unmasking CPU Starvation in Oracle (Part 3/3): ASH to the Rescue
AWR, Oracle

Unmasking CPU Starvation in Oracle (Part 2/3): Don’t Be Fooled by CPU Utilization

Published by

YuanOracle

on

29th Apr 2025

This article explores how CPU starvation manifests in Oracle AWR reports. The discussion is divided into three parts: Why Doesn’t % DB Time Add Up to 100%? Don’t Be Fooled by CPU Utilization ASH to the Rescue Don’t Be Fooled by CPU Utilization You might think low CPU utilization means…
Continue reading →: Unmasking CPU Starvation in Oracle (Part 2/3): Don’t Be Fooled by CPU Utilization
Linux

CPU Hyper-Threading Explained (Part 3 of 3): Does Hyper-Threading Boost CPU Performance—and by How Much?

Published by

YuanOracle

on

29th Apr 2025

This is Part 3 of a 3-part article series to explore CPU Hyper-Threading. What Is CPU Hyper-Threading? How Hyper-Threading Skews CPU Usage Readings Does Hyper-Threading Boost CPU Performance—and by How Much? Does Hyper-Threading Boost CPU Performance—and by How Much? Multiple-thread applications—such as video editing, 3D rendering, virtualization, and some scientific…
Continue reading →: CPU Hyper-Threading Explained (Part 3 of 3): Does Hyper-Threading Boost CPU Performance—and by How Much?
Linux

CPU Hyper-Threading Explained: How Hyper-Threading Skews CPU Usage Readings (Part 2 of 3)

Published by

YuanOracle

on

29th Apr 2025

This is Part 2 of a 3-part article series to explore CPU Hyper-Threading.Part 1: What Is CPU Hyper-Threading?Part 2: How Hyper-Threading Skews CPU Usage ReadingsPart 3: Does Hyper-Threading Boost CPU Performance—and by How Much? How Hyper-Threading Skews CPU Usage Readings Because the operating system collects CPU usage metrics based on…
Continue reading →: CPU Hyper-Threading Explained: How Hyper-Threading Skews CPU Usage Readings (Part 2 of 3)