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 their own.

The issue is very simple (which I think is one of the reasons why it resulted in such a heated debate). It has to do with unreasonably high CPU utilization with low values of the db_file_multiblock_read_count (from that point on, MBRC) parameter. Here is the setup I used in my tests:

Continue reading “High CPU during I/O?” →

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.

Continue reading “Disjunction” →

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

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 like this:

Continue reading “Tuning methods (comparison based on a real-life example)” →

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 received very favorable feedback from several top experts in the field, such as Jonathan Lewis, Tanel Poder, Kevin Closson, Doug Burns, Kyle Hailey and many others. Another interesting topic that I came across in 2014 is DML internals, in particular the factors that determine whether a DML statement will be processed row-by-row or in bulk. That lead to discovery of undocumented behavior which as far as I can tell was not know prior to that.

All that allowed my blog audience to grow significantly. I have crossed the 100,000 visits mark in 2014, and judging by current visit statistics, I might be able to double that number at some point in 2015. This is important to me because along with other metrics (retweets/reposts, comments, subscribers etc.) it shows me that I’m on the right track. I was also honored to be added to OakTable’s “good Oracle blogs to follow” list.

One of the frustrations in 2014 was that some of the good stuff in this blog remains unknown to the broader audience because of relatively low search engine rankings (ironically, some of my older posts that I myself don’t find particularly interesting, are doing much better in that area). If anyone who is reading this blog knows how this can be addressed (without spamming of course!), I would very much appreciate an advice from you.

Anyways, I’d like to thank all the readers of this blog and wish you all the best in the new year!