Category: Uncategorized

This time, instead of dumping the contents of the log file for a specific Data Block Address (DBA), as in the last part, I’m going to dump it for a specific operation type – Lock Rows (SELECT FOR UPDATE in this case) – which is part of the Row Operations layer (11) and is the… Continue reading Diagnosing Locking Problems using ASH/LogMiner – Part 9

So what about those SELECT FOR UPDATEs? I know that they’ll generate redo entries and so something should appear in both log file dumps and the LogMiner output, but what exactly will appear? (This is all on Oracle 10.2.0.4) For this post I’ll go back to the example from Part 4, where Session 1 performs… Continue reading Diagnosing Locking Problems using ASH/LogMiner – Part 8

(With my tongue causing a minor gash on the inside of my cheek ….) I’m working on an application that seems to suffer constant long waits on log file parallel write and, consequently, log file sync. I thought that the 54 ms log file parallel writes where just typical of an early 90s disk configuration,… Continue reading How to induce log file sync waits

(With my tongue starting to feel a little tired …) Mmmm, there’s a table that the application is accessing extremely frequently, called R_DUMMY. I wonder what data it contains? SQL> SELECT dummy_key FROM r_dummy; DUMMY —– DUMMY Mmmm, that looks familiar, doesn’t it? It’s almost like the DUAL table, isn’t it?   FAST DUAL optimisation?… Continue reading How to generate more Logical Reads

Picking up from the end of the last example, I immediately generated a log file dump as follows. (You’ll need to look back at Part 5 to see that the ROWID, log file name etc. match up or just trust me that these steps came from a continuation of the same SYS session used in… Continue reading Diagnosing Locking Problems using ASH/LogMiner – Part 7

“Toto, I’ve a feeling we’re not in Kansas any more.“ What started as a simple write-up of a course demo gone wrong (or right, depending the way you look at these things) has grown arms and legs and staggered away from the original intention to talk about ASH. so I thought it might be worth… Continue reading Diagnosing Locking Problems using ASH – Part 6

… and so it continues. Coincidentally, the subject of tracking down locking problems after they occurred cropped up on the Oak Table mailing list just after the last post. Several suggestions were offered but I think the nearest and most detailed suggestion was that proposed by Kyle Hailey, ex-Oracle and now working Embarcadero. There were… Continue reading Diagnosing Locking Problems using ASH – Part 5

Some features in this post require a Diagnostics Pack license. No sooner had I finished part 3 with some conclusions than I thought of another example I should have included and then someone else made a comment in an email which suggested another. (Thanks, JB!) “It’s probably worth pointing out that NO TOOL can claim… Continue reading Diagnosing Locking Problems using ASH – Part 4

Some features in this post require a Diagnostics Pack license. In the last part I described a locking scenario where the blocking session had only executed one SQL statement that was quick enough to avoid being sampled by ASH and is now inactive. As a result, there is no ASH data for the blocking session,… Continue reading Diagnosing Locking Problems using ASH – Part 3

Some features in this post require a Diagnostics Pack license. There are few better illustrations of the utility of ASH data than the ability to detect and diagnose locking problems*, even after they’ve occurred. Because ASH is sampling all active sessions all of the time, you should have information both for the blocked and blocking… Continue reading Diagnosing Locking Problems using ASH – Part 1