-
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…
-
Continue reading →: Simulating Oracle Performance Hub with Text-Based ASH Visualizations
The Performance Hub in Oracle Enterprise Manager Cloud Control is an integrated dashboard for performance monitoring and diagnostics. However, this useful graphical tools may not always be available due to access limitations or licensing restrictions. Fortunately, all graphical representations are inherently supported by underlying commands. Specifically, the Performance Hub is…
-
Continue reading →: Monitoring SQL Execution Plan Changes
If a SQL statement’s performance suddenly degrades, one possible cause could be a change in its execution plan. The following query can be used to identify when and how the execution plan has changed: The commented line in the SQL query serves as an optional filter condition. If uncommented, it…
-
Continue reading →: Should Database be Blamed for Slow Response?
When an end user experiences a slow response from an application, it can sometimes be difficult to determine whether the issue is caused by the application layer (middleware) or the database layer. This often leads to discussions (and sometimes arguments) between the application developer and the database administrator (DBA) as…
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 