-
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…
-
Continue reading →: Understanding ASH Data Retention in Oracle Database
Oracle’s Active Session History (ASH) is a powerful performance diagnostic tool that enables real-time and historical analysis of session-level activity. However, one common question DBAs and performance engineers often ask is: “How long is ASH data retained?” The answer depends on whether you’re referring to in-memory ASH or persisted ASH…
-
Continue reading →: Auditing AWR Usage in Oracle
You can track whether AWR-related feature have been used by querying the DBA_FEATURE_USAGE_STATISTICS view: Sample output: This output clearly shows which AWR features have been used and how often, aiding compliance audits.
-
Continue reading →: How to Avoid Unlicensed Use of AWR/ASH in Oracle
Without an Oracle Diagnostics Pack license, using AWR and ASH is illegal, even with an Enterprise Edition license. To ensure compliance with Oracle’s licensing requirements and prevent avoid unintentional use of AWR and ASH, consider the following methods: Note: Some Oracle features, like Automatic Segment Advisor and Undo Advisor, rely…
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 