oracle 11g 新功能之 SQL Test Case Builder

郑全

在Oracle11gR1中出现的很多新功能，现在都已经backport到最新的Oracle 10.2.0.4中，比如之前有谈到的Real Applcation Testing，而这次有助于客户更简单的寻求Oracle技术支持帮助的SQL Test Case Builder（TCB）也可以在Oracle 10.2.0.4中使用了。

一. 什么是Test Case Builder

TCB是Oracle数据库Fault Diagnosability Infrastructure架构中的一部分，关于FDI的架构可以参看Oracle 11gR1 Administrator文档中的相关章节。

我们先描述一个场景，假如你是某个系统的数据库管理员，忽然有一天，你发现系统中的某个SQL运行变得非常缓慢，但是在简单检查之后却无法发现问题的实际原因，因此你需要寻求Oracle技术支持的帮助，或者说，你需要一个更NB的朋友的帮忙。你需要做的是，详细地向他描述你这个SQL是怎样的，如果有绑定变量，那么值是什么，牵涉到的表结构是怎样的，数据量有多少，数据如何分布的，统计信息如何收集的，这个SQL的执行计划是怎样的，等等等等一系列情况，如果大家有经验在Oracle Metalink上寻求过Oracle Support的支持，那么应该知道这就几乎跟提供所有的trace一样繁琐。。。但是我们不能责骂对方，因为如果没有完备的信息，对方就无法重现你的错误，无法重现也就意味着即使给你提供了解决方案，那也都是基于猜测的。

能不能有一个工具，我们只需要告诉它我哪个SQL有问题了，它就能自动抓取并存放下足以重现这个问题的所有相关数据呢？好吧，TCB完全满足你的这个需求。

TCB究竟抓取了哪些数据呢？借用一下Oracle Optimizer Team的blog中相关文章中的描述。

1. 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.

2. Transient information

… 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.

二. 如何使用Test Case Builder

使用TCB很简单，只需要简短的几个步骤就可以。下面我们从Oracle 10.2.0.4中导出一个SQL的相关数据，再在Oracle 11.1.0.6中重现。

1. 从Oracle 10.2.0.4中导出SQL

1.-- 创建所需要的Directory，TCB抓取的数据将存放在这个目录中。
2.SQL> create or replace directory TCB_DIR_EXP as 'D:\oracle\tmp';
3.
4.Directory created.
5.
6.SQL> grant read,write on directory TCB_DIR_EXP to scott;
7.
8.Grant succeeded.
9.
10.-- 使用dbms_sqldiag包有些限制，为了方便直接授予scott用户dba角色
11.SQL> grant dba to scott;
12.
13.Grant succeeded.
14.
15.--创建一个包含问题SQL语句的包，当然我们也可以忽略这个步骤，而在下一步中指定该SQL在系统中的sql_id
16.create or replace package define_vars is
17.  sql_stmt1     varchar2(2000) := q'# select ename,dname,job from emp, dept
18.                        where emp.deptno= dept.deptno
19.                                    #';
20.end;
21./
22.
23.--开始导出，在export_sql_testcase存储过程中有更多的参数可以选择，具体可以参看$ORACLE_HOME/rdbms/admin/dbmsdiag.sql
24.declare
25.  tco           clob;
26.begin
27.  -- Export test case
28.  dbms_sqldiag.export_sql_testcase
29.  (
30.    directory           => 'TCB_DIR_EXP',
31.    sql_text            => define_vars.sql_stmt1,
32.    user_name           => 'SCOTT',
33.    exportData          => TRUE,
34.    testcase            => tco
35.  );

详细看一下，结果文件中都包含了哪些内容。

D:\oracle\tmp>ls
 ORATCB1_009F000D0001DPEXP.DMP
 oratcb1_009F000D0001sql.xml
 README.txt
 oratcb1_009F000D0001ssimp.sql
 oratcb1_009F000D0001dpexp.log
 oratcb1_009F000D0001xpl.txt
 oratcb1_009F000D0001dpexp.sql
 oratcb1_009F000D0001xplf.sql
 oratcb1_009F000D0001main.xml
 oratcb1_009F000D0001xplo.sql
 oratcb1_009F000D0001ol.xml
 oratcb1_009F000D0001xpls.sql

其中README.txt文件是非常体贴的设计，里面记录了这些文件各自包含的内容，同时也写明了如果在另外一个库中要导入这些文件需要执行的SQL语句。.DMP文件是TCB使用datapump技术导出的SQL相关表和数据。.xml包含了需要执行的SQL语句，该SQL语句的执行计划。

注意，在这一系列文件中一定有一个命名为oratcbn_xxxxxxmain.xml的文件，描述了所有需要导入的信息文件的名称，而这个文件名也是在下一步导入的时候需要作为参数输入的。

2. 导入到Oracle 11.1.0.6中

1.--同样需要先创建一个directory，因为在我的测试环境中是同一台机器，因此这个目录跟上面的导出目录是一样，当然在真实环境中可以是完全不同的两台机器，在这个目录中应该包含上面导出步骤中生成的所有文件，这可以通过任何文件传输的方法来实现，比如cp或者ftp。
2.SQL> create or replace directory TCB_DIR_IMP as 'D:\oracle\tmp';
3.
4.Directory created.
5.
6.--将目录的读写权限赋予一个新的用户，注意，这个用户下面没有任何上述SQL能够正确执行需要的表，没有dept表也没有emp表。
7.SQL> grant read,write on directory TCB_DIR_IMP to sztech;
8.
9.Grant succeeded.
10.
11.--以该用户登录，导入SQL
12.conn sztech
13.
14.begin
15.  -- Import test case
16.  dbms_sqldiag.import_sql_testcase
17.  (
18.    directory           => 'TCB_DIR_IMP',
19.    filename            => 'oratcb1_009F000D0001main.xml',
20.    importData          => TRUE
21.  );
22.
23.end;
24./
25.
26.--检查一下结果，导入过程中TCB自动创建了相关的表和索引。
27.SQL> col object_name for a20
28.SQL> select object_name, object_type, created from user_objects
29.  2  where trunc(created)=trunc(sysdate);
30.
31.OBJECT_NAME          OBJECT_TYPE         CREATED
32.-------------------- ------------------- ------------
33.DEFINE_VARS          PACKAGE             16-SEP-08
34.PK_EMP               INDEX               16-SEP-08
35.PK_DEPT              INDEX               16-SEP-08
36.EMP                  TABLE               16-SEP-08
37.DEPT                 TABLE               16-SEP-08

三. 结论
 有了TCB之后，在要求远程的某人帮助调整一个SQL或者一段PL/SQL的时候，只需要将相关内容exp出来，然后将结果文件的存放目录打个包，给对方发过去就OK了。

[此贴子已经被作者于2013-10-3 12:02:54编辑过]