Devising a 10G Testing Strategy and the Anatomy of a 10G Data Warehouse.

I have attached a few files to this blog.   When I was consulting, one of my primary responsibilities was to perform database assessments for our customers.  The assessments were performed on every database that we intended to support remotely. They helped us to determine what we were in for when we began to administer the new environments.   After we assumed support for the database, we immediately initiated a project to correct all of the issues that were identified in the assessment.    This process resulted in a higher quality database environment for our customers and less administrative problems for us.  I guess you could describe it as a "win-win" situation.

We also performed database assessments to generate additional revenue, charging a couple of thousand dollars per assessment.   I would run a dozen or so scripts, spend some "quiet time" reviewing the output and create several recommendation documents that would be printed, bound and delivered to the customer.   I continue to perform database assessments here at Giant Eagle.   I can safely say that I have performed six or seven dozen of these database assessments during my career.    

Analysis Scripts
The analysis scripts were condensed from my rather large SQL library into six sets of scripts that focused on general database info, database structures, statistics, performance, recovery and security.  I collect scripts like some folks collect baseball cards.   I also write scripts as a hobby and for articles such as this one. The two files I'm giving you contain SQL statements that provide information about the database environment (dbinfo.txt) and database structures (structure.txtl).  Rest assured, I'm not giving away any of my old company's trade secrets.  The SQL statements are generally available on the Internet and are very basic by their nature.   Their output allowed me to have an understanding of the database size and configuration before I began reviewing the statistics and performance reports.

Analysis Output
The output of these two files (dbinfo_output.txt and structure_output.txt) will provide you with the general layout of our first data warehouse we intend to convert to 10G.  I removed some of the output generated because I felt it was irrelevant to this discussion.   If you want to see all of the output, just download the scripts and run them.  Don't worry about the "9I Only" displays in the headings.  They were only reminders to let me know that there was some Oracle9I specific information coming.

"Weighing in" at less than 500 GIGs in size, this environment is not large by data warehouse standards.  The database is actually the newest data warehouse we have here at Giant Eagle.  You could describe it as our "infant" data warehouse that will eventually grow to three or four terabytes in size as it matures.  In addition, it is currently not as mission critical to us as some of its big brothers, so this makes it the obvious choice for our first warehouse to convert.  

The developers responsible for testing this application are absolutely top-notch.   They'll be able to tell us what works and what doesn't.  I think I can safely classify the entire warehouse developement team as "access path experts."   As a result, I know that they will be able to identify any access path changes that occur as a result of the database upgrade.  I haven't seen a release yet that didn't change access paths, so I expect that 10G won't be any different.  Let's just hope that any access path changes result in faster (and not slower) performance.  

Application Testing Strategies
We wanted to create an "apples to apples" comparison environment here at Giant Eagle.  Take it from your friendly ex-Oracle instructor, it is difficult to compare access paths and query performance on databases that have different amounts of data and different parameter configurations.  We all know that the optimizer often takes a different access path based on the number of rows in the tables being accessed.   

But what if you don't have the disk available to clone your production environment?  Oracle provides a PL/SQL utility (DBMS_STATS) that allows administrators to transfer data dictionary information used by the optimizer from one database to another.   You won't be able to compare execution times but you will be able to compare access paths.

We have a quality control environment called DWQUAL that is currently an exact duplicate of production.  Ace DBA Jim Dojonovic created a duplicate of DWQUAL on the same test server called DWQUAL2 and upgraded it to Oracle10G.  These two environments are almost exact duplicates of each other.  We have reviewed the database parameters, database statistics, I/O load on EMC drives - absolutely everything that we could think of that would possibly taint the results was checked and double-checked.   If something out of the ordinary happens, we intend to blame the software FIRST and not our environment.  I say that tongue-in-cheek because if we have problems, we will also investigate environmental differences as a possible cause.  Oh, one last thing, we now secure DWQUAL2 like our production environments.  It contains production data and it is treated accordingly. 

Let's finish this blog with a quick review of our Oracle10G testing strategy.

Base Functionality Testing
Our base functionality test plan includes: startup/shutdown, remote access, utility execution (SQLLOADER, Export/Import), interoperability with the UNIX and LINUX operating systems, querying the data dictionary, backup/recovery, basic DML and DDL statement execution.  Please note that since we have been testing Oracle10G in our labs for some time now, most of this will be just a quick review to ensure that everything works like it did during our initial functionality tests.   

10G OEM Testing
The database isn't any use to use if we can't administer it effectively.  Of course, 10G OEM will be our 10G database administrative tool of choice.  I haven't found another Sissy GUI Tool as effective as OEM.   But 10G OEM is radically different than its 9I counterpart.  The difference between the tools is so dramatic that it requires a separate testing plan.  I'll provide the details on Giant Eagle's 10G OEM testing plan in my next blog.

Application Functionality Testing
Our application team counterparts have created their own formal10G test plans.  Some of the components that they will be testing are:

• Business rules enforced by constraints, triggers and procedures
• Data feeds from other architectures
• Load utility processing
• Nightly batch processing
• Online transaction processing
• Development tools used to build front-end application screens
• Third-party tools used to provide Ad-Hoc query access
• Procedural language used to build business logic (Java, PL/SQL)
• Advanced features required (replication, advanced queuing, partitioning, parallelism)

Although application functionality testing isn't intended to identify performance problems, general run times will be evaluated.  If something runs twice as fast or twice as slow (ouch!) as we expected, we'll be digging into it.

Performance Testing
Performance testing will allow us to compare the performance of various SQL statements in the new Oracle10G database with the statements' performance in the old release (in this case Oracle Version 9.2).  We intend to run selected online transactions and batch jobs in DWQUAL and DWQUAL2 and compare their execution times and performance statistics. 

We won't have to run traditional stress tests.  Like many data warehouses,  a high number of concurrent users is not the pressing issue for this database.    We are concerned about loading lots of data and SQL statements that scan millions and millions of rows.  If the database can survive our nightly batch processing job stream,  I'll know that 10G can handle virtually everything we throw at it.

We have numerous Oracle supplied and third-party toolsets available.   Since 10G and 9i have different versions of Oracle Enterprise Manager, we won't use them during our performance evaluation.  As a result, we intend to use the basic toolsets at our disposal. 

We'll begin by comparing the elapsed execution times of both workloads.  We'll use explain plans to compare access paths and STATSPACK to generate statistical performance reports.  Yes, even though 10G has all kinds of high-tech tuning gadgets, we will be using the old tried-and-true STATSPACK tool to generate our performance statistics.   We also intend to use SQL Trace and TKPROF when more in-depth analysis is warranted.  The same tools I have been using since Scott's tiger was a cub.

In addition, our UNIX team will also be involved.  They will be using NMON, VMSTAT, IOSTAT and various third-party toolsets to measure the overall load on the system during each of the test runs.   We'll keep the admins happy by feeding them a couple of raw slabs of beef (or a hapless developer) from time to time.

10G New Feature Testing
Once again, most of this testing has been performed in our labs.  Rather than provide you with a listing of all of the 10G features we intend to test, I'll just cover each test in its own quickie blog!  That's the great thing about writing blogs - there is always more to come!

Next up
In the next blog, we'll review Giant Eagle's OEM 10G test plan.