Wednesday, December 20, 2006

11g PL/SQL enhancements presentation

While a number of bloggers have reported on what they heard at conferences regarding 11g, in many cases the presentations by Oracle employees have not been made available. I believe this is due to Oracle not wanting to be implied as committing to ANYTHING.
While doing an information hunt, I came across the NYOUG presentations here which includes the pdf of a Tom Kyte presentation on 11g PL/SQL enhancements.

If there is a policy of not releasing these presentations, there's a chance Oracle will ask for it to be removed from the site so best "get it while its hot".

May ratings are :
  • DML in triggers is faster - 2. We are putting that code into packages anyway, aren't we ?
  • Fine Grained Dependency Tracking - 8. Very useful for impact analysis.
  • Native compilation - ?. Depends on whether there is a significant performance benefit.
  • Intra-unit inlining -5. Anything that allows use to write more readable code with no performance hit is bound to be good.
  • Function result cache - 7. Looks good, but we'll have to watch that RELIES clause
  • SQL result cache - ?. If it is a straight query with no variables, isn't it a materialized view ? Wonder how it works with variables and/or SYS_CONTEXT
  • Compound trigger -?. Sounds good, but I can see people trying to put validation in there that doesn't cater for concurrent table updates.
  • Dynamic SQL -3. I don't see much use of it, but anything that makes it more practical to keep the SQL in the database and away from those Java developers must be good :)
  • Fine-grained access control for UTL_FILE/UTL_HTTP/... - 8. More security GOOD.
  • Regular expression enhancements-?. Not something I've used.
  • Super-?. Also not something I'm familiar with.
  • Read only table-2. Check out the Dizwell article on this. Is it really Read Only (and could we apply it to individual partitions ?)
  • Disabled trigger-1. Don't we test these things ?
  • Trigger firing order-1. Do we need to make triggers MORE complicated ?
  • "When others without raise" warning-7.
  • Direct use of sequence.nextval in PL/SQL-7.
  • "Continue" command for loops-2. Spaghetti code.
  • Named parameter referencing for functions called from SQL-7. Readability improvement.
So fine-grained dependency tracking is the big ticket item as far as I'm concerned, with the advantage that as soon as 11g is out, you can copy your database structure to a development copy of 11g and get that impact analysis benefit long before you upgrade your production environment to 11g.

7 comments:

APC said...

This version of the presentation does not include what Tom described as the "Killer feature" when he talked on the same topic at the UKOUG Annual Conference: version control in the database. This will allow us to have different "editions" of our software (PL?SQL, views, etc) in the same database, thereby providing a mechanism for getting a release correct in the production database whilst the users continue to use the previous release.

So does the fact that this feature has dropped from Tom's Decemeber presentation mean anything?

APC said...

Perhaps it means that the lawyerly caveat really does apply and there will be a lot of changes between and launch day, whenever that may be.

Cheers, APC

Gary Myers said...

They've only just gone into beta, so they shouldn't be dropping features yet :)
I suspect that feature is more of a 'whole of database' change than a PL/SQL one. Sort of like Workspace Manager on growth hormone.
Wonder whether that will be an extra cost option....

APC said...

Well, it mainly affects PL/SQL and related stuff (triggers, views): tables are excluded from it. So it isn't really "whole database".

Having read the PDF it looks quite similar to the one Bryn Llewellyn presented at Open World in October (possibly it is the same one but I missed the actual session).

Cheers, APC

Kevin Closson said...

The 11g beta program commits participants to not so much as state they are beta program participants. This is why there is not much information out there about 11g. To that end, you can quote me as saying, "I can neither confirm nor deny whether I am or am not an 11g Beta program participant."

Good post about PL/SQL, BTW.

Gary Myers said...

Hmmm. My understanding was that tables would be part of 'editioning', not in regard to data content, but to things like column visibility. A new 'edition' of a table could have a column that is not visible to a SELECT * run under the old edition.
Of course I suspect this would be a matter of VPD and/or views and the underlying data segments are the same. As such, this may just be semantics - PHYSICAL tables are not part of editioning, but LOGICAL tables are. Sort of like Workspace Manager not being 'magic' internal stuff, but bunches of triggers and hidden columns.

Edgar Chupit said...

I think that Dynamic SQL Functional Completeness is very fantastic feature for all PL/SQL developers. Because for years we were limited with ref cursor incompleteness.
We were unable to describe result set of ref cursor, we were unable to create "abstract" function that will be able to fetch from any ref cursor, and because of that we couldn't use ref cursors to the great extend in APEX.
We were unable to re-execute ref cursor without causing soft-parse. The only option was "open rc for select xxx", now as I can see from this presentation, I can convert ref cursor to cursor id, bind new variables and re-execute cursor. And this can save us from parsing cursor over and over.
I'm looking forward to get my hands on 11g :)