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.
analytics
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.
1 Comment