Author: Riyaj Shamsudeen

Recently we quickly and efficiently resolved a major performance issue with one of our clients related to a central database that was intermittently freezing because of high CPU usage. In this blog, I will discuss this performance issue and its solution.

I recently traveled to Europe to present at a few conferences. The Slovenia Oracle User Group (SIOUG) conducted an Oracle conference in Portoroz, a port city in Adriatic Sea. Back to reality. I presented a few papers for the Dallas Oracle Users Group (DOUG) for their October tech meeting too. All these papers can be accessed following these links…Also, if you are planning to attend the UKOUG Conference & Exhibition in December, please attend my presentation on “Cost based query transformation” on Thursday of that week.

Recently, I resolved a performance issue with one of our clients related to an application shut down in DB1 which created massive CPU consumption on the PROD1DB server. Unfortunately, this spike in CPU usage lasted for five to ten seconds,causeing the ASM heartbeat to fail. I found this problem interesting and worth blogging about. Have a look.

In an Oracle-l thread, this question was raised: how can you find objects creating nologging changes?

Recently, I was involved in a discussion about import performance in this OTN forum in which the original poster raised the issue of whether or not import will resort to single-row inserts for tables with date columns. For array inserts, the buffer parameter essentially specifies the size of this array. We know, however, that if a table has lob columns, the import parameter buffer is not honored, and the import utility will use single-row inserts for those tables. But tables with date columns, so the claim goes, also must suffer single-row inserts. In this blog, I will probe this further and validate that claim.

Recently, I had an opportunity to tune latch contention for cache buffers chain (CBC) latches. The problem was high CPU-usage combined with poor application performance. A quick review of the statspack report for 15 minutes showed a latch-free wait as the top event, consuming approximately 3600 seconds in an 8-CPU server. CPU usage was quite high. First, I’ll find the SQL suffering from latch contention and objects associated with the access plan for that SQL. Next,I will find the buffers involved in latch contention, and map that back to objects. Finally, I will match these two techniques to pinpoint the root cause.

In this blog entry, I will discuss strategies and techniques to resolve ‘log file sync’ waits. This entry is intended to show an approach based upon scientific principles, not necessarily a step-by-step guide. Let’s understand how LGWR is inherent in implementing the commit mechanism first.

Recently, I had a few email exchanges — on the Oracle-l list and offline — about HW enqueue contention. A few interesting observations emerged from test cases I created during that discussion.

A few days ago, a developer and I had an interesting conversation. The developer was trying to tune an expensive SQL statement, using a trial-and-error method. After looking at his method in amusement, I showed him how to identify and tune SQL statements scientifically, and decided to blog about it. Let’s look at a simple case and then proceed to slightly more complex versions.

We all have encountered this situation many times before: the cost-based optimizer assumes no correlation between two columns (until 11g), and this has the effect of erroneously reducing the cardinality of a row source. Incorrect cardinality estimates are one of many root causes for SQL performance issues. Consider the following example.