Kerry Osborne's picture

Hotsos Symposium 2011 – Keynote

The Hotsos Symposium is, in my humble opinion, the best Oracle conference in the world. The quality of the presenters and the participants is just outstanding. So what a surprise and an honor to be invited to do the Keynote Talk at the 2011 Hotsos Symposium. I told Gary that I’d have to think about it for a day or so. Of course I agreed to do it. My good friend Karen Morton is doing the optional training day this year. She knows her stuff and is a great communicator. So I’d highly recommend sticking around for the extra day. Anyway, here’s a link to the main Hotsos Symposium page:

Greg Rahn's picture

Reading Parallel Execution Plans With Bloom Pruning And Composite Partitioning

You’ve probably heard sayings like “sometimes things aren’t always what they seem” and “people lie”. Well, sometimes execution plans lie. It’s not really by intent, but it is sometimes difficult (or impossible) to represent everything in a query execution tree in nice tabular format like dbms_xplan gives. One of the optimizations that was introduced back in 10gR2 was the use of bloom filters. Bloom filters can be used in two ways: 1) for filtering or 2) for partition pruning (bloom pruning) starting with 11g. Frequently the data models used in data warehousing are dimensional models (star or snowflake) and most Oracle warehouses use simple range (or interval) partitioning on the fact table date key column as that is the filter that yields the largest I/O reduction from partition pruning (most queries in a time series star schema include a time window, right!). As a result, it is imperative that the join between the date dimension and the fact table results in partition pruning. Let’s consider a basic two table join between a date dimension and a fact table. For these examples I’m using STORE_SALES and DATE_DIM which are TPC-DS tables (I frequently use TPC-DS for experiments as it uses a [...]

glennfawcett's picture

New Oak table member… It’s official!

After many long nights, technical discussions, and joint work with Oak table members they decided to nominate me.  I just received notice and it is official!!  I am humbled and honored to be recognized along with this top-notch group of Oracle rock stars.

Filed under: Oracle

cary.millsap's picture

Agile is Not a Dirty Word

While I was writing Brown Noise in Written Language, Part 2, twice I came across the word “agile.” First, the word “agility” was in the original sentence that I was criticizing. Joel Garry picked up on it and described it as “a code word for ‘sloppy programming.’” Second, if you read my final paragraph, you might have noticed that I used the term “waterfall” to describe one method for producing bad writing. Waterfall is a reliable method for producing bad computer software too, in my experience, and for exactly the same reason. Whenever I disparage “waterfall,” I’m usually thinking fondly of “agile,” which I consider to be “waterfall’s” opposite.

tanelpoder's picture

A: The most fundamental difference between hash and nested loop joins

Ok guys, thanks for waiting!

I ended up expanding the article quite a lot compared to what I had originally planned. In fact I only wrote 50% of what I plan to write, I’ll update the rest… um… later… Instead of just stating the difference between the joins I took a step back and elaborated something what I often see people doing (and talking about in newsgroups and lists too).

Basically the most fundamental (or biggest or most important) difference between nested loop and hash joins is that:

  • Hash joins can not look up rows from the inner (probed) row source based on values retrieved from the outer (driving) row source, nested loops can.

In other words, when joining table A and B (A is driving table, B is the probed table), then a nested loop join can take 1st row from A and perform a lookup to B using that value (of the column(s) you join by). Then nested loop takes the next row from A and performs another lookup to table B using the new value. And so on and so on and so on.

This opens up additional access paths to the table B, for example when joining ORDERS and ORDER_ITEMS by ORDER_ID (and ORDER_ID is leading column of PK in ORDER_ITEMS table), then for whatever orders are taken from ORDERS table, we can perform a focused, narrow index range scan on ORDER_ITEMS for every ORDER_ID retrieved from the driving ORDERS table. A hash join can’t do that.

Of course this doesn’t mean that hash joins can’t use any indexes for tables they read – index range scans and unique lookups can still be used under a hash join, but only if there are constant values in the query text (in form of literal or bind variables). If there are no such constant (filter) conditions under a hash join, then the other options to use that index would be to do an INDEX FULL SCAN (which is a range scan from end to end of the index) or INDEX FAST FULL SCAN (which is like a full table scan through the entire index segment). However none of these opportunities give the same benefits as nested loops looking up rows from row source B dynamically based on what was retrieved from A during runtime.

Note that this nested loops benefit isn’t limited to indexes only on table B, the difference is more fundamental than just a specific access path. For example, if table B happens to be a single table hash cluster or indexed X$ table, then the nested loop is also able to do “optimized” lookups from these row-sources, based on the values retrieved from table A.

So, my article with a lot of (loosely) related details is here:

In the comments section of my question, Tom, Bernard Polarski, Christian Antognini and Marc Musette got the closest to what I had in my mind when I asked the question. However, of course your mileage may vary somewhat depending on what kind of problems you have experienced the most over all the years. Also, Jonathan Lewis had a valid comment regarding that the answer depends on what exactly does one mean by “fundamental” and yeah this was open to interpretation.

Nevertheless, I wanted to emphasize that there’s a more important difference between NL and hash joins, than the usual stuff you see in training material which talk about implementation details like hash tables and memory allocation…

Some day I will complete that article, I plan to add some design advice in there, like denormalization opportunities for getting the best of the both worlds etc. But now I’m gonna get a beer instead.

Thanks for reading and answering my blog, I was quite impressed by the volume of comments & answers to my question. I must do this more often!


oraclebase's picture

Advert: PL/SQL Masterclass in Hong Kong is back…

I managed to swap the Melbourne to Birmingham flights, so the Hong Kong date (October 25th-26th) is back again. If you are interested in coming, the contact details are listed here. The date hasn’t made it back onto the website yet, but it is definitely going to happen.

The final trip schedule is pretty scary.

  • Birmingham -> Dubai -> Singapore
  • Singapore PL/SQL Masterclass (14th – 15th)
  • Singapore -> Sydney
  • Sydney PL/SQL Masterclass (18th – 19th)
  • Speak at the Sydney Oracle Meetup in the evening (18th)
  • Sydney -> Melbourne
  • Melbourne PL/SQL Masterclass (21st – 22nd)
  • Melbourne -> Hong Kong
  • Hong Kong PL/SQL Masterclass (25th – 26th)
  • Hong Kong -> Bangkok -> Dubai Birmingham
  • Nervous Breakdown

I feel a bit sick at the thought of all that time on planes and all the timezone switches. The last time I did a trip this complicated I was away from home for 2 months. This time it’s compacted into 16 days, so if you see a fat zombie, point him to the projector and tell him to start speaking. Once the mouth starts, the rest will take care of itself. :)



oraclebase's picture

Let’s play spot the Sve…

I was just looking at a couple of photos from OpenWorld and I noticed a “not so little” Bulgarian hiding in the photo. Can you spot the Sve (Svetoslav Gyurov)?

Other things to notice about the photo are:

  • Richard Foote explaining exactly how much like David Bowie he really is. My original explanation of his action was a little more uncharitable. :)
  • Me being interested enough in Richard’s conversation to drag me away from my iPad for 2 seconds.
  • Chris Muir ignoring us both and trying to come to terms with the fact he hadn’t brought his iPad to the US and was forced to use one of those mini-iPads.

If you recognize yourself on this photo feel free to comment. I think the foot may belong to Jacco Landlust as I seem to remember him having one of those posh lunch boxes one of the days we were camped out in The Zone. Having said that, Jacco is usually making far more of a fashion statement than denim. :)




This is just a short note on the parameter introduced in the 11gR2 called _connect_by_use_union_all. I’ve noticed it for the first time in Doc ID 7210630.8, which gives a brief overview of the changes made to the way CBO generates plans for hierarchical queries. As usually happens, the change helps to one problem, but produces [...]

oraclebase's picture

PL/SQL Masterclass in Hong Kong?

Two days after getting the final confirmation for the Singapore, Sydney and Melbourne dates and the cancellation of the Hong Kong date things have changed a little… Maybe…

I’ve been asked to try and reschedule the Hong Kong date (October 25th-26th). I’m in the process of trying to check the implications of the flight changes now. If it were a Eurpoean date it would be a no-brainer, but since it’s the other side of the world it has to be combined with the existing trip or there is simply not enough time to get home and fly back out again.

I’m hoping to know by tomorrow if the date can be rescheduled. Watch this space.



tanelpoder's picture

New online seminars – Advanced Oracle Troubleshooting v2.0 Deep Dives

As I mentioned in a previous post, I won’t be doing much flying anymore and so am changing all my seminar offering to online seminars.

So, I’ve changed and re-arranged my seminar content into self-contained 4-hour deep dives and thanks to the online nature (no travel needed), people can choose which days they want to attend. If you’re interested in latch contention only, you can attend the Latch Contention deep dive for example etc. Or you can still attend all the deep dives. The cool thing is that these deep dive sessions take only half a day, too (and are priced accordingly). That way you don’t have to skip work for the whole day (or week) and still can get some of your daily work done too. Hopefully it makes your life a bit easier when getting approval to attend the sessions.

As the main feedback from my seminars has been that “there’s too much to learn” within the short 2-3 days I used to do my seminars in, I have arranged the material so that there will be more time to go deep into the subject area. Also, I have planned plenty of time for questions & answers (1 hour out of the 4 hours is planned Q&A sessions and attendees can also ask questions any time during the lecture & demos).

It looks like I will only offer my Advanced Oracle Troubleshooting v2.0 class online this year. I will probably schedule my Advanced SQL Tuning deep dives in January/February 2011 and the Advanced Troubleshooting class again in March/April and so on (until I go public with my other business, when I won’t have time for full length training anymore).

You can check the current schedule and pricing out here:

Here’s a brief outline of individual half-day Deep Dives I offer:

  1. AOT deep dive 1: Systematic approach for Advanced Oracle Troubleshooting
  2. AOT deep dive 2: Troubleshooting physical IO and buffer cache issues
  3. AOT deep dive 3: Troubleshooting commit, redo, undo and transaction issues
  4. AOT deep dive 4: Troubleshooting Oracle SGA/PGA/UGA and OS memory issues
  5. AOT deep dive 5: Troubleshooting shared pool and library cache issues
  6. AOT deep dive 6: Troubleshooting enqueue lock waits and deadlocks
  7. AOT deep dive 7: Troubleshooting latch contention
  8. AOT deep dive 8: Troubleshooting Mutex and “cursor: pin” contention
  9. AOT deep dive 9: Troubleshooting complex hangs and spins
  10. AOT deep dive 10: Troubleshooting crashes, bugs and ORA-600/ORA-7445 errors

So, sign up now, seats are limited ;-)


To prevent automated spam submissions leave this field empty.
Syndicate content