Systems Engineering and RDBMS

SQL Test Case Builder in Oracle 11g and

Posted by decipherinfosys on April 10, 2008

Oracle 11g (this feature was also back ported to has a new diagnostic tool called the “SQL Test Case Builder”. One of the most difficult things during the load testing QA cycle or in a production environment is to be able to put together a reproducible test case. This is easier said than done. That is where this new tool comes into play. The SQL Test case builder allows for the automatic gathering of as much information as possible related to a SQL issue and then packages it so that it becomes easy for a developer or a support engineer to reproduce the problem on another machine.

A test case in TCB (Test Case Builder) captures two types of information:

1) Information that is always captured:

This includes the SQL Text, the PL/SQL procedures/packages/functions, optimizer stats, execution plan information, data for the table (this could be either a sample or full data), meta-data for all the objects that were involved, bind variables and user information (access rights etc.).

2) Some transient information – this includes dynamic sampling results, run time information, cached information etc. i.e. the information that is needed to shed more light on the context in which the SQL was compiled.

The test case script contains all the commands that are needed to be able to re-create the objects, the user, the stats and the environment. You can launch the task for the creation of a test case either from the Enterprise Manager – you can invoke TCB via IPS (Incident Packaging Service) after a SQL incident has occured. Alternately, you can also do this for a SQL Query that is giving you a problem. Another way to create the test case is via our good old SQL *PLUS. You can directly invoke one of the PL/SQL functions in the SQL Diagnostic package. You will need to use the PL/SQL package called dbms_sqldiag for that. The two main procedures are:

1) dbms_sqldiag.export_sql_testcase: For exporting a SQL test case for a given SQL statement. This dumps it to a specified directory.
2) dbms_sqldiag.import_sql_testcase: For importing the SQL testcase from a directory.


directory => ‘Prod_Issue’,
sql_text => ‘select….’,
testcase => mytest,
exportData => True)

directory is the place where the testcase will be stored, the sql_text is the problem sql query for which the testcase is being built, the testcase is test case metadata as output and exportData states that we also want to include the data.

Once the testcase has been built up, we can then copy all the data in the directory over to another environment where we need to do the analysis. And then importing it is equally straight forward:

directory => ‘Prod_Issue_DIR’,
filename => ‘xxxxxxxxxxxxxxmain.xml’)

Please refer to the dbmsdiag.sql file to look up the different options that are available for the 2 procedures that are mentioned above. There are other procedures as well in that package like: EXPORT_SQL_TESTCASE_DIR_BY_INC, EXPORT_SQL_TESTCASE_DIR_BY_TXT etc.. You can read up more on TCB using these links:

Sorry, the comment form is closed at this time.

%d bloggers like this: