Friday Dec 02, 2011
Wednesday Nov 16, 2011
By Maria Colgan-Oracle on Nov 16, 2011
Tuesday Sep 01, 2009
By Richard Liang on Sep 01, 2009
Friday Mar 14, 2008
By Richard Liang on Mar 14, 2008
Why SQL Test Case Builder?
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
- 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.
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.
- 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.
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.
- Export as user APPS
- Import as user TEST
- Verification. This is to check that now all relevant objects were imported successfully.
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.
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 / Package created. SQL> SQL> set serveroutput on SQL> SQL> declare 2 tco clob; 3 begin 4 -- Export test case 5 dbms_sqldiag.export_sql_testcase 6 ( 7 directory => 'TCB_DIR_EXP', 8 sql_text => define_vars.sql_stmt1, 9 user_name => 'APPS', 10 exportData => TRUE, 11 testcase => tco 12 ); 13 14 end; 15 / 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.
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.
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!
The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.
- Global Temporary Tables and Upgrading to Oracle Database 12c - Don't Get Caught Out
- Optimizer Feature Differences Between Oracle Database Releases
- How to Use SQL Plan Management
- Group-by and Aggregation Elimination
- Upgrade to Oracle Database 12c and Avoid Query Regression
- Oracle OpenWorld 2015
- Tips on SQL Plan Management and Oracle Database In-Memory – Part 3
- Tips on SQL Plan Management and Oracle Database In-Memory - Part 2
- Tips on SQL Plan Management and Oracle Database In-Memory Part 1
- What you need to know about SQL Plan Management and Auto Capture
Optimizer White papers
- Best Practices For Gathering Optimizer Statistics In Oracle Database 11g - Part2
- SQL Plan Management in Oracle Database 12c
- Understanding Optimizer Statistics in Oracle Database 12c
- Upgrading from 11g to 12c: What to expect from the Optimizer
- Best Practices For Gathering Optimizer Statistics In Oracle Database 12 - Part2
- SQL Plan Management in Oracle Database 11g
- Understanding Optimizer Statistics in Oracle Database 11g
- Upgrading from 10g to 11g: What to expect from the Optimizer
- Explain the Explain Plan
- Upgrading from 9i to 10g: What to expect from the Optimizer
Optimizer Development Technical Papers
- Closing the Query Processing Loop in Oracle 11g
- Cost-Based Query Transformation in Oracle
- Efficient and scalable statistics gathering for large databases in Oracle 11g
- Enhanced Subquery Optimizations in Oracle, VLDB 2009
- Optimizer plan change management: improved stability and performance in Oracle 11g
- /Cursor Sharing
- /Diagnostic tools
- /How do I
- /Oracle OpenWorld
- /Oracle Optimizer
- /SQL Plan Management
- /Upcoming events