Search

Top 60 Oracle Blogs

Recent comments

August 2013

12c subquery factoring

I’ve written a few notes about anomalies in subquery factoring (with subquery) in the past, principally making a fuss about the fact that moving an inline view into a “with subquery” can cause a plan to change even when the internal code moves the subquery back in line. With the arrival of 12c one of my first sets of tests was to rerun all the examples to see how many of them had been addressed. I hadn’t written about as many examples as I had thought, and some of them had been fixed before 12c, but here are few references to a couple of outstanding items that I thought worth a mention:

Interview

The interview I did with Timur Akhmadeev while visiting Moscow is now online. 90 minutes ! Just got through the first 6 minutes and haven’t embarrassed myself yet: http://timurakhmadeev.wordpress.com/2013/08/05/interview-with-jonathan-lewis/

12 notes

This is just a temporary note to advertise the fact that I’ve added Steve Karam’s collation of blog links on 12c features to the top of the list of links to the right of the page (12c Notes).

LAOTN Tour (Southern Leg) : Day off in Lima, Peru

I actually got a day off from travelling and presenting yesterday. I would have loved to see Macchu Picchu, but that would take more time than I had available, so instead I went to see Pachacamac, which is about 30 minutes from the city. It is a really big site, about 10 hectares. It must have looked amazing in its heyday. One part of the site is a cemetery, where they estimate there are 40,000 mummies. What with that and the whole human sacrifice thing, it was kind-of freaky. I shall have to get some more information about it and scare my nephews with the story. :)

I took quite a lot of photos, but they don’t really do the site justice.

The Fall of Hyperion

The Fall of Hyperion is the second book in the Hyperion Cantos series by Dan Simmons.

I’m not really sure what to say about The Fall of Hyperion. On the one hand, I was very interested to see what happened to the characters from the previous book. On the other hand, this book was much less focussed and quite disjointed at times. It didn’t help that it took me a long time to get through it, reading it in small snippets, rather than a few long sittings.

Despite my minor misgivings, I’ve already started the next book in the series and I’m keen to see how this plays out.

Cheers

Tim…

PS. I’ve just checked the dates between this post and the one from the proceeding book. It took about 2 months to get through it. I’m sure that has a big factor on my perception of it.

Bloom Filter

I’ve posted this note as a quick way of passing on an example prompted by a twitter conversation with Timur and Maria about Bloom filters:

The Bloom filter (capital B because it’s named after a person) is not supposed to appear in Oracle plans unless the query is executing in parallel but here’s an example which seems to use a serial Bloom filter.  Running in 11.2.0.3 and 12.1.0.1 (the results shown are the latter – the numbers are slightly different between versions):

Interview with Jonathan Lewis

If you follow my Twitter you probably have heard that Jonathan Lewis visited Moscow in May this year. Thanks to Innova, Jonathan gave a 1-day seminar, and I was one of the lucky people from Russian Oracle community who have been invited to the event.
Even more, I was offered to take an interview with Jonathan which was recorded! It was my first time interview and I was very excited (long before the recording) and scared to death (right in front of the camera) so please forgive me for speaking like a dull. I know that, and it almost doesn’t matter. What matters is Jonathan was brilliant and so excited at times, that 90+ minutes of sitting in a cold room right under air conditioner for me flew like a 1 minute.
Well, enough the words, just enjoy it (this is uncut version recorded in 1 go by the way)!

Fast refresh of aggregate-only materialized views – introduction

This post introduces a series about the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only an aggregate:

create materialized view test_mv
build immediate
refresh fast on demand
with rowid
as
select gby        as mv_gby,
       count(*)   as mv_cnt_star,
       AGG  (dat) as mv_AGG_dat,
       count(dat) as mv_cnt_dat
  from test_master
 where whe = 0
 group by gby
;

Where AGG is either SUM or MAX, the most important aggregates.

In the next posts, I will illustrate the algorithms used to propagate conventional (not direct-load) inserts, updates and deletes on the master table; I will illustrate also the specialized versions of the algorithms used when only one type of DML has been performed (if they exist).

12c Histogram fixes

I posted a couple of examples some time ago of oddities and boundary cases for frequency histograms on character columns. Part of the process of playing around with the 12c Beta was to re-run such cases to see if newer code made any difference. Looking at these examples, one was fixed (or improved, at least) the other wasn’t, so I’ve added a footnote to each and produced this little note to highlight the changes:

LAOTN Tour (Southern Leg) : PEOUG…

Jonathan Lewis‘ sessions (over WebEx) about “Designing Optimal SQL” and “How to Hint” were extremely popular. The room was full and the audio and slides were very clear. I would say approximately 3/4 of the audience were wearing the simultaneous-translation earphones. Everyone looked very focussed! It was a pity Jonathan couldn’t make it in person, but this was a much better alternative than cancelling his sessions!