Analyzing segment content by block type

Occasionally one might want to know what a segment is made of in terms of block types. For example, you notice that the number of blocks in an index segment is somewhat larger than the number of branch and leaf blocks, and wonder what kind of blocks accounts for the difference. The only way to do this is by dumping index blocks (e.g. as described in Richard Foote’s blog here). Dumping blocks is easy, but (more...)

MOS “log file parallel write” reference note updated

Last year, I spent some time researching redo log related performance problems, which resulted in a mini-series, including one post devoted specifically to one previously unknown scenario of excessive log file sync waits.

I am happy to announce that a service request opened on the back of this research resulted in the MOS note on “log file parallel write” wait event (Doc ID 34583.1) having been updated with a general description of this scenario (more...)

Logical I/O

Except a few special cases, optimizing SQL is about minimizing I/O. And by “I/O” we normally mean “physical I/O”, because everybody knows that logical I/O (LIO) is much, much faster. But how much faster exactly? For a long time, this question has been bothering me. It looks like there has been little research in this area. Basically the only thorough investigation I managed to find on the subject was one by Cary Millsap and co-authors. (more...)

High CPU during I/O (continued)

Last week I posted a blog on high CPU utilization during I/O. The blog title contained a question mark because it seemed like there were more questions than answers. Were the CPU utilization numbers real or just an instrumentation artifact? Were they distorted by double-counting? If real, where was it coming from? About a dozen top experts participated in that discussion, and everybody had a different theory. I’ve spent some time looking at the problem (more...)

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...)