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.
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. 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.
How do I use the SQL Test Case Builder?
- 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
2 FROM V$SQL
3 WHERE SQL_ID ='8khuzqdww5thy'
4 /
SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
SELECT pr.prod_id, sa.promo_id,count(*) as total_customers from sh.products pr, sh.sales sa where pr.prod_id = sa.prod_id g
roup by pr.prod_id, sa.promo_id
sh@11G> DECLARE
2 l_test_case CLOB;
3 begin
4 dbms_sqldiag.export_sql_testcase(
5 directory =>'DATA_PUMP_DIR',
6 sql_id => '8khuzqdww5thy',
7 plan_hash_value => null,
8 exportEnvironment => true,
9 exportMetadata => true,
10 exportData => true,
11 timeLimit => 3600,
12 testcase_name =>'QUERY_HR_SCHEMA',
13 testcase =>l_test_case);
14 end;
15 /
PL/SQL procedure successfully completed.
You have the option to set exportData to TRUE to include the data. For security reason, the user data are not exported 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, which contains the metadata of the test case.
Now importing the Test case can be done as
scott@ORCL> create user test_user identified by test_user default tablespace EXAMPLE quota unlimited on EXAMPLE;
User created.
scott@ORCL> grant connect,resource,dba to test_user;
Grant succeeded.
scott@ORCL> grant read on directory DATA_PUMP_DIR to test_user;
Grant succeeded.
scott@ORCL> grant write on directory DATA_PUMP_DIR to test_user;
Grant succeeded.
scott@ORCL> connect test_user/test_user
Connected.
test_user@ORCL> begin
2 dbms_sqldiag.import_sql_testcase(
3 directory=>'DATA_PUMP_DIR',
4 filename =>'QUERY_HR_SCHEMAmain.xml',
5 importEnvironment=>true,
6 importMetadata=>true,
7 importData=>true,
8 importDiagnosis=>true);
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:02:13.24
test_user@ORCL> SELECT table_name, status
2 FROM user_tables
3 /
TABLE_NAME STATUS
------------------------------ --------
PRODUCTS VALID
SALES VALID
test_user@ORCL> SELECT pr.prod_id,
2 sa.promo_id,
3 COUNT(*) AS total_customers
4 FROM products pr,
5 sales sa
6 WHERE pr.prod_id = sa.prod_id
7 GROUP BY pr.prod_id,sa.promo_id
8 /
PROD_ID PROMO_ID TOTAL_CUSTOMERS
---------- ---------- ---------------
17 999 5962
18 999 9206
23 999 19022
25 999 18943
...............................................
...............................................
...............................................
212 rows selected
Elapsed: 00:00:01.06
test_user@ORCL>
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.
No comments:
Post a Comment