Saturday, May 25, 2013

Of Sequences, Insert All, and Prepared Statements

This post was almost titled "When is nextval Not the Next Value"

I was recently asked to help troubleshoot a constraint violation.  The developer was getting a unique constraint violation on a constraint they did not recognize (nor could I... thank you Hibernate, and your gosh-awful constraint naming).

Finding the constraint was simple enough. 

select * from dba_cons_columns where constraint_name='TC234467890'; 

I quickly saw this was a constraint on the ID column of the history table, which I knew was set from a sequence.  I asked to see the SQL that was failing.  They sent me part of their java code.


sql="insert all "
     + "into history (id, col1, col2,col3) "
     + " values (hibernate_seq.nextval,1,0, " + floatVar[i] + ") "
     + "into history (id, col1, col2, col3) "
     + " values (hibernate_seq.nextval, 1,0, " + floatVar[i+1] +") "
     + "into history (id, col1, col2, col3) "
     + " values (hibernate_seq.nextval, 2,0, " + floatVar[i+2] +") "
     + " select 1 from dual;";
executeUpdate(SQL);

After cringing at the lack of bind variables I saw that the Insert All was the culprit.  A quick test of the query in sqlplus confirmed it.  The code was attempting to increase performance by executing multiple inserts in a single call to the database.  This is admirable, but misguided.  Because all 3 inserts are part of the same SQL statement, the nextval of hibernate_seq is fetched at the first occurrence and re-used for each additional occurrence  in the same statement.  This caused the same ID value to be used for each of the 3 rows, which of course violates the primary key's unique constraint.

I advised them to break up the insert all into multiple calls to the database.  They did and that fixed the immediate issue. I then introduced them to prepared statements.  This developer was new to java, so there was no expectation that they were aware of it.


sql="insert into history(id, col1, col2, col3) values (hibernate_seq.nextval, ?,?,?)"
stmt=prepareStatement(SQL);
for (I=1;I<=4;I++)
{
  setFloat(1,floatVar[i]);
  stmt.execute();
};

The prepared statement approach to this has several advantages.
First, it fixes the sequence nextval issue.
Second, it allows more readable code by separating the structure of the query from the variables passed into it.
Third, it allows clearer type casting of data types.  This fixed the next issue the developer ran into due to implicit type conversions when the SQL was sent as a single string.
Fourth, and most important for performance, it allows the database to work more efficiently.  In the executeQuery approach, each SQL call is a unique string sent to the database.  Each is hard parsed and never re-used.  With prepared statements, the SQL is generic and common across all calls.  Hard parsing is not needed.  The value of variables are passed and bound to their location in the query at runtime.
Fifth, using bind variables prevents SQL injection... a topic for another day.




Monday, May 13, 2013

Why Does the Broadcast Van in Iron Man 3 Need Exadata?


One of the things I enjoy when watching Disney/Marvel's Iron Man and Avengers franchises is looking for the Oracle product placement.  I need help, I know.

Some of the stand-outs are Larry Ellison's cameo in Iron Man 2, as well as Oracle all over the advertising at the Stark Expo. In the Avengers, several racks of Oracle appliances were in the Shield lab where the tesseract was being studied. In Iron Man 3 Jarvis used the Oracle Cloud to reconstruct a crime scene, but the one that had me scratching my head was "Why does a broadcast van need an Exadata machine?"

My first thought was "that was ridiculous", but after I saw someone besides me also post this to Twitter, I started to ponder why you might do that.

So why would you consider Exadata anywhere in the first place?  Speed. Processing power. A turn-key Oracle RAC installation.  Tom Kyte has referred to Exadata as the closest thing he has seen to FAST=TRUE.

The other compelling reasons... Storage nodes and Hybrid Columnar Compression. The first application for this is data warehousing, but I can think of other massively parallel applications as well, some very relevant in a news van.

First, consider Gary the cameraman. He modeled his life after Tony Stark. He probably read the Oracle magazine articles about everything Stark Industries was doing with Oracle products, and talked his station into outfitting his van with some of that tech so he could work like Tony.

Video is a data intensive media.  Modern video, HD recording, and high frame rates can benefit from a powerful processing and storage engine.  A database solution with the Oracle Multimedia package could allow Gary to capture footage, and instantly categorize and organize it.  The parallel processing of Exadata storage nodes would allow tagging, processing, and searching large amounts of video in ways not possible with traditional tape or digital video equipment.

Aliens came through a wormhole and devastated New York. The news network may have revised their disaster recovery plans to make mobile copies of much of their infrastructure. Exadata in the field could be part of that strategy. If the central studio went down, advertising, billing, and other business functions could be brought up in the remote vans if needed. Feasible in our world? Maybe not, but we're in the Marvel universe for this discussion.  You never know when a super-villain is going to wreck a data center.

Is it feasible?  A Mercedes-Benz Sprinter with the extended roof would need to be modified slightly.  The stock high-roof model is half an inch short for a full rack installation.  At over 14 kW peak, the power requirements for Exadata are high for a mobile unit, but broadcast vans often are power hungry, so heavy power generation is often part of the package.  We'll assume the news channel bought a mobile-class Arc Generator from Stark Industries.  At 42 inches deep, there would not be a lot of room in the van for work space. Again, not unusual in a broadcast installation.  Cooling may be the biggest challenge, but if we are modifying the body to make things fit anyway, and powering the van with an Arc Reactor, this too is easily solved.

One of the things I love about science fiction is its ability to make us think about where we can push technology or where it might be applied that we don't normally think about.  Sure, this Iron Man 3 scene was rather contrived product placement, but it wasn't that long go that my phone was dumb and my 486dx was powerful.  If those devices have switched places, how long before we really are installing Exa-class machines in broadcast vans?