An Interview with Expert Warehouse Developer Bob George on 10G Application Performance

I've had the good fortune of working with Bob George since I started at Giant Eagle.   Besides having a very strong technical skill set, Bob takes ownership of the applications he supports and is one stubborn individual.   I like that in a developer.   He's not afraid to stand up against all of the Type A personalities I have on my DBA team.   Why are all DBAs Type A's anyway? 

If Bob doesn't feel comfortable with us migrating his warehouse to 10G, he'll tell us.  I trust Bob's opinion.  If Bob says we don't (or can't) go to 10G, we will postpone the migration until he says we're ready.  Bob has been running our entire warehouse nightly batch job stream against the 10G test database for some time now and comparing the run times against our 9I  production warehouse.   I thought it might be beneficial to interview Bob on his findings and on 10G application performance in general.  

Question
You have been working with Giant Eagle's data warehouses for some time now. Why do you like working with such large data stores?

Answer
I've been working on large data warehouses for about 5 years now.   I like big data.  Working with large data stores is extremely challenging.  Data Warehouses, because of the amount of data they store, are very unforgiving.  As a result, they challenge your technical expertise on a daily basis.  If you make a mistake on an access path, not only can your job run long, it can lock up the entire warehouse platform.   My job is to be able to transform terabytes of data into meaningful information for my business users.  That's the name of the game. I like working with a system that can bite you if you aren't careful.

Question
What does the future hold for data warehouse data stores?

Answer
I think that we will see more real time data feeds into data warehouses.  The warehouse will no longer be viewed as a simple repository of historical data that is used to make strategic decisions.   The real time feeds will allow end users to use the data to also make tactical decisions.  As a result, the traditional role of the warehouse will change.  It will be a data store that is used to make both strategic and tactical decisions. 

The size of data warehouses will also continue to grow.  Businesses want to store more data not less.  More data to be analyzed means better business decisions.  Storage is becoming more cost effective.  In the past, databases that were hundreds of GIGs in size were considered to be large environments.  We now have single index structures that size. 

Question
You have been monitoring 10G application performance for some time now.   What is your general opinion?

Answer
The database is pretty rock solid.  You never know what to expect when you begin testing a new release.  You always expect at least a few problems to crop up. We have had no problems worth mentioning.   We did have a few problems with TOAD, which is one of the tools we use a lot here.  Users that have Versions 7.5 and 7.6 of TOAD may have problems accessing a 10G database.

I highly recommend that TOAD users upgrade to Version 8 and install the Oracle10G client software. Version8 also takes advantage of some of the 10G new features that really enhance the tool's functionality.  I'm really happy with Version 8's SQL performance reporting capabilities.

Question
Is there anything that took you by surprise?

Answer
The PL/SQL engine in 10G is much faster than previous versions. When you first talked to me about 10G, you stated that we should expect to see a 30% improvement in PL/SQL program execution. Not the SQL, but the actual procedural code itself.  I thought to myself "yeah right".  Well, we are actually experiencing performance improvements that are greater than 30%.  We have one set of resource intensive programs that are critical to our nightly batch streams.  They used to run in 5 to 6 hours in Oracle9i.  In 10G, they run in 1 to 2 hours.  I was shocked.  The more instructions the code has, the greater the reduction will be.   We are seeing improvements across the board. Very good news.

Question
When you compare run times between programs running in 9I against their 10G counterparts, have you identified any patterns on what runs faster?

Answer
As I stated previously, the more resource intensive the PL/SQL code is the greater the performance improvement will be.   10g just runs PL/SQL a LOT faster than previous versions.

Question
Slower?

Answer
Nothing worth mentioning.   We run dozens of jobs in our nightly batch stream that contain hundreds of SQL statements.  A few statements may have run a few seconds or a few minutes longer.  Remember, we are working with tables that have hundreds of millions of rows, so a few minutes is not a long time.  These systems are not kept in a vacuum.  We use them.  There may have been other jobs contending for resources.

Question
Now the big question.  How comfortable are you with 10G?  Should we begin migrating our warehouses to it?

Answer
I say we go.