The final installment of this series deals with Tom Kyte’s top 10 features in 11.2 and Wolfgang Breitling’s “seeding statistics”.
Tom Kyte – Top 10 new 11.2 features
I was a little late so didn’t get the essence of this. Seems to automatically do what Tom described as “poor man’s parallelism” in his effective oracle by design book. Take a huge table and instruct Oracle to subdivide it into non overlapping rowid ranges and then give it a command to work on these ranges in parallel. All of this was possible before, but required manual skripting-now it should happen without that hassle. As always, this turns an atomic update into a number of atomic operations. Need to roll back: restore or flashback database.
2) More analytics:
listagg: create a whatever separated list
nth_value: return n-th field of a partition (as in max(cola) over (partition …))
3) Execute privilege on directory
Allows us to execute code (with ext table preprocessor) in a directory
Wasn’t too clear if we we need to get the output from the preprocessor to standard out?
Leaves a number of questions, most importantly: what about security? That preprocessor executes anything within the privileges of the oracle user. What about rm -rf $ORACLE_HOME if some malicious code replaces your own script?
4) Recursive subquery factoring
As Tom said: the recusrive subquery factoring is easier to understand if you don’t know connect_by yet. Otherwise the whole syntax is confusing at first, which I have to admit, and neither does it look more elegantly. I personally like the connect_by more, even though I haven’t used it for 22 years – apparently the feature has been in the database since version 2 of the kernel. The syntax of the new recursive subquery factoring can be found in the SQL Language Reference as part of tbe select statement
With the new syntax, you get the first (root) element, called anchor member. After you’ve got it, you add the recursive members. Main use: now ANSI compatible, don’t need to change the syntax for compatibility with db2 and sybase. Don’t know why this is hyped but never mind.
This is the second part of my review of UKOUG conference 2009, day 1. Check out the first part as well! This picks up exactly where I left part 1 after Tom Kyte’s session.
I remained in hall 1 to see the great presentation of James Morle. I have to say that even if you aren’t familiar with the subject of his presentations you should go to see him-he’s such a great presenter. The prank of today was a bottle of Oracle wine, distributed into 5 glasses (“everyone help yourself to your portion of Oracle”) to simulate the idea of virtualisation. James then offered insights into some of the VMWare internals alongside some competing offerings, mainly from Oracle and Cytrix (Xen) and Red Hat (KVM and RHEV). It seems the golden age of para-virtualisation is over, with AMD and Intel releasing so many features in their processors for hardware assisted virtualisation that VMWare’s offering caught up performance-wise. Personally I still love Xen (and I am writing this article on a virtual machine!) because it gives me all the performance I need on cheap hardware. I also don’t think the nehalem processors will make it into laptops, my venerable openSuSE 11.1 will support me for some more time. When it comes to performance, anything in running on vmware in userland will roughly match a physical box, but as soon as you enter kernel mode, due to modifications VMWare has to make in order for multiple instances of OSs to coexist. Again, this wasn’t tested on VT-d or IMMOU capable processors so your experience might be different. Overcommitting memory might work well with other than Oracle workloads, but James’s advise is not to use this feature in production.
All in all a very balanced presentation with the usual laugh at the beginning.
After this presentation I had some lunch and managed to see the folks from CERN which was interesting again. A great many of them were there to actually present and I briefly met Eva, their streams specialist who is responsible for pushing a lot of data from their site (tier 0) to sites all around the world (tier 1) for distributed computing. If I remember correctly this is part of the EGEE network but might be wrong there.
RAC round table
This was chaired by Julian Dyke and David Burnham (actually Julian had a problem with his voice which left him so David took over) and saw a great attendance from some reknown Oracle specialists: Piet de Visser, Phil Davies, David Burnham, Alex Gorbachev, Luca Canali, Mark Bobak, Jonathan Lewis to name just a few.
This was my first round table and I didn’t know what to expect. I greatly enjoyed the atmosphere up to the point where the discussion about block level replication started to drag on. The questions (and some answers) were:
- Block corruption with AIX 6.1 on p595 LPARs when accessing freshly created database (in ASM). Requires a lot of tracing on the net*8 layer all the way up a strace of the oracle process, can’t be diagnosed without a system. The block corruption didn’t result of a restore from a compressed backup, the system was freshly created via dbca.
- Update from Larry Carpenter about future of data guard/streams and golden gate. Very interesting stuff, in essence streams won’t go away and Golden Gate receives Log Miner code. Golden Gate will remain an independent product and won’t be assimilated in the streams group.
- There are plans in Oracle to extend RMAN in order to be able to restore across endian boundaries (not to be confused with the convert command!) Before that can happen, Phil Davis was sharing details of work on a project where Golden Gate could be used to cut downtime a lot, capturing changes while the rman convert was still running on the destination platform
- Using san block level replication: mirroring oracle home and database to remote host still requires full license, unless you don’t mount it (or mount it only when the primary site is completely down)
- Phil Davies: does anyone know why in clusterware active/passive setups the cluster database resource doens’t write a trace file in $CRS_HOME/log/hostname/racg ? I didn’t, neither did anyone else. That question didn’t cover 11.2, but I didn’t have time to test cold failover clusters with 11.2 yet.
- RAC ONe node: really just a cold failover, additional benefit is mainly for maintenance. For RAC One Node you have to license only one node (apparently). Aimed primarily against VMWare
I will add yet another post about the final two sessions I attended about Tom Kyte’s top 10 11.2 features and Wolfgang Breitlings seeding statistics.
What a great decision to go to UKOUG #tech_ebs on Monday! I’ve managed to see presentations from Wolfgang Breitling, James Morle and Tom Kyte (twice). So that was good. But the even better part was catching up with Julian Dyke, Piet de Visser (I promise I will read more of your blog!), David Kurtz, the guys from Nominet (feeling sorry for Jason who was on remote support duties today and I couldn’t see him), David Burnham and the e-DBA bunch. Thanks for the nice orange juice Mike! I saw Joel Goodman with Harald von der Brederode from a distance but couldn’t get through the crowd to say hello. Connor McDonald also attended the conference, but we have never really been introduced (he also used to work for e-DBA for a short period of time).
Over the following days I will try and milk it to get a number of blog posts, one per session I attended-the first is already done-see below.
Now, the conference certainly is a highlight, no doubt about it. I started my day with Tom Kyte’s “what are we stll doing wrong?” I immediately wanted to send all the “developers” I am working with to the talk. Simple, basic topics that haven’t changed for ages. Use of bind variables, simple non verbose but concise code. Oh how much I’d like to see this. No more “I didn’t write this query, hibernate did” excuses or “I don’t know where in the application that is executed”. You have to be a Java developer to appreciate the “beauty” of such code. As a DBA, I am not so sold on the whole thing. Neither do I like the term “legacy DBMS” for mature products such as Oracle, but the same applies for DB2 and Sybase. I also think that each developer I have to work with should be force-fed “effective Oracle by Design”, and I am going to have an exam with them in batches. But unfortunately that won’t happen.