-
Continue reading →: Don’t Read the Entire Oracle AWR Report—Only a Few Key Sections!
When investigating a database performance issue, you don’t need to go through the full AWR report line by line, you only need to read from the beginning of the report up to the Time Model Statistics section. This range includes the report header and the Report Summary section. Although these…
-
Continue reading →: Oracle AWR Report Analysis: Diagnosing Global Cache Log Flush Performance in RAC
I was given an Oracle AWR report where the client complained about intolerably slow performance. Top 10 Foreground Events by Total Wait Time Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class gc buffer busy release 776,713 267.3K 344 31.9 Cluster enq: TX – index contention…
-
Continue reading →: Global Cache and Enqueue Services – Workload Characteristics in Oracle RAC AWR Reports
The Global Cache and Enqueue Services – Workload Characteristics section in the AWR report reflects how efficiently Oracle RAC nodes coordinate block access through the Global Cache Service (GCS) and Global Enqueue Service (GES). In other words, it tells us how fast blocks are transferred between nodes, and whether the…
-
Continue reading →: Oracle RAC Global Cache Load Profile
Oracle RAC Global Cache Load Profile from an AWR report provides a snapshot of inter-node communication efficiency. Below is an example: Per Second Per Transaction Global Cache blocks received: 227.44 1.56 Global Cache blocks served: 56.49 0.39 GCS/GES messages received: 332.53 2.28 GCS/GES messages sent: 535.63 3.67 DBWR Fusion writes:…
-
Continue reading →: Another Way to Judge If Your Redo Logfiles Are Adequately Sized
If your Oracle redo log file is undersized, you will run into the wait event: log file switch (checkpoint incomplete). Another way to judge redo logfile sizing is by examining Checkpoint Activity in the Oracle AWR report. The distribution of checkpoint writes reveals whether Oracle is checkpointing smoothly or struggling…
-
Continue reading →: Forget MySQL Root Password? Two Ways to Reset It
Forgetting an ordinary MySQL user’s password is usually not a big problem, since the root user can reset it. However, if the root password itself is forgotten, the situation becomes more complicated. There are two ways to reset the MySQL root password. Method 1: Using –skip-grant-tables Option When MySQL is…
-
Continue reading →: False Alarm of High Rollback Activities in Oracle AWR Report
The following excerpt is taken from the “Load Profile” section of an Oracle AWR report: This data shows the database is processing 5,595.6 transactions per second, while also encountering 1,388.2 rollbacks per second. On the surface, this suggest that nearly a quarter of all transactions are being rolled back—seemingly a…
-
Continue reading →: Simulate Linux Memory Pressure with a One-Liner
Here’s a powerful one-liner that allocates 50GB memory and holds it for 600 seconds: This one-liner can be easily adapted into a short Bash script that increments memory usage step-by-step. Doing so lets you monitor system behavior at different pressure levels.
-
Continue reading →: SQL Statements Execution Patterns
The query below presents execution patterns of 5 top SQL statements, grouped by the day of the week for the last week. Sample output:
-
Continue reading →: Mapping Child SQL to Parent PL/SQL in Oracle AWR Reports
In the “SQL statistics” section of an Oracle AWR report, both SQL and PL/SQL are listed. This results in the double-counting SQL statements executed within PL/SQL blocks. For example, a resource-intensive SQL statement can propel both itself and its parent PL/SQL to the top of the list, making it seem…
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 