Sunday, October 26, 2008

McCain is oh so proud of cute, little Sarah

Is anyone else bothered by McCain's repeated use of the word "proud" to describe how he feels about his vice presidential candidate, Sarah Palin?

As in:

"I'm so proud of the way she ignites the crowds. The way she has conducted herself in my view is incredibly admirable," McCain said.

Let's suppose for a moment that McCain hadn't plucked Palin out of relative obscurity (at the urging of the ultra-right in this country, most notably Rush Limbaugh, who started pushing for her as the GOP VP candidate back in February 2008).

Suppose he had picked Mitt Romney or Rudy Giuliani.

Can you imagine John McCain saying over and over again:

"I'm so proud of Rudy, the way he responded to 9/11."

Doesn't that sound just a wee bit condescending? Like McCain is her dear, old Uncle, patting her on the head?

Wednesday, October 08, 2008

Doing SQL in PL/SQL: a key resource from Bryn Llewellyn

One of the highlights of Oracle Open World 2008 for me was the presentation by Bryn Llewellyn (PL/SQL Product Manager) on 'Doing SQL in PL/SQL: best and worst practices.'

Bryn surely has the most thorough and clear understanding of the PL/SQL language of anyone I have met (definitely including me).

His talk was detailed and precise (and maybe just a little bit overwhelming. He needed twice the time allotted) on this most important topic.

The bad news about this talk is that most PL/SQL developers will never be able to hear Bryn present it.

The good news is that everything he talks about (and more) may be found in a newly published whitepaper of the same name.

Simply visit the Oracle Technology Network PL/SQL Homepage and click on the hyperlink for that paper.

Notice also that Bryn has published an in-depth whitepaper on SQL injection, a very serious and widespread security risk in many applications.

Sunday, October 05, 2008

Quick Tip: Oracle11g function result cache

I just realized it's been a little while since I offered some PL/SQL thoughts on this blog. Sorry, the election campaign is twisting around my priorities. So how about if I share with you what I believe is far and away the most important new feature for PL/SQL developers in Oracle11g: the function result cache.

Suppose you have a table that is queried frequently (let's say thousands of times a minute) but is only updated once or twice an hour. In between those changes, that table is static. Now, most PL/SQL developers have developed a very bad habit: whenever they need to retrieve data they write the necessary SELECT statement directly in their high-level application code. As a result, their application must absorb the overhead of going through the SQL layer in the SGA, over and over again, to get that unchanging data.

If, on the other hand, you put that SELECT statement inside its own function and then define that function as a result cache, magical things happen. Namely, whenever anyone in that database instance calls the function, Oracle first checks to see if anyone has already called the function with the same input values. If so, then the cached return value is returned without running the function body. If the inputs are not found, then the function is executed, the inputs and return data is stored in the cache, and then the data is sent back to the user. The data is never queried more than once from the SQL layer - as long as it hasn't changed. As soon as anyone connected to that instance commits changes to a table on which the cache is dependent, Oracle invalidates the cache, so that the data will have to be re-queried (but just once). You are, as would be expected inside an Oracle database, guaranteed to always see clean, correct data.

Why would you do this? Because the performance improvements are dramatic. In the 11g_emplu.pkg script (available in the demo.zip at PL/SQL Obsession), I compare the performance of a normal database query via a function to a function result cache built around the same query and I see these results:

Execute query each time Elapsed: 5.65 seconds.
Oracle 11g result cache Elapsed: .30 seconds.

Isn't that just amazing and incredible and wonderful? Here's the original version of the function (over 5 seconds):

PACKAGE BODY emplu
IS
FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE)
RETURN employees%ROWTYPE
IS
onerow_rec employees%ROWTYPE;
BEGIN
SELECT *
INTO onerow_rec
FROM employees
WHERE employee_id = employee_id_in;

RETURN onerow_rec;
END onerow;
END emplu;

and here's the result cache version:

PACKAGE BODY emplu
IS
FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE)
RETURN employees%ROWTYPE
RESULT_CACHE RELIES_ON ( employees )
IS
onerow_rec employees%ROWTYPE;
BEGIN
SELECT *
INTO onerow_rec
FROM employees
WHERE employee_id = employee_id_in;

RETURN onerow_rec;
END onerow;
END emplu;

Can you see the difference? Not much of a change, right? I just added that single RESULT_CACHE line. And notice that I would not have to change any of the code that was already calling this function.

Here's the bottom line regarding the function result cache: get ready now to take advantage of this feature. Stop writing SELECT statements directly into your application code. Instead, hide your queries in functions so that you can easily convert to result caches when you upgrade to Oracle11g.

Friday, October 03, 2008

Sarah Palin, the lipstick-coated, pitbull-mannered Stepford Wife

I watched the VP debate. Yep, there is no doubt about it: we now know that if Sarah Palin is given a month to prepare for a debate, then she will not totally screw things up. In fact, what we found is that she will be able to pretty much ignore whatever question is presented to her and instead spit out her pre-programmed statements.

And she will do it in an aggressive, condescending manner that indeed makes one think of a pitbull with lipstick and very nice $400 eyeglasses.

Doggone it, but she reminded me so much of a Stepford Wife. She just didn't seem entirely human.

Well, that does it, then. I will vote for Obama.