Skip to main content

My Ninth OOW - and first as an Oracle employee

Before heading out to San Fran last Thursday for meetings and the ACE Director briefing, I happened to pick up my Oracle PL/SQL Advanced Programming with Packages (my second book, published in 1996, immediately following up from the 1995 publication of my Oracle PL/SQL Programming text at IOUW in Phillie). 

Here’s what I found taped inside the cover:

That was the first Oracle Open World ever, and I was still working for SSC, the consulting firm I joined when I left Oracle in 1992. Must confess: don’t remember anything about OOW96, but I know how to count. Here’s some OOW math:

I have attended OOW96, 97, 98, 99 2000….2014: nine Oracle Open Worlds. During that time I published nine books on PL/SQL and one (with primary author Guy Harrison) on MySQL Stored Procedures; I wrote one or two automated testing frameworks for PL/SQL, and racked up 2M miles on American Airlines - ignoring my family as I traversed a sizeable chunk of human-occupied Earth presenting on PL/SQL.

OOW14 was, however, the first OOW I have ever attended as an Oracle employee. 

And it was a blast.

As I mentioned above, I went to the ACE Director briefing on Friday, and gave a short presentation at the enviable timeslot of 4:45 PM (just before food and beer. That’s tough). Ironically, this was the first ACE D briefing I’d ever attended - and I am no longer an ACE Director. I’d never gone to any when I was an ACE D, because (a) that would mean that I‘d be away from ome and family even longer, and (b) since I only knew (and was interested in) one thing (PL/SQL), the vast majority of presentations would be, well, boring.

Anyway, that was then, this is now. So at the briefing, I talked about the importance of promoting Oracle Database as much more than a “dumb” datastore, and instead as a powerful platform for application development. Lots of nodding heads. However, the message that seemed to resonate most strongly with my friends was that we, Oracle, need them (and you) to help us demonstrate the power of Oracle Database, and help users leverage more of its advanced features.

The next (Saturday) morning was, to be honest, the highlight of my entire trip - and OOW hadn't even started. That's because I spent three hours with a good friend in the Purisima Open Space Preserve, on a seven mile walk in through a redwood forest. I like Oracle a whole lot, but technology can't hold a candle to the connection I feel these days to trees (and my granddaughter).

But then my feet got sore. So we drove along US1, had a delightful lunch looking at over the ocean….and then I headed north. A day later, there I was at Moscone. Not as beauitful. Not as peaceful. Not quite as inspiring. But still pretty amazing.

I imagine that a few of my most devoted fans would like to read a day by day account of my time in San Fran. Most of you would, however, get bored fast. So, instead I offer the following highlights:

ACE Dinner

Even though I had to “give back” my ACE D title when I rejoined Oracle, Victoria Lira, the excellent director of the ACE program, was kind enough to invite me along, anyway. It was a great evening! These dinners are always fantastic, because whatever the quality of drinks and food, you have within close proximity literally dozens of the finest Oracle tech minds in the world. Wow. These days I am seeking feedback on my ideas for evangelizing application development features of Oracle Database, and there was no shortage of advice and cautionary tales. Then the program began and for me the highlight was an incredible acrobat/contortionist. I do a little bit of stretching and ab work each day, and that just made her flexibility and control even more impressive to me.

YesSQL Celebration

I’d been thinking that when I joined Oracle I would more or less continue what I’d been doing: writing, writing about, training and presenting on PL/SQL. And sure I will be doing that. But in addition I have been asked to, well, shake things up a bit. Try some different ideas for getting application developers excited (again) about PL/SQL and even more important, SQL. And one thing I’d noticed as I looked around Oracle, our websites, and our community is that SQL has become a bit taken for granted, like the air we breathe.

So I figured the first thing we should do is remind ourselves of how amazing this technology is and how important is all of our work with the technology.

And thus was born YesSQL! A celebration of SQL and PL/SQL. The idea for this event was, roughly, to NOT talk about products and features, but instead to share stories from our evangelists, users and developers (the people who build SQL and PL/SQL and the optimizer and APEX and so on).

About 500 people attended, which was great. My Macbook started throwing a hissy fit right before we were about to start. That was not so great. And since I organized the event pretty much on my own, it was a bit hit-and-miss. I also didn't have my act together enough to make sure we took lots of pictures and video. I promise to do a (MUCH) better job next year!

We started off with Tom Kyte's reflections on SQL and NoSQL. With sharp insight, he noted that the whole point of SQL was to replace "NoSQL" databases, such as VSAM. Then the years go by, some people forget the power of SQL, the cycle spins around's time for NoSQL again! Then another few years go by, and now SQL is "sexy" again, because even the Hadoop developers recognize that it is really, really hard to write applications without a powerful set language like SQL. Hence, NewSQL!

Then a greeting from Thomas Kurian, Executive Vice President of Product Development and former PL/SQL Product Manager (!), which you can watch here. But in the meantime, enjoy a snap of Thomas eating from a slice of Happy YesSQL Celebration cake:

Next was, for me, the highlight of the evening: stories from Andy Mendelsohn, EVP of Server Technologies. He's been responsible for Oracle Database and all of its surrounding technologies for the last twelve years, but before that, let's see: he built the B-tree indexing code, and was one of the co-designers of the Oracle PL/SQL language. Been there, done lots of things. And shared some very entertaining stories of the early days at Oracle.

We also heard from Andy Witkowski, SQL Architect and (for YesSQL) the NoSQL Pirate. Mohamed Zait, who heads the optimizer team, shared a very amusing video (sorry not able to share it, at least not yet) showing how his time comes up with (and rejects) new ideas for the optimizer. Bryn Llewellyn and Iyer Iyer Chandrasekharan (PL/SQL PM and Dev Manager, respectively) talked about life with PL/SQL. Mike Hichwa reminisced about the early origins of APEX.

And that's not all! Maria Colgan of Optimizer and now In Memory fame; Graham Wood, SQL performance tuning wizard; Joel Kallman, APEX dev manager; all chipped in with stories and reflections on life with Oracle Database.

But then, sigh....we ran out of time. And not a single user shared their story. Which counts as a bit of a failure for me. My apologies! We will, however, start gathering stories BEFORE OOW15 and at YesSQL15, we will put users first and listen to your stories, before we let Oracle people fill up the evening.

DinoDate, aka, PL/SQL the Scripting Language Liberator

I gave a talk on PL/SQL new features in 12.1 and that was great fun, as usual. But I also did a talk with Christopher Jones, who is a PHP pro, as well as all-around strong Oracle technologist (and PM). We wanted to show how you can leverage Oracle Database as an application development platform, and not simply as a "dumb" datastore.

And since I was involved, we needed to break out of the emp-dept HR demo model, because it bores the heck out of me and I hate to be bored.

So we dreamt up DinoDate, the premier dating site for dinosaurs. After all, the meteor is falling. Time is running out. Must find a mate fast

[Note: sure, we could have made the site prettier and likely will, but focus group surveys indicated a distinct disinterest by dinosaurs in user interface design.]

And, of course, Chris decided that if I would insist on such silliness, that there was a price to be paid (by me):

So Chris built the site in PHP, talking advantage of the "usual" client side or macro techniques for improving performance and scalability with Oracle Database, including:
  • End-to-end tracing
  • Connection pooling
  • Bind variables
  • Statement Caching
  • Not auto-committing
  • Pre-fetching
  • Enabling the Client Query Result Cache
Still, performance was not the greatest. Enter, Steven Feuerstein, PL/SQL Evangelist extraordinaire! "What else can I do?" asks Chris, and then we (both) showed how to improve performance and scalability, and also add significant functionality to users in search with:
  • Consolidate multiple server calls from PHP into single stored procedure.
  • Leverage Oracle Advanced Queueing to remove processing bottlenecks.
  • Reduce network traffic with bulk processing.
  • Use Oracle Text to easily implement fuzzy searches.
  • Use Oracle Spatial to allow searching for nearby dinosaurs.
Overall, I thought it went quite well. We will explore using DinoDate as a demo platform for our various technologies, and hope to make all code available to you soon.

The session was recorded and when I can figure out where it is, I will let you all know.

Doing PL/SQL from SQL: Correctness and Performance (by Bryn Llewelyn)

My favorite session at OOW was Bryn Llewellyn's Doing PL/SQL from SQL: Correctness and Performance (CON8269):

"A SQL statement that calls a PL/SQL function is slower than one that defines the same result by using only SQL expressions. The culprit is the SQL to PL/SQL round-trip. Its cost can be reduced by marking the function deterministic, by invoking it in a scalar subquery, by using the PL/SQL function result cache, or by some other caching scheme. With caching, you can’t predict the result set if the function isn’t pure, so it would seem that caching should be used with caution. This session shows that a function must have certain properties to be safe in SQL, even without caching—in other words that the possible semantic effect of caching is a red herring. You can’t rely on how often, or in what order, an uncached function is called any more than you can rely on group by to do ordering."

It was an impressive piece of research (actually interviewing developers who built specific features), deep and clear thinking, and convincing (though somewhat limited) benchmarks.

I encourage you to download the presentation here, but I offer Bryn's summary below:
  • A PL/SQL function must be statement-duration-pure if it is to be called from SQL
  • In most cases, this simply falls out of what you want to write
  • Mark it deterministic when it is (SSQ when not? Hmm...)
  • By its very nature, it’s likely to be self-contained – so use pragma UDF, or equivalently write its whole definiIon in the with clause
  • You might well and up with a function that both is marked deterministic and has pragma UDF. Don’t worry. The performance benefit of the SQL-friendly compilatIon mode is not compromised by computatIons done for caching pragma UDF is the hands-down winner ‘cos its benefit is
  • independent of cardinality
  • If you find a potenIal use for a PL/SQL function that does SQL, and that is called from SQL, think hard... very hard
  • Might you, after all, express the whole thing in SQL?
  • Are you confident that you’re not getting nonsense by violations of read-consistency
  • Then, and only then, go ahead
  • Result-cache it, or not, in the light of cardinality considerations
  • Use the scalar subquery locuIon as well when you expect to meet only hundreds of distinct values in any one statement execution
Well, there's more I want to say and share with you about OOW14, but I think I will publish this before too many days pass. 


Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers. In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply. All the code shown in this example may be found in this LiveSQL script . How to Get a Mutating Table Error I need to implement this rule on my employees table: Your new salary cannot be mo

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p