SQL Test Case Builder in Oracle 11g

What is SQL Test Case Builder?

SQL Test Case Builder is a diagnostic tool in 11g, back-ported to 10g, that is designed to be able to obtain a reproducible test case for developers, DBAs, or Oracle Support. 

Oracle built the SQL Test Case Builder diagnostics utility primarily to assist Oracle Support and customers for performance-related Service Requests.  Before SQL Test Case Builder, when a performance-related SR was logged, the resolution path would typically look like this:

1. Support would ask whether the problem is isolated to a SQL statement, program, or something widespread.   Customers would sometimes be able to quantify this, sometimes not.
2. Support would ask for tkprof’d trace files.
3. Support would ask whether segment statistics were up-to-date and inquire about the statistics collection approach used.
4. Support sometimes would ask for RDA output to be uploaded to the SR, sometimes not.
5. Typically, the resolution process would take a long time either because the customer wouldn't have the proper experience to provide Support with required information or the customer wouldn’t be able to provide Support with enough information for Oracle to be helpful in determining the resolution.  In many situations, SRs would be resolved by the customer via SQL tuning, or worse, the SR would be closed without resolution and/or abandoned.
    

The steps above led to SRs being opened for long periods of time, high levels of escalation and customer dissatisfaction, and so forth.

SQL Test Case Builder provides a means to supply support with comprehensive test case data for a problematic SQL statement.  It uses DBMS_SQLDIAG procedures to export key information about the SQL test case, which Support is then able to upload to a test environment and perform analysis.

SQL Test Case Builder Process

1. Build and/or identify a data pump directory on your database tier host to export test case data to.
2. Identify the SQL_ID for the problematic SQL statement.
3. Run DBMS_SQLDIAG.EXPORT_SQL_TESTCASE to export a test case to your dump directory.  The test case export contains DDL definitions of all the tables, indexes, and other objects in your environment, your environments optimization environment (initialization parameter settings that impact the CBO), object and environment metadata, and optionally, exported data from the tables that the SQL statement accesses.
4. Zip the test case dump data and upload to Oracle Support, or transfer to another system in your environment.
5. Either the customer or Oracle Support uses DBMS_SQLDIAG.IMPORT_SQL_TESTCASE to import the test case into their environment.

Example: Exporting a SQL Test Case

First, let’s create our directory that we’ll use for exporting the test case:

Next, we’ll run a SQL statement, note the SQL_ID used, and then run DBMS_SQLDIAG.EXPORT_SQL_TESTCASE to export the SQL Test Case.

Now we can export out test case:

When finished, let’s look at the contents of the directory:

Example: Importing a SQL Test Case

Using the test case exported from the previous section, let’s import this into another database, XBM.  First, let’s make sure that no “SOE” schema exists, as this would be a good test case in situations when attempting to import a SQL test case from a completely foreign system:

Now, in XBM, let’s create our directory:

Next, let’s import our test case. When doing this, all you need to do is specify the “*main.xml” file from the previous section:

When finished, let’s check our environment:

As we can see, the objects were imported into the SYSTEM schema.  Let’s dig a little deeper:

The tables don’t exist in any tablespace because we selected to NOT import data.   Let’s run an EXPLAIN PLAN against our SQL statement now, comparing DBM (the source) and XBM (the database we imported the test case into):

Now in XBM:

We see slight differences in the explain plan, which should not be the case, but you can see that even without the object’s data being imported, an explain plan still worked.

Word of Caution !!!

Be careful about importing a SQL Test case into an environment in which you may already have the same schema and data – doing so could cause you to lose your objects.

Example with Data

In this test case, we’ll do a SQL Test Case export with data from a database called DBM and import this into the XBM database.  Prior to doing this, we’ll create the destination tablespaces and schema in XBM:

I’ll also grant all on our XBM directory to SOE:

Now we’ll run the same SQL Test Case Builder export, and this time, we’ll export the data from our two SOE tables, CUSTOMERS and ORDERS.

If we check the directory, it looks like the below.  Now the large export dump fie, TEST1dpexp.dmp:

Now let’s source the XBM environment, login as SOE/SOE, and import the SQL Test Case contents:

As you can see, the dbms_sqldiag.import_sql_testcase call returned an error that generated an LPX-00209 error.  This is because I didn’t delete the previous SQL Test Case export files, and the existing files in the directory were appended to by the most recent run.  This caused multiple lines in TEST1main.xml that had the tag “<?xml version="1.0"?>”, which is the cause of the error.  To resolve this, I deleted the test case export files and re-ran the export.

The second import attempt into XBM is provided below:

However, we can see that the tables did get imported:

The execution plan for DBM, the source database, looks like this:

And the execution plan in XBM looks identical:

So why did we get the Data Pump import errors?  A look at TEST1dpimp.log shows this:

We’re having errors creating indexes because SOEINDEX tablespace does not exist.  This is because I created a tablespace called SOE_INDEX in XBM instead of SOEINDEX.  Let’s drop it and try again:

Now we’ll re-run our SQL Test case import:

We still see errors, but in examining the import log, they’re all related to “not cleaning up” the SOE schema:

Let’s try getting a clean run – see below:

Now for the next Import attempt:

As you can see, it worked.