Oracle

I’ve written about dynamic sampling in the past, but here’s a little wrinkle that’s easy to miss. How do you get the optimizer to work out the correct cardinality for a query like (the table creation statement follows the query):

select	count(*)
from	t1
where	n1 = n2
;

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
)
select
	mod(rownum, 1000)	n1,
	mod(rownum, 1000)	n2
from
	generator	v1,
	generator	v2
where
	rownum <= 1e6 ; 

If you’re running 11g and can changed the code there are a couple of easy options – adding a virtual column, or applying extended stats and then modifying the SQL accordingly would be appropriate.

Some time ago – actually a few years ago – I wrote a note about the hint /*+ gather_plan_statistics */ making some informal comments about the implementation and relevant hidden parameters. I’ve recently discovered a couple of notes from Alexander Anokhin describing the feature in far more detail and describing some of the misleading side effects of the implementaiton. There are two parts (so far): part 1 and part 2.

Reading Data Sheets
If you are in a position of influence affecting technology adoption in your enterprise you likely spend a lot of time reading data sheets from vendors.  This is just a quick blog entry about something I simply haven’t taken the time to cover even though the topic at hand has always be a “problem.” Well, at least since the release of the Oracle Exadata Database Machine X2-8.

We’ve had a couple of short lived, but very inconvenient I/O latency issues recently. I’ve been using the awesome Latency Heat Map Visualization by Luca Canali as one of the tools to investigate this.

I’m guessing this isn’t the type of I/O latency heat map most people would want to see from a production system.

How do you want to start the day? I’m guessing it’s not to be called out to the front of the room by a speaker and used as a guinea pig, while they ask you trick questions to make you look stupid. Tom Kyte, you will pay. Oh yes! You will pay!!!

The sessions I attended on day 2 were:

So Day 1 (part 2) didn’t go to plan because I forgot to take my camera or my phone to the party.

Suffice to say, lots of food, lots of drink (for those that do) and most importantly lots of dancing. Yes, I once again murdered the traditional dances of Bulgaria, but it’s the takling part that counts right?

I had good intentions of leaving early, but I ended up chatting about Oracle until about 02:00. Day 2 is going to be tough…

Cheers

Tim…

Last night we all got together to eat some food and chat. Julian Dontcheff is practically a savant where Bulgarian Poetry, World Cup match results and random Oracle facts are concerned. Although Christian Antognini was pretty impressive on the random Oracle facts too.

I didn’t have any presentations today, so I got to sit and watch. I’ve done loads of typing, mostly of syntax for 12c features, but it’s not really stuff that is worth posting, because I have no way to validate it out, so I’m just going to keep it as a reminder for when I get hold of 12c and can try it out.

The sessions I went to included:

Thanks to everyone that submitted abstracts for our upcoming E4 conference. Unfortunately, there were more quality submissions than we had room for. Maybe next year we should expand the event to 3 days. :) But in the meantime, we have assembled what I believe is an excellent line up of speakers. I’ll just mention a few highlights here:

Tom Kyte will be doing the keynote. Enough said!

Maria Colgan and Roger MacNicol will be doing a 3 hour combined session on smart scans. Maria will attack the topic from the top down (optimizer) point of view (since she is the product manager for the optimizer) and Roger will be attacking it from the bottom up (since he is the lead developer for the smart scan code). This should be an awesome session and Tanel Poder has already said he was going to line up the night before.

It’s stupid o’clock in the morning and I’m waiting for my taxi to arrive. Considering how close Bulgaria is, it takes me a very long time to get there.

I am a mix of excited and nervous. This is my first conference this year, so all the usual insecurities are in full effect, from fear of flying to the constant nagging thoughts that perhaps I don’t know anything about Oracle and maybe I shouldn’t be on stage acting like I do.

I’m sure it will go OK and it will be nice to meet up with the gang again.

Cheers

Tim…

Dominic Brooks published a note recently about some very nasty SQL – originally thinking that it was displaying a run-time problem due to the extreme number of copies of the lnnvl() function the optimizer had produced. In fact it turned out to be a parse-time problem rather than a run-time problem, but when I first read Dominic’s note I was sufficiently surprised that I decided to try modelling the query.

Unfortunately the query had more than 1,000 predicates, (OR’ed together) and some of them included in-lists.  Clearly, writing this up by hand wasn’t going to be a good idea, so I wrote a script to generate both the data, and the query, as follows – first a table to query: