For most SQL problems, the
single most important factor for a speedy bug resolution is to obtain a
reproducible test case. However, this is normally the longest and most painful
step for customers. The goal of the SQL Test Case Builder (TCB) is to automatically gather as much
information as possible related to a SQL incident (problem) and package it in a
way that allows a developer or a support engineer to reproduce the problem on
his or her own machine quickly.
At a very high-level, SQL Test Case
Builder can be seen as a way to export a SQL. Currently, Oracle export (expdp) takes a schema or a set of tables and
exports all the dependents objects. SQL Test Case Builder provides the same
service but takes a SQL statement as input.
What's Inside Test
Case Builder?
The main input of SQL Test Case Builder is a SQL
object. A SQL object is defined as the SQL text plus all the information
required to compile it on a particular database instance (this contains the
parsing user name, for example).
Logically, a SQL test case appears as a
script containing all the necessary commands to recreate the objects, the user,
the statistics, and the environment.
Within the Oracle Diagnosability
infrastructure, TCB compiles the problem SQL in a special capture mode to obtain
the set of objects to export. A test case captures two types of
information:
Permanent information
SQL text
PL/SQL functions, procedures, packages
Statistics
Bind variables
Compilation environment
User information (like privileges)
SQL profiles, stored outlines, or other SQL Management Objects
Meta data on all the objects involved
Optimizer statistics
The execution plan information
The table content (sample or full). This is optional.
Transient information
For
most of the SQL test cases, the permanent information above is enough to
reproduce a problem. There are however cases where this is not enough and
additional information about the context in which this SQL was compiled is
required. Therefore, in addition to the permanent information, SQL Test Case
Builder captures transient information, e.g. information that is only available
as part of the compilation of the SQL statement. This includes dynamic sampling
results, cached information, some run time information, like the actual degree
of parallelism used, etc.
As part of creating a SQL test case, the SQL
object is reloaded and all the diagnostic information available generated and
gathered automatically. This information will be made available to Oracle
support and developers.
How do I use the SQL Test Case
Builder?
The task of creating a SQL test case can be performed
in two ways:
From EM (Enterprise Manager), where
TCB is invoked on user-demand via IPS (Incident Packaging Service) after a SQL
incident occurred. The user can also manually create an incident for a problem
query for building test case purpose.
From SQLPLUS, where you can directly
invoke one of the PL/SQL API functions in the SQL Diagnostic package. We will
give examples of using the APIs below.
All the new PL/SQL
procedures supporting SQL Test Case Builder are part of a new PL/SQL package
called dbms_sqldiag (see dbmsdiag.sql for details). The two main features
related to TCB in this package are export
and import test cases.
Procedure dbms_sqldiag.export_sql_testcase exports a SQL
test case for a given SQL statement to a given directory.
Procedure dbms_sqldiag.import_sql_testcase imports a test
case from a given directory.
To build (or export) a test case, the
simplest form would be something like: dbms_sqldiag.export_sql_testcase( directory => 'TCB_DIR_EXP', sql_text => 'select count(*) from sales', testcase => tco) Here
directory and sql_text are inputs which specify where the test
case will be stored, and the problem query statement, respectively.
Testcase specifies the test case metadata as output.
For
security reason, the user data are not exported by default. You have the option
to set exportData to TRUE to include the data. You can also set
samplingPercent if you are exporting with data. To protect users
proprietary codes, TCB will not export PL/SQL package body by
default.
Once the test case has been built, you can copy all the files
under the export directory to your test environment. Note there is a file called
xxxxxxxxmain.xml, for example, oratcb1_03C600800001main.xml, which
contains the metadata of the test case.
Now importing the test case can
be as simple as:
dbms_sqldiag.import_sql_testcase( directory => 'TEST_DIR', filename => 'oratcb1_03C600800001main.xml') To
verify that the test case is successfully rebuilt, you can just issue an explain command for the problem query. However,
if you want to actully run the query, then you need to have the data
available.
You can refer to dbmsdiag.sql for more information about other
options available for these procedures.
Example - We now
show the typical steps of using TCB by a sample query with materialized view. In
this exmaple, we set the exportData option to TRUE, so we can re-run
the same query after the TCB task is completed.
Setup
SQL> connect / as sysdba Connected. SQL> SQL> create or replace directory TCB_DIR_EXP as 2 '/net/tiger/apps/tcb_exp'; Directory created. SQL> SQL> grant dba to apps; Grant succeeded. SQL> SQL> connect apps/apps Connected. SQL> SQL> create materialized view scp_mvu 2 parallel 2 3 as 4 select p.prod_name, c.cust_gender, 5 max(s.amount_sold) max_amount_sold 6 from sales s, products p, customers c 7 where s.prod_id = p.prod_id 8 and s.cust_id = c.cust_id 9 group by p.prod_name, c.cust_gender;
Materialized view created.
SQL> SQL> desc scp_mvu; Name Null? Type ----------------------------------------- -------- ------------ PROD_NAME NOT NULL VARCHAR2(50) CUST_GENDER CHAR(1) MAX_AMOUNT_SOLD NUMBER
SQL> SQL> select * from scp_mvu where max_amount_sold > 7000 order by 3;
PROD_NAME C MAX_AMOUNT_SOLD -------------------------------------------------- - --------------- Joseph Sportcoat F 7400.8 Kenny Cool Leather Skirt M 7708 Leather Boot-Cut Trousers M 8184
3 rows selected.
Export as user APPS
SQL> connect apps/apps Connected.
SQL> SQL> Rem define the problem SQL statement SQL> create or replace package define_vars is 2 sql_stmt1 varchar2(2000) := q'# select * from scp_mvu 3 where max_amount_sold > 7000 4 order by 3 5 #'; 6 end; 7 /
PL/SQL procedure successfully completed. SQL> SQL> Rem Drop MV before importing SQL> drop materialized view scp_mvu;
Materialized view dropped. At
this stage, the export procedure has successfully completed. The next commands
prepare a directory for import purpose. The directory could be on a different
machine. SQL> conn / as sysdba Connected. SQL> create or replace directory TCB_DIR_IMP 2 as '/net/lion/test/tcb_imp'; Directory created. SQL> SQL> grant dba to test; Grant succeeded. As
the export has finished successfully, you can now transfer all the files under
TCB_DIR_EXP to a directory in test environment, for example, TCB_DIR_IMP as
created above. Again, look up and make note of the TCB metadata file
xxxxxxxxmain.xml, which will be used below.
Import as user TEST
SQL> connect test/test Connected. SQL> SQL> set serveroutput on SQL> SQL> begin 2 -- Import test case 3 dbms_sqldiag.import_sql_testcase 4 ( 5 directory => 'TCB_DIR_IMP', 6 filename => 'oratcb3_05e803500001main.xml', 7 importData => TRUE 8 ); 9 10 end; 11 /
PL/SQL procedure successfully completed.
Verification. This is to check that now all relevant objects were imported
successfully.
SQL> desc scp_mvu; Name Null? Type ----------------------------------------- -------- ------------ PROD_NAME NOT NULL VARCHAR2(50) CUST_GENDER CHAR(1) MAX_AMOUNT_SOLD NUMBER SQL> SQL> select * from scp_mvu where max_amount_sold > 7000 order by 3;
PROD_NAME C MAX_AMOUNT_SOLD -------------------------------------------------- - --------------- Joseph Sportcoat F 7400.8 Kenny Cool Leather Skirt M 7708 Leather Boot-Cut Trousers M 8184
3 rows selected. Finally,
we also have good news for 10g users: SQL
Test Case Builder has been backported to 10.2.0.4!
EXPORT_SQL_TESTCASE Procedures This procedure exports a SQL test case to a directory. Syntax This variant has to be provided with the SQL information. DBMS_SQLDIAG.EXPORT_SQL_TESTCASE ( directory IN VARCHAR2, sql_text IN CLOB, user_name IN VARCHAR2 := NULL, bind_list IN sql_binds := NULL, exportEnvironment IN BOOLEAN := TRUE, exportMetadata IN BOOLEAN := TRUE, exportData IN BOOLEAN := FALSE, exportPkgbody IN BOOLEAN := FALSE, samplingPercent IN NUMBER := 100, ctrlOptions IN VARCHAR2 := NULL, timeLimit IN NUMBER := 0, testcase_name IN VARCHAR2 := NULL, testcase IN OUT NOCOPY CLOB, preserveSchemaMapping IN BOOLEAN := FALSE, version IN VARCHAR2 := 'COMPATIBLE'); -- "version" NOT AVAILABLE IN BETA 1 This variant extracts the SQL information from an incident file. DBMS_SQLDIAG.EXPORT_SQL_TESTCASE ( directory IN VARCHAR2, incident_id IN VARCHAR2, exportEnvironment IN BOOLEAN := TRUE, exportMetadata IN BOOLEAN := TRUE, exportData IN BOOLEAN := FALSE, exportPkgbody IN BOOLEAN := FALSE, samplingPercent IN NUMBER := 100, ctrlOptions IN VARCHAR2 := NULL, timeLimit IN NUMBER := DBMS_SQLDIAG.TIME_LIMIT_DEFAULT, testcase_name IN VARCHAR2 := NULL, testcase IN OUT NOCOPY CLOB, preserveSchemaMapping IN BOOLEAN := FALSE);
EXPORT_SQL_TESTCASE_DIR_BY_INC Function This function generates a SQL Test Case corresponding to the incident ID passed as an argument. It creates a set of scripts and dump file in the directory passed as an argument. Syntax DBMS_SQLDIAG.EXPORT_SQL_TESTCASE_DIR_BY_INC ( incident_id IN NUMBER, directory IN VARCHAR2, exportEnvironment IN VARCHAR2 := 'TRUE', exportMetadata IN VARCHAR2 := 'TRUE', exportData IN VARCHAR2 := 'FALSE', samplingPercent IN VARCHAR2 := '100', ctrlOptions IN VARCHAR2 := NULL) RETURN BOOLEAN;
