Real Application Testing is a new feature of Oracle 11g. It allows users to capture the workload of a running database and then to playback that workload in a test environment. Great for testing the impact of changes to your database environment.

But Real Application Testing is an extra cost option which may be out of reach for many Oracle customers. I’ve been working on a very rudimentary script that facilitates executing many SELECT statements against a database and then generates the explain plans and captures duration for all of them. This is not nearly the functionality of Real Application Testing, but it’s at least a smattering of it.

It works like this. First you must do a significant amount of manual set up work by capturing SELECT statements used in your database. Make sure each SELECT gets into a single text file. I’ve been doing it this way: Use SQL Developer to capture top SQL in a database and save it to XLS format. Then use an Excel macro to write each SELECT statement to a separate file. I would like to automate this process but haven’t had the time.

Once each SELECT statement is in a separate file, my two scripts go to work. The scripts assume that all SELECT statements can be run by the same database user. Script number 1 will iterate over each SQL file and create a companion file that produces the explain plan for each SQL statement in a separate file. It also will create a master script that executes the original SQL file and also the newly created companion explain plan file.

At this point, one can run the master file against your target database. This will generate many log files.

The final step is to run script number 2, which parses all the log file output. it extracts the name of each query, the explain plan cost for the query, the duration of the query and the records returned by the query. It places that data into tabular CSV format.

With the above scripts, one can easily run a set of SELECT SQL statements against a database and quickly measure explain plan cost, query duration and records returned.

For example, I can compare the output files from the final step listed above to compare the duration, cost and rows returned from many queries executed on two different systems. This allows me to see if a change in configuration results in a different explain plan or duration. This helps when you are trying to answer questions like “Does the query that runs 500,000 times a day run in 1 second now run in 2 seconds?”

Please see my Download page for the two shell scripts and a sample Excel macro.

Advertisements