Database » FeuerThoughts

« Expand/Collapse

FeuerThoughts (10 unread)

October 08, 2008
13:36
Doing SQL in PL/SQL: a key resource from Bryn Llewellyn
» ‎ FeuerThoughts

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.
October 05, 2008
9:36
Quick Tip: Oracle11g function result cache
» ‎ FeuerThoughts
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.
October 03, 2008
14:21
Sarah Palin, the lipstick-coated, pitbull-mannered Stepford Wife
» ‎ FeuerThoughts

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.
September 30, 2008
15:31
Could they at least APOLOGIZE for f#@king up our country?
» ‎ FeuerThoughts

For years now, people on the left have declared the US economy a "house of cards" built on debt and by transferring wealth from the middle class and poor to the rich. I include myself in this group, as you can verify by reading back through my blog.

And for those same years, the media and our elected officials bowed down to the so-called leaders of our great nation: Alan Greenspan, Warren Buffet, T. Boone Pickens and the other exorbitantly-paid CEOs who steered the great ship of the greatest example of the wonders of capitalism. They could do no wrong, they could not earn enough money, and they bristled whenever anyone talked about maybe giving the middle class a tax cut. ("Class warfare!" went the cry)

And for those same years, we doubters of the wonders of the "free market" have been vilified and mocked. Naive! Communist! Ignorant!

Now, sadly, it must be said: We were right. We of the left, we of the Naive Class, we Americans who never believed that a CEO was worth 500 times the value of a software developer in his company, we bleeding hearts who could never accept that 40 million Americans must have no health care, that tens of millions must go unemployed, that children should go hungry in this country: we were right.

And our so-called leaders were wrong. And not just wrong. Complicit, greedy, corrupt and arrogant.

These days, it is the arrogance that gets me the most.

They are so used to running everything their way, that no one even feels the need, the sense of human decency, to apologize.

Alan Greenspan, who allowed the economy to drift into this horrible state, is no wise man. He is a smart man who knew how to get his bread well buttered. And he does not apologize for his part in this mess.

William Jefferson Clinton, who signed into law deregulation of the financial industry that made this collapse inevitable, is no friend of the people. He is a brilliant politician who always sided with Wall Street. And he does not apologize for his part in this mess.

All those CEOs (from both failed and failing companies), who filled their boards will yes-men and yes-women who agreed to the most obscene pay packages, are not wise men. They are greedy bastards who will not apologize for their parts in this mess.

Keep it up, Leaders of the Free World. Make no apologies, accept no blame and stuff your pockets with every dollar bill (or euro) in sight. Then build some really big walls.

Because if the collapse continues, and if the bailout mainly bails YOU out, then I predict that the American people will shed their sheeps' disguises. They will come boiling out of their homes, full of anger and desperation, and I wonder if any of the walls you build could be tall enough to stop them.
September 14, 2008
13:40
Another aspect of the Bush Legacy
» ‎ FeuerThoughts

A world-wide race to arm rather than feed our citizens....
US in push for arms deals
The Times, citing Defense Department sales data through the end of August, reported that countries newly reliant on the United States as a primary major weapons source included Argentina, Brazil, India, Iraq, Morocco and Pakistan and former Soviet republics Azerbaijan and Georgia.

Together the countries signed $870 million worth of arms deals with the Bush administration from 2001 to 2004, but in the past four fiscal years the total had increased to $13.8 billion.
From $870M to $13.8B! Fortunately they are going to the most stable and democratic of nations, so we don't have to worry that someday some of these weapons might be used to kill our own citizens.
Seems like George W (or, to put a finer point on it, Dick "Watch Your Head" Cheney) is making sure that all of his friends in the military-industrial complex will be taking dumps in solid gold bathroom fixtures for the rest of their unnatural lives.
September 11, 2008
18:02
A week in life of Travelin' Steven
» ‎ FeuerThoughts

BODY {font-family="Arial"} TT {font-family="Dark Courier"} BLOCKQUOTE.CITE {padding-left:0.5em; margin-left:0; margin-right:0; margin-top:0; margin-bottom:0; border-left:"solid 2";} So the following is what I did this past week:

On Monday morning, I caught an 8:30 AM flite to Los Angeles (a four hour trip). After a two hour stopover, I boarded a 777 to Tokyo. That took 11 hours. After a two hour stopover, which I spent in the wonderful Japan Air Lines First Class Lounge (fantastic massage chairs and the most wonderful service), I got on a 747 bound for SIngapore. Seven hours later, we landed (just after midnight). At 1 Am, I was in my Carlton hotel room on the 7th floor, skyped over to Veva to say hello, yes I made it alive and sane, and then it was time for bed. Woke up at 7 AM (before the darned alarm went off).

By 8:30 I had walked the few blocks in humid, warm (but not yet hot) weather over to the Suntec convention center and up to room 208. There I spent the next 8.5 hours presenting on PL/'SQL technology to 102 Oracle technologists, some 15 of which had even flown in from Thailand. That all went very well, even though Clara of Quest (that rare very competent person who also maintains a vigorous sense of humor) introduced me using a photo of yours truly from the waist up, at age 18 months or so, completely naked. Very cute, eh? Very unusual. I can't really imagine what all the attendees thought of that!

Back to the hotel for a quick workout at the fitness center, then off to dinner with a handful of other Quest Software fellows at Jumbo Seafood in the East Coast Park, where we feasted on the signature Singapore dish: chili crab (and lots of other delights). Then Jason drove me to the Changi airport for a 10:40 PM flight back to Tokyo. Oh, joy.

I really wanted to sleep overnight, but I couldn't drift off. I couldn't get an upgrade for the Singapore-Tokyo flights on Japan Air Lines, and my legs don't like hanging down and stretching out from a regular coach seat. That was too bad. So seven hours later, we disembarked at 6:15 Am at the Narita airport in Tokyo, and I was very tired, in a daze. Back to the First Class Lounge, where I spent the next nine hours waiting for the 4 PM flight to Los Angelos. I spent over an hour total in the massage chair, and had a real (but short - 10 minute) massage by a masseuse, that was very nice.

Then I flew 11 hours back to LA, picked up a rental car, drove to Aliso Viejo where my employer is headquartered, and am now sitting through 1.5 days of meetings, then I fly back home to Chicago on Friday evening.

So let's total this up....between Monday and Friday I will have spent spent:

* Less than 23 hours in Singapore.
* Just two nights in a bed.
* About 45 hours up in the air.
* At least 60 hours in transit (in the plane, waiting for flights, going to and from airports).

That's just crazy. And that's my life, though usually it isn't quite this ridiculous. And I really am going to stop doing all this traveling pretty darn soon. Just not in 2008.

I hope you are all enjoying sleeping in your own beds!
August 09, 2008
8:05
Butterfly and Leaf - a poem
» ‎ FeuerThoughts

(what? You thought I only wrote software? Enjoy....)

Butterfly and Leaf

Moca Finca, August 9, 2008
Copyright 2008, Steven Feuerstein

Flickering shadow
of a butterfly
catching the sun
seeking out color:
Alive, for a time.

Fluttering shadow
of a leaf
losing the sun
losing the branch:
Dying, in separation.

My eyes
can't tell the difference.
Living or dying,
the shadow is the same.
It communicates,
it hides.

My eyes
move on,
seeking a shadow
I can trust.
August 03, 2008
8:00
Getting weary, so weary: Big Oil's biggest quarter ever: $51.5B in all
» ‎ FeuerThoughts

Big Oil sucks us dry once again.

By JOHN PORRETTO, AP Business Writer Fri Aug 1, 5:11 PM ET
HOUSTON - Oil giants Chevron Corp. and Total SA wrapped up a string of gargantuan, record-breaking earnings reports Friday, a stretch in which six of the major international oil companies topped $50 billion in combined profit for the first time.
While the profits of unparalleled size have brought withering criticism from Washington and disgust from consumers across the country, very few were surprised. Crude prices during the second quarter were nearly double what they were a year ago.
====================================================
It is getting so so hard to even pretend that we live in a democracy. In what sort of democracy do its citizens allow corporations to suck us dry as they repeat the mantra of "supply and demand"? In what sort of democracy does its citizens allow a runaway, renegade, ultra-right government (which admits to hating government and wanting to privatize all) take all our tax dollars and hand them over to banks, financial "services" companies, military-industrial wacko companies like Blackwater - either when they actually blow it so badly they fail or pay off our legislators to hand them big fat no-bid contracts?
The US is not a democracy. It is a plutocracy, with manic edges of theocracy. And it is wearing me down.
I say: start doing some worse case planning.
What are you going to do with they start charging for air?
What are you going to do when the infrastructure of the country collapses (bridges, roads, etc.) and while they continue to pour our tax dollars down black holes of the War Against Extremism, they raise our taxes to theoretically repair that infrastructure (but then another suicide bombing requires another war - sorry, no infrastructure)?
What are you going to do when gas hits $10 a gallon?
If you don't plan for it now, you will be screwed, totally screwed, very soon.
And that is my mid-summer rant. See ya....
July 21, 2008
17:42
My Oracle Open World Schedule
» ‎ FeuerThoughts

Hey folks,

For anyone attending Oracle Open World this year, here is my schedule of presentations:
Session ID: S300184 Session Title: Weird PL/SQL Track: Oracle Develop: Database Room: Golden Gate C3 Date: 2008-09-21 Start Time: 15:45
Session ID: S300183 Session Title: Break Your Addiction to SQL! Track: Oracle Develop: Database Room: Salon 02 Date: 2008-09-22 Start Time: 13:00
Session ID: S300185 Session Title: Why You Should Care About Oracle 11g PL/SQL Now Track: Oracle Develop: Database Room: Salon 02 Date: 2008-09-23 Start Time: 11:30
Hope to see you there!
July 09, 2008
8:23
How we fill the shopping malls
» ‎ FeuerThoughts

I don't go shopping very often. I like to buy things, don't get me wrong. But I don't like to shop, don't like the marketing, the noise, the lines.

But when I do go shopping, I am startled by how full the parking lots are and how busy the shops are.

After all, consumer confidence is almost as low as the approval ratings for President Bush. Everyone but the rich are suffering badly and broadly, losing their homes, declaring bankruptcy because a child or parent falls ill, and so on. So how can we Americans keep on buying stuff?

An article in the Tribune confirmed my suspicion (full text at end of this entry): many Americans have simply given up on the idea of ever digging themselves out of their debt hole. So what the hell? Use the credit card! Load it up! Get another one and load up that one! Buy a big LCD TV. Buy a new cell phone. Hey, buy an iPhone! Pay my bill? Maybe. I sure hope so. But since my outstanding balance is already $12000, what difference does it make if I bump it up to $14000 or $20000? Oh and when I have to take my child to the emergency room because I couldn't afford preventive doctor visits, I will put that on my credit card, too. If they will let me.

What a doomed nation....
Borrowing with credit cards surges
Consumers boosted their borrowing in May, mostly reflecting heavy credit card use to finance their purchases.

The Federal Reserve reported Tuesday that consumer credit increased at an annual rate of 3.6 percent in May, roughly the same pace as logged in the prior month.

The pickup pushed total consumer debt up by $7.78 billion, to $2.57 trillion.

The increase was led by much stronger demand for revolving credit, which is primarily credit cards. Use of revolving credit rose at a 7.1 percent pace in May, a month in which a flow of tax rebates helped to energize consumer spending. In April consumers cut back on such credit at a 0.5 percent pace.

Still, the longer-term trend shows that consumers have been charging more of their purchases on credit cards as banks have tightened lending standards on other types of loans.

"Consumer spending was so large in May that consumers used their income tax rebate checks and brought out the plastic as well," said Chris Rupkey, chief financial economist at Bank of Tokyo-Mitsubishi in New York. "This double-dipping approach cannot keep the consumer afloat forever."

Demand for non-revolving credit used to finance cars, education and other things, meanwhile, slowed to a 1.6 percent increase in May. That was down from a growth rate of 6.1 percent in April and was the slowest since December.

Overall, revolving debt jumped $5.69 billion during May and non-revolving debt increased $2.09 billion.

The Fed's measure of consumer borrowing does not include any debt secured by real estate, such as mortgage or home equity loans.

Consumer spending accounts for more than two-thirds of economic activity.

← Eddie Awad's Blog Mark Rittman's Oracle Weblog →

Database » FeuerThoughts

Feeds

FeuerThoughts (10 unread)