-
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…
-
Continue reading →: A List of Key DBA_HIST_* Views and Their Corresponding V$ Performance Views in Oracle
When creating snapshot, Oracle collects data from operating system and the dynamic performance views, known as v$ (pronounced “v-dollar”) tables, which reside in memory. The collected data is then stored in the corresponding DBA_HIST_* tables within the SYSAUX tablespace. The following table displays a list (incomplete) of the DBA_HIST_* views…
-
Continue reading →: Enabling AWR in Oracle Database
To use the Automatic Workload Repository (AWR), the STATISTICS_LEVEL parameter must be set to either TYPICAL or ALL. The default setting is TYPICAL. The parameter can be changed online. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including AWR, and is not recommended. It’s important to note that using…
-
Continue reading →: Detecting a Transient Performance Issue with ASH Report
This blog is an example of how to use an ASH report to detect a transient performance issue. ASH report begins with basic information about the environment in which the report was generated: The main sections of the ASH report include: Except for the last section, all sections focus on…
-
Continue reading →: Troubleshooting Post-Restoration Error ORA-00904: “ACDRROWTSINTCOL#”: invalid identifier
I was told after a Oracle database restoration, the database could not be opened. When attempting to open the database, the following error message were encountered: I’ve noticed the the Oracle version in use is 12.2.0.1.0, but the compatible parameter in the PFILE is set to 12.1.0.2.0. This discrepancy indicates…
-
Continue reading →: Undocumented View V$ASH_INFO
The view V$ASH_INFO contains information of ASH sampling process, this view is not documented in the official Oracle documentation. The LATEST_SAMPLE_TIME is very close to current time (within 1 second) , and the OLDEST_SAMPLE_TIME indicates the earliest ASH data available in Oracle ASH buffer. These two columns define the time…
-
Continue reading →: Two More Oracle 19c Certifications in RAC and DataGuard
I currently hold over 20 Oracle certifications. Recently, I have obtained tow more Oracle 19c certifications in RAC and DataGuard. In fact, the content is not much different from 12c, but the questions are still very difficult. Many options are ambiguous, and a clear understanding of the relevant concepts is…
-
Continue reading →: Legality of Using AWR and ASH in Oracle Database
It is important to note that having a license for Oracle Database Enterprise Edition alone does not entitle you to use AWR (Automatic Workload Repository) and Active Session History (ASH). AWR and ASH are part of the Diagnostics and Tuning Pack, which are additional paid options on top of 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 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 