Tagged: Oracle Exadata

The Core Performance Fundamentals Of Oracle Data Warehousing – Set Processing vs Row Processing

[back to Introduction]

In over six years of doing data warehouse POCs and benchmarks for clients there is one area that I frequently see as problematic: “batch jobs”.  Most of the time these “batch jobs” take the form of some PL/SQL procedures and packages that generally perform some data load, transformation, processing or something similar.  The reason these are so problematic is that developers have hard-coded “slow” into them.  I’m generally certain these developers didn’t know they had done this when they coded their PL/SQL, but none the less it happened.

So How Did “Slow” Get Hard-Coded Into My PL/SQL?

Generally “slow” gets hard-coded into PL/SQL because the PL/SQL developer(s) took the business requirements and did a “literal translation” of each rule/requirement one at a time instead of looking at the “before picture” and the “after picture” and determining the most efficient way to make those data changes.  Many times this can surface as cursor based row-by-row processing, but it also can appear as PL/SQL just running a series of often poorly thought out SQL commands.

Hard-Coded Slow Case Study

The following is based on a true story. Only the facts names have been changed to protect the innocent.

Here is a pseudo code snippet based on a portion of some data processing I saw in a POC:

{truncate all intermediate tables}
insert into temp1 select * from t1 where create_date = yesterday;
insert into temp1 select * from t2 where create_date = yesterday;
insert into temp1 select * from t3 where create_date = yesterday;
insert into temp2 select * from temp1 where {some conditions};
insert into target_table select * from temp2;
for each of 20 columns
  update target_table t
    set t.column_name =
      (select column_name
       from t4
       where t.id=t4.id )
    where i.column_name is null;
end loop
update target_table t set {list of 50 columns} = select {50 columns} from t5 where t.id=t5.id;

I’m going to stop there as any more of this will likely make you cry more than you already should be.

I almost hesitate to ask the question, but isn’t it quite obvious what is broken about this processing?  Here’s the major inefficiencies as I see them:

  • What is the point of inserting all the data into temp1, only then to filter some of it out when temp2 is populated.  If you haven’t heard the phrase “filter early” you have some homework to do.
  • Why publish into the target_table and then perform 20 single column updates, followed by a single 50 column update?  Better question yet: Why perform any bulk updates at all?  Bulk updates (and deletes) are simply evil – avoid them at all costs.

So, as with many clients that come in and do an Exadata Database Machine POC, they really weren’t motivated to make any changes to their existing code, they just wanted to see how much performance the Exadata platform would give them.  Much to their happiness, this reduced their processing time from over 2.5 days (weekend job that started Friday PM but didn’t finish by Monday AM) down to 10 hours, a savings of over 2 days (24 hours).  Now, it could fail and they would have time to re-run it before the business opened on Monday morning.  Heck, I guess if I got back 24 hours out of 38 I’d be excited too, if I were not a database performance engineer who knew there was even more performance left on the table, waiting to be exploited.

Feeling unsatisfied, I took it upon myself to demonstrate the significant payback that re-engineering can yield on the Exadata platform and I coded up an entirely new set-based data flow in just a handful of SQL statements (no PL/SQL).  The result: processing an entire week’s worth of data (several 100s of millions of rows) now took just 12 minutes.  That’s right — 7 days worth of events scrubbed, transformed, enriched and published in just 12 minutes.

When I gently broke the news to this client that it was possible to load the week’s events in just 12 minutes they were quite excited (to say the least).  In fact, one person even said (a bit out of turn), “well, that would mean that a single day’s events could be loaded in just a couple minutes and that would give a new level of freshness to the data which would allow the business to make faster, better decisions due to the timeliness of the data.”  My response: “BINGO!”  This client now had the epiphany of what is now possible with Exadata where previously it was impossible.

It’s Not a Need, It’s a Want

I’m not going to give away my database engineer hat for a product marketing hat just yet (or probably ever), but this is the reality that exists.  IT shops started with small data sets and use small data set programming logic on their data, and that worked for some time.  The reason: because inefficient processing on a small data set is only a little inefficient, but the same processing logic on a big data set is very inefficient.  This is why I have said before: In oder to fully exploit the Oracle Exadata platform (or any current day platform) some re-engineering may be required. Do not be mistaken — I am not saying you need to re-engineer your applications for Exadata.  I am saying you will want to re-engineer your applications for Exadata as those applications simply were not designed to leverage the massively parallel processing that Exadata allows one to do.  It’s time to base design decisions based on today’s technology, not what was available when your application was designed.  Fast forward to now.

Oracle OpenWorld 2010: The Oracle Real-World Performance Group

Now that Oracle OpenWorld 2010 is just under 70 days away I thought I would take a moment to mention that the Oracle Real-World Performance Group will again be hosting three sessions.   This year I think we have a very exciting and informative lineup of sessions that are a must-attend for those wanting to see and hear Oracle Database performance insight right from Oracle’s own performance engineers.  Hope to see you there!

And for those who are interested, there will likely be many discussions about the Oracle Database Machine and Oracle Exadata.  Very hot stuff!

Session ID: S317164 (Monday 2:00PM)
Session Title: The Latest Real World Performance Challenges
Session Abstract: Oracle’s Real-World Performance Group — the group that first presented at Oracle OpenWorld parallel query techniques with partitions, the index-less database, cardinality challenges with the optimizer, over-processed databases and connection storms — this year presents the performance issues before you experience them and how to plan for future projects with success. All topics discussed in this session come from the Real-World Performance Group’s observations and problem solving.
Session ID: S317166 (Monday 5:00PM)
Session Title: Real-World Performance Panel Session
Session Abstract: This session is your chance, via written questions, to ask a panel stacked full of real-world performance talent all those questions to which you’ve just wanted to get a simple answer. You can write your questions on postcards available in the meeting room. Please focus on performance topics and not system debugging! 
Session ID: S317165 (Tuesday 2:00PM)
Session Title: Oracle Database Performance Secrets Finally Revealed
Session Abstract: Have you ever seen a real-world database performance engineer solve Oracle Database performance problems? Wouldn’t you like to know all the performance secrets they know? In this session, real-world database performance engineers will go over many of the performance secrets they use to get extreme performance out of Oracle Database. Not only will they tell you about these secrets but they will demo them for you as well. This session is specifically for those wanting to advance their database performance knowledge and experience.


Oracle Exadata Storage Server and the HP Oracle Database Machine

If you haven’t been under a rock you know that Larry Ellison announced the Oracle Exadata Storage Server and the HP Oracle Database Machine at Oracle OpenWorld 2008. There seems to be quite a bit of interest and excitement about the product and I for one will say that I am extremely excited about it especially after having used it. If you were an OOW attendee, hopefully you were able to see the HP Oracle Database Machine live demo that was in the Moscone North lobby. Kevin Closson and I were both working the live demo Thursday morning and Doug Burns snapped a few photos of Kevin and I doing the demo.

HP Oracle Database Machine Demos

In order to demonstrate Oracle Exadata, we had an HP Oracle Database Machine set up with some live demos. This Database Machine was split into two parts, the first had two Oracle database servers and two Oracle Exadata servers, the second had six Oracle database servers and 12 Oracle Exadata servers. A table scan query was started on the two Oracle Exadata servers config. The same query was then started on the 12 Oracle Exadata servers config. The scan rates were displayed on the screen and one could see that each Exadata cell was scanning at a rate around 1GB/s for a total aggregate of around 14GB/s. Not too bad for a single 42U rack of gear. This demo also showed that the table scan time was linear with the number of Exadata cells: 10 seconds vs. 60 seconds. With six times the number of Exadata cells, the table scan time was cut by 6.

The second live demo we did was to execute query consisting of a four table join (PRODUCTS, STORES, ORDERS, ORDER_ITEMS) with some data that was based off one of the trial customers. The query was to find how many items were sold yesterday in four southwestern states of which the item name contained the string “chili sauce”. The ORDER_ITEMS table contained just under 2 billion rows for that day and the ORDERS table contained 130 million rows for the day. This query’s execution time was less than 20 seconds. The execution plan for this query was all table scans – no indexes, etc were used.

When One HP Oracle Database Machine Is Not Enough

As a demonstration of the linear scalability of Oracle Exadata, a configuration of six (6) HP Oracle Database Machines for a total of 84 Exadata cells was assembled. 14 days worth of POS (point of sale) data onto one Database Machine and executed a query to full table scan the entire 14 days. Another 14 days of data were loaded and a second Database Machine was added to the configuration. The query was run again, now against 28 days across two Database Machines. This process was repeated, loading 14 more days of data and adding another Database Machine until 84 days were loaded across six Database Machines. As expected, all six executions of the query were nearly identical in execution time demonstrating the scalability of the product. The amazing bit about this all was with six Database Machines and 84 days of data (around 163 billion rows), the physical I/O scan rate was over 74 GB/s (266.4 TB/hour) sustained. To put that in perspective, it equates to scanning 1 TB of uncompressed data in just 13.5 seconds. In this case, Oracle’s compression was used so the time to scan 1 TB of user data was just over 3 seconds. Now that is extreme performance!!!

As I’m getting ready to post this, I see Kevin has beat me to it. Man, that guy is an extreme blogging machine.

Initial Customer Experiences

Several Oracle customers had a 1/2 HP Oracle Database Machine* (see Kevin’s comments below) to do testing with their data and their workloads. These are the ones that were highlighted in Larry’s keynote.


  • Currently runs on two IBM P570s with EMC CX-30 storage
  • 4.5TB of Call Data Records
  • Exadata speedup: 10x to 72x (average 28x)
  • “Every query was faster on Exadata compared to our current systems. The smallest performance improvement was 10x and the biggest one was 72x.”

LGR Telecommunications

  • Currently runs on HP Superdome and XP24000 storage
  • 220TB of Call Data Records
  • “Call Data Records queries that used to run over 30 minutes now complete in under 1 minute. That’s extreme performance.”

CME Group

  • “Oracle Exadata outperforms anything we’ve tested to date by 10 to 15 times. This product flat out screams.”

Giant Eagle

  • Currently runs on IBM P570 (13 CPUs) and EMC CLARiiON and DMX storage
  • 5TB of retail data
  • Exadata speedup: 3x to 50x (average 16x)