- About Us
- Events and Webinars
- Contact Us
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:
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
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.
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.