In the previous parts (here and here) of the series we looked at some aspects of nested loop I/O optimizations, but we have left out the most important question (from the practical point of view): how these methods are doing time-wise? Which one(s) is(are) faster, and how much savings are they offering compared to the non-optimized plan? We will turn to these questions now.
First of all, if we simply run everything with (more...)
In the previous part of this mini-series we looked at differences in multiblock read behavior for different nested loop optimization mechanisms depending on degree of ordering of the data. In this post I’ll continue to explore the subject, but this time we’ll focus on decision-making process: what factors (other than the obvious ones — like optimizer hints and/or parameters) affect the specific choice of a mechanism?
Previously, we saw that in all nested (more...)
Nested loop join appears like the simplest thing there could be — you go through one table, and as you go, per each row found you probe the second table to see if you find any matching rows. But thanks to a number of optimizations introduced in recent Oracle releases, it has become much more complex than that. Randolf Geist has written a great series of posts about this join mechanism (part 1, (more...)
What would you think if you receive a complaint about plan regression with the following information (from SQL real-time monitoring report) about the good plan:
| Elapsed | Cpu | IO | Concurrency | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
| 99 | 64 | 36 | 0.00 | 6 | (more...)
Sometimes you want to know what’s inside a certain block. Of course, the most straightforward way to do it is by dumping block contents using ALTER SYSTEM DUMP DATAFILE contents and analyzing it. However, “straightforward” doesn’t mean “simple”. Block dumps represent its contents in binary format which is hard to read. Sure, there are various utilities (like utl_raw) that can help you convert everything to the human-readable format, but it’s going to be a tedious (more...)
Year 2015 was a very good one for me, even though not exactly in a way I expected it to be. I didn’t get to blog as much as I wanted to, and I didn’t get as many interesting performance troubleshooting to do as years before that. But there was lots of other interesting experiences — e.g. designing, running and analyzing all sorts of sophisticated performance tests for a candidate hardware platform.
Of course, (more...)
Earlier this year I’ve already touched upon the subject of so called “Observer effect” – impact that the act of observation makes upon the observed process – applied to the database world. In this blog I’d like to expand on this subject a little bit, and discuss one way to minimize this effect using OS observability tools.
All tools that we use to obtain diagnostic information about database processes are intrusive to some (more...)
I feel like I owe a big apology to my readers — not also I haven’t blogged anything for about half a year, but also I started a series and I haven’t finished it. But I have a good excuse. In September, I’ve moved to the UK. It’s a great place to be if you’re into databases — UKOUG is one of the strongest Oracle groups in the world, if not the strongest one. And (more...)
Imagine that you’re on a desert island where some pirates hid their treasure. You don’t know where exactly it is hidden, but you want to find it. How should you approach this – e.g. should you dig randomly? Or should you follow some sort of a system – and if yes, then which?
I think most readers have smelled a trap right away (it’s not a very sophisticated trap anyway) – of course it (more...)