High CPU during I/O?

Yesterday, I posted a trace file that seemed odd to me Twitter. This resulted in a very lively discussion, and several very interesting theories were put forward. Even though the issue hasn’t been solved yet, I would like to post a summary of this discussion (plus my findings) so that more people would be able to give their inputs. Also, some of the theories put forward to explain the observed anomaly are very interesting on (more...)

Disjunction

Disjunction (logical OR) is known to cause various performance problems, from extreme parse times (e.g. here) to sub-optimal plans. A common solution to such problems is getting rid of OR’s by “OR expansion” (i.e. rewrite via UNION ALL), although it doesn’t work in 100% cases. In this post, I will consider an example of an OR problem that can be solved differently.

Here is the setup:

create table t(
   id number,
   x  (more...)

Method R and parallelism (another real-life example)

In my previous post I mentioned method R as probably the most efficient approach to SQL optimization. Occasionally, however, one may encounter a situation when following this method literally can lead to trouble.
Consider this example (once again, the query is still running, so the only reliable diagnostic tool at our disposal is SQL real-time monitor):

SQL Plan Monitoring Details (Plan Hash Value=776230426)
=========================================================================================================================================================================================================================================
| Id   |              Operation              |           Name            |  Rows   | Cost  |    (more...)

Tuning methods (comparison based on a real-life example)

First principles, Clarice. Simplicity. Read Marcus Aurelius. Of each particular thing ask: what is it in itself? What is its nature?

The Silence of the Lambs

What do you do when you get a call about a query that keeps running without completing, where the query text itself looks very simple:

select col1, col2, ...
from someview
where colA = '12345-A'
and colB in ('B')
order by colN, colM

and the SQL plan monitor report looks (more...)

My 2014

Year 2014 was a successful one for me. I was blessed with quite a few interesting performance issues which allowed me to expand my horizons. One topic I’d like to mention in particular is redo writing. I spent a few weeks optimizing a batch load process and since redo writing was a serious bottleneck, I had to spend some time understanding internals of the process. This resulted in a series of posts on log writing (more...)

Occam’s razor

This year was rich in interesting performance issues. This post is about one recent issue that was particularly intriguing because it was:

- intermittent

- not associated with any particular database activity

- it tended to appear at a certain time of the day (between 2am and 4am database time), but there were some variations

- there was no discernible pattern in days when the problem appeared

- performance degradation was due to a whole (more...)

“4k bug” is not a bug

A couple of weeks back I received an update on my “4k bug” SR (slow multirow DML performance for small block sizes). As it turns out, the observed behavior is not a bug. It’s actually more interesting than that. It is an undocumented feature that controls the insert mode depending on the estimated row size (the sum of all column sizes). If the estimated column size exceeds the size of the block, then Oracle switches (more...)

Making sense out of optimizer trace files

Sometimes the optimizer makes really odd decisions. We can use optimizer (“event 10053″) tracing to obtain clues as to why such decisions were taken, but it’s not simple. First, such trace files tend to be big. Second, they don’t always contain the necessary information. There is a simple trick that can be useful in such cases.

First, I use “optimizer_features_enable” hint to try and find Oracle version where the logic changes. Of course, I don’t (more...)

Transactional integrity

In the database world (especially among the database developers) a commit is often viewed as some sort of a virtual “save” button — i.e. it’s something that you need to do to make your changes permanent. This is one of the reasons why developers are often happy to commit work as soon as they get a chance (even if it’s not properly finished), so that they wouldn’t lose their changes in case of an (more...)

Excessive commits?

Let’s consider a hypothetical scenario. Suppose you have a process A that you want to run faster. This process doesn’t commit (much), so it doesn’t wait on log file sync. However, there is another multi-threaded process, B, that commits very frequently, and spends a lot of time on “log file sync”. You don’t care about the process B, your only goal is to make A go faster. After exhausting your tuning arsenal (to no avail), (more...)