-
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…
-
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…
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 