-
Continue reading →: Oracle AWR Case Study: Slow Standby Database Dragging down the Primary
One of my clients from a hospital recently complained about slow performance in their Oracle database and sent me the AWR report for analysis. In this post, I’ll walk through my investigation and troubleshooting process. Load profile — busy but normal The Load Profile section shows a fairly busy OLTP-style…
-
Continue reading →: Where Do These SQLs Come From?
When Oracle displays top SQL statements in the “SQL Statistics” sections of an Oracle AWR report, it truncates long SQL texts for readability— showing only the first few words next to each SQL ID. At the end of this section, the “Complete List of SQL Text” provides the full SQL…
-
Continue reading →: Interpreting “SQL ordered by Physical Reads (UnOptimized)”
In the “SQL Statistics” section of an Oracle AWR report, the list titled “SQL ordered by Physical Reads (UnOptimized)” presents the top SQLs in term of unoptimzied read request. This list can be used to evaluate how effectively Smart Flash Cache or Exadata Smart Scan are being utilized from SQL…
-
Continue reading →: What Are These SQLs Doing Besides CPU and I/O?
Shown below is the “SQL ordered by Elapsed Time” list from the “SQL Statistics” section of an Oracle AWR report: After examining the list, we get the following observations: The missing portion of DB Time can by quantified as (100% – %CPU – %IO) * %Total. Results for the top…
-
Continue reading →: How Representative Are Captured SQL Statements in Oracle AWR Reports?
Before analyzing the Top SQL in the SQL Statistics section of an Oracle AWR report, first determine how well these SQL statements represent the overall workload. If the captured portion is too small, any further analysis may be meaningless or even misleading.
-
Continue reading →: Understanding “SQL Execute Elapsed Time” in the “Time Model Statistics”
In Oracle AWR reports, the Time Model Statistics section greatly simplifies a DBA’s task by revealing where the database spends its time from the user’s perspective. In most cases, “sql execute elapsed time” is dominant component of DB Time. Since Oracle databases are built to execute SQL, one may expect…
-
Continue reading →: PL/SQL vs. SQL Execution Time in Time Model Statistics
In Oracle AWR reports, the “Time Model Statistics” section greatly simplifies a DBA’s task by revealing where the database spends its time from the user’s perspective. It complements top timed events section, which presents a similar view but from the database’s internal perspective. In this example, it states that SQL…
-
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…
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 