-
Continue reading →: Script For Oracle AWR Report: Top 10 Foreground Events by Total Wait Time
As always, to truly understand how this section is generated, I’ve developed a custom SQL script that reproduces these results. To dive deeper into Oracle AWR analysis, download the full PowerPoint tutorial. Contact me on X, I’d love to share it with you for free!
-
Continue reading →: SQL Script Generates Load Profile of Oracle AWR Report
Load Profile section gives you a quick overview of the database workload. I’ve developed a SQL script that can generate the same results as the Load Profile’s output. To dive deeper into Oracle AWR analysis, download the full PowerPoint tutorial. Contact me on X, I’d love to share it with…
-
Continue reading →: Understanding Session Statistics in Oracle AWR Reports
Oracle AWR report reports number of sessions, below is an example: Where does Oracle collect Session Data? I conduct a sample experiment by run the following queries: Observations from the experiment: Oracle collects session statistics from the DBA_HIST_SYSSTAT table, specifically from the row where STAT_NAME = ‘logons current’. This value,…
-
Continue reading →: Understanding Oracle’s Multiple LGWR Processes and the “LGWR any worker group” Wait
Since Oracle 12c, Oracle has adopted multiple Log Writer (LGWR) processes instead of a single LGWR process to parallelize redo writing from the log buffer to the redo log file. The primary LGWR process delegates operations to helper log writer worker processes (named LGnn). Typically, there are two worker processes…
-
Continue reading →: Compiling and Installing PostgreSQL from Source on Red Hat
In this guide, I’ll walk you through the process of creating a dedicated user for PostgreSQL, compiling, and installing PostgreSQL 17.2 from source on a Red Hat-based system. This approach allows you to customize your PostgreSQL installation for specific needs, such as enabling debugging information. The steps also cover configuring…
-
Continue reading →: How Under-Sized Log Files Eat Into Oracle Performance
Here is an example of an AWR report generated from a benchmark test of an OLTP system: In the Top 10 Foreground Events by Total Wait Time section, it shows that log file sync accounts for 48.3% of the total database time. This event occurs when a user session commits…
-
Continue reading →: An Under-Sized PGA Detected by AWR Report
Setting the correct size for the Program Global Area (PGA) is crucial for optimizing Oracle database performance, this is especially true with OLAP system. By analyzing AWR reports, we can determine if the configured PAG size is adequate for the workload. Symptoms of an Under-Sized PGA Here is an AWR…
-
Continue reading →: Identify the Chain of Blocking Sessions with ASH
In a high-traffic database, it is inevitable that concurrent sessions will block each other. One blocked session can subsequently block other sessions, resulting in a chain of blocking sessions when blockages occur. Depending on how long the block has been lifted, information on blocking issues can be retrieved from three…
-
Continue reading →: Configuring Oracle AWR Snapshot
Default Snapshot Setting Metadata about snapshots is recorded in the DBA_HIST_SNAPSHOT view. Below is an example of querying the view: A sample output: Each snapshot is assigned a unique snapshot ID, which appears in the SNAP_ID column. This column also exists in all DBA_HIST_* tables and can be used to join with the…
-
Continue reading →: Moving Windows Snapshot Baseline
A moving window snapshot baseline represents a continuously updating set of AWR snapshots over a specified period. Oracle database automatically maintains a system-defined moving window baseline. The DBA_HIST_BASELINE view contains baseline information: You can drop baseline at a later date using: You can also create baseline for a future contiguous…
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 