Oracle AWR Case Study: Diagnosing ‘enq: TX – row lock contention’
In a busy and healthy database environment, it is unavoidable that multiple sessions will attempt to modify the same row or set of rows simultaneously. This leads to situations where one session must wait for another to finish before proceeding, resulting in the “enq: TX – row lock contention” wait event. While some level of…
Oracle AWR Case Study: Pinpointing When the Execution Plan Flip Occurred
One day, users angrily complained that the Oracle database had suddenly become unbearably slow. They could feel the lag but had no idea when the slowdown actually started. The Standout Troublemaker Below is the “SQL Statistics” section from the latest AWR report: The top SQL consumed more than 99% of Total DB Time. It executed…
How Oracle Calculates the Number of Sessions in the AWR Report
At the top of every Oracle AWR report, there’s an important metric called “Sessions”, which represents the number of sessions at the time each snapshot (Snap ID) is being taken. This value comes from the logons current statistic in the DBA_HIST_SYSSTAT table. Let’s take a closer look at how Oracle obtains this value through a…
Oracle AWR Case Study: Slow Standby Database Dragging down the Primary
One of my clients from a hospital recently complained about slow performance in their Oracle database and sent me the AWR report for analysis. In this post, I’ll walk through my investigation and troubleshooting process. Load profile — busy but normal The Load Profile section shows a fairly busy OLTP-style workload, but nothing unusual or…
Where Do These SQLs Come From?
When Oracle displays top SQL statements in the “SQL Statistics” sections of an Oracle AWR report, it truncates long SQL texts for readability— showing only the first few words next to each SQL ID. At the end of this section, the “Complete List of SQL Text” provides the full SQL text for every SQL ID.…
Interpreting “SQL ordered by Physical Reads (UnOptimized)”
In the “SQL Statistics” section of an Oracle AWR report, the list titled “SQL ordered by Physical Reads (UnOptimized)” presents the top SQLs in term of unoptimzied read request. This list can be used to evaluate how effectively Smart Flash Cache or Exadata Smart Scan are being utilized from SQL workload perspective. In an Oracle…
What Are These SQLs Doing Besides CPU and I/O?
Shown below is the “SQL ordered by Elapsed Time” list from the “SQL Statistics” section of an Oracle AWR report: After examining the list, we get the following observations: The missing portion of DB Time can by quantified as (100% – %CPU – %IO) * %Total. Results for the top SQLs are: SQL ID Calculation…
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.
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 it to be close to…
Yuan Yao, An Oracle ACE's Blog 