Oracle OpenWorld – 11 Things about 11gR2

So this was Tom Kyte on at the end of the day, and maybe because it was the last presentation of a long day, I think the room was possibly not as busy as I’d seen it last year. However, this was another good show.

Tom’s choice of the 11 features was quite eclectic, there was some stuff I had not heard about. Not many of these were the whizz-bang features that the marketeers would like to bang on about. Some of them seemed quite small, but with every example you were left thinking oh yeah, I can see how that makes life better.

There were actually only 10 different topics. Tom felt that Edition Based Redefinition is so good, that it needed to take 2 of the 11 spots in his presentation!

I was gobsmacked by how few people have heard of this. Tom really thinks this is the one feature that if your manager asks “Why should we upgrade to 11gR2 ?” that you should respond with. I really think it has the potential to be an absolute game-changer. It’s the killer feature.

diy parallelism

This uses dbms_execute_parallel

incrementally modify a table in parallel break up table into chunks and then do several of the chunks at once – as opposed to what parallel query does which is run all parallel threads at once

each of the bite sized chunks commits independently so that an update does not lock up an entire table at once.


Tom’s really liking listagg, saves lots of lines of code from 9i and 10g

likes nth_value

execute on a directory

external tables allow for a preprocessor – program is run when you select from external table

use this to decompress a file when you perform the select on an external table that uses the execute

Tom showing how to do an ls in a directory running just select * from ls – which is using this feature to produce the listing of a directory

Recursive Subquery factoring

ANSI SQL replcement for connect by

Improved Time Travel

Tom is talking about flashback data archive setup on table by table basis. 11gR2 improves the amount of operations you can undertake on a table that has flashback data archive set on it.

You’ve got mail

file arrives in directory and you need to run a process on it. File watchers

deffered segment creation

many apps creation 1000’s of tables, lots of them empty, with deffered segment creation none of these empty tables will take any storage.

flash cache

SSD acts as level 2 cache, with SGA level 1, faster than disk but cheaper than memory, larger capacity than main memory

parallel improved

parallel query introduced in 7.1.6 1994 parallel query was designed to have 1 user use as much of the power of a machine as possible

automated degree of parallelism

a sql statement is parsed and if a serial plan takes less time than a threshold that is dba settable then the serial execution will be run, if it’s going to take longer then a parallel execution plan is attempted.

parallel statement queueing will pause a query if not resources are available

in-memory parallel execution uses an algorithim based on size of table being read to determine whether it’s data is read into memory, spread over nodes in a cluster

Edition Based Redefinition

Not many people have actually heard of this, Tom polled the audience. He thinks it’s the Killer feature. Tom went on to give a quick a demo, but did not really have time to do this justice.


One thought on “Oracle OpenWorld – 11 Things about 11gR2

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s