开Bug必备，wrong result问题必备。不解释，直接贴action plan。
1)在以下文档中下载并安装最新的 SQLT:
SQLT (SQLTXPLAIN) – Tool that helps to diagnose SQL statement performing poorly (Doc ID 215187.1)
Install SQLT:
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql
2)生成SQLT report（包含不带数据的testcase，如果生成带数据的，请直接跳到第3步):
cd sqlt/run
sqlplus / as sysdba
START sqltxtract.sql <SQL_ID> <======替换<SQL_ID>成你想要生成报告的SQL ID.
比如：
START sqltxtract.sql bkvbqs9tjpufv
如果是12c以上版本，使用SYSDBA是无法正确生成报告的，必须赋权或者使用其他用户：
sqlplus / as sysdba
GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;
START sqltxtract.sql bkvbqs9tjpufv
或者使用其他用户
sqlplus oracle/oracle
START sqltxtract.sql bkvbqs9tjpufv
…
中间过程忽略，在这一步输入SQLTXPLAIN用户的密码(希望你还记得安装时输入过的）
Paremeter 2:
SQLTXPLAIN password (required)
Enter value for 2: <====输入SQLTXPLAIN用户的密码(希望你还记得安装时输入过的）
其他一路回车
…
在当前目录下生成以下文件：
ls -l
-rw-r–r– 1 oracle dba 2592472 Nov 9 14:18 sqlt_s48357_xtract_bkvbqs9tjpufv.zip
-rw-r–r– 1 oracle dba 238 Nov 9 14:18 sqlt_s48357_purge.sql
如何使用这个报告呢？
在目标机器解压：
unzip sqlt_s48357_xtract_bkvbqs9tjpufv.zip -d s48357
[oracle@nascds18 run]$ cd s48357
[oracle@nascds18 s48357]$ ls
sqlt_s48357_10053_explain.trc sqlt_s48357_readme.html
sqlt_s48357_10053_i1_c0_extract.trc sqlt_s48357_sql_detail_active.html
sqlt_s48357_cell_state.zip sqlt_s48357_sqldx.zip
sqlt_s48357_driver.zip sqlt_s48357_tc_script.sql
sqlt_s48357_lite.html sqlt_s48357_tc_sql.sql
sqlt_s48357_log.zip sqlt_s48357_tcx.zip
sqlt_s48357_main.html sqlt_s48357_tc.zip
sqlt_s48357_opatch.zip sqlt_s48357_trc.zip
unzip sqlt_s48357_tc.zip -d tc
cd tc
./xpress.sh
…
3/7 Press ENTER to import SQLT repository for statement_id 48357.
SQL> HOS imp SQLTXPLAIN FILE=sqlt_s48357_exp.dmp LOG=sqlt_s48357_imp.log TABLES=sqlt% IGNORE=Y
Import: Release 12.2.0.1.0 – Production on Thu Nov 9 14:21:58 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
<====输入SQLTXPLAIN用户的密码(希望你还记得安装时输入过的）
Export file created by EXPORT:V12.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SQLTXPLAIN’s objects into SQLTXPLAIN
. importing SQLTXPLAIN’s objects into SQLTXPLAIN
. . importing table “SQLT$_SQL_STATEMENT” 1 rows imported
. . importing table “SQLT$_AUX_STATS$” 13 rows imported
. . importing table “SQLT$_DBA_AUTOTASK_CLIENT” 1 rows imported
. . importing table “SQLT$_DBA_AUTOTASK_CLIENT_HST” 20 rows imported
. . importing table “SQLT$_DBA_HIST_SNAPSHOT” 198 rows imported
. . importing table “SQLT$_DBA_OBJECTS” 1 rows imported
. . importing table “SQLT$_DBA_OPTSTAT_OPERATIONS” 3054 rows imported
. . importing table “SQLT$_DBA_SEGMENTS” 1 rows imported
. . importing table “SQLT$_DBA_TAB_COLS” 1 rows imported
. . importing table “SQLT$_DBA_TAB_MODIFICATIONS” 1 rows imported
. . importing table “SQLT$_DBA_TAB_STATISTICS” 1 rows imported
. . importing table “SQLT$_DBA_TABLES” 1 rows imported
. . importing table “SQLT$_DBA_TABLESPACES” 5 rows imported
. . importing table “SQLT$_DBMS_XPLAN” 77 rows imported
. . importing table “SQLT$_GV$NLS_PARAMETERS” 19 rows imported
. . importing table “SQLT$_GV$OBJECT_DEPENDENCY” 2 rows imported
. . importing table “SQLT$_GV$PARAMETER2” 428 rows imported
. . importing table “SQLT$_GV$PARAMETER_CBO” 505 rows imported
. . importing table “SQLT$_GV$PQ_SLAVE” 8 rows imported
. . importing table “SQLT$_GV$PQ_SYSSTAT” 20 rows imported
. . importing table “SQLT$_GV$PX_PROCESS” 8 rows imported
. . importing table “SQLT$_GV$PX_PROCESS_SYSSTAT” 15 rows imported
. . importing table “SQLT$_GV$SQL” 1 rows imported
. . importing table “SQLT$_GV$SQL_OPTIMIZER_ENV” 1 rows imported
. . importing table “SQLT$_GV$SQL_PLAN” 2 rows imported
. . importing table “SQLT$_GV$SQL_SHARED_CURSOR” 1 rows imported
. . importing table “SQLT$_GV$SQLAREA” 1 rows imported
. . importing table “SQLT$_GV$SQLAREA_PLAN_HASH” 1 rows imported
. . importing table “SQLT$_GV$SQLSTATS” 1 rows imported
. . importing table “SQLT$_GV$SQLSTATS_PLAN_HASH” 1 rows imported
. . importing table “SQLT$_GV$SQLTEXT_WITH_NEWLINES” 1 rows imported
. . importing table “SQLT$_GV$SYSTEM_PARAMETER” 426 rows imported
. . importing table “SQLT$_LOG” 922 rows imported
. . importing table “SQLT$_METADATA” 3 rows imported
. . importing table “SQLT$_NLS_DATABASE_PARAMETERS” 20 rows imported
. . importing table “SQLT$_OUTLINE_DATA” 28 rows imported
. . importing table “SQLT$_PLAN_EXTENSION” 4 rows imported
. . importing table “SQLT$_PLAN_INFO” 16 rows imported
. . importing table “SQLT$_SQL_PLAN_TABLE” 2 rows imported
. . importing table “SQLT$_V$SESSION_FIX_CONTROL” 1301 rows imported
. . importing table “SQLT$_WRI$_ADV_TASKS” 1 rows imported
. . importing table “SQLT$_WRI$_OPTSTAT_AUX_HISTORY” 36 rows imported
Import terminated successfully with warnings.
…
最终会生成一个TCxxxxxx用户，就是你文件名里的这个号码sqlt_s48357_xtract_bkvbqs9tjpufv.zip，表在该用户下，但是没数据：
SQL> select * from TC48357.test;
no rows selected
3)生成包含数据的SQLT report（请注意原表中的数据不要太大，或者使用tcb_sampling_percent取百分比的数据):
主要增加了以下设置：
EXEC sqltxadmin.sqlt$a.set_sess_param(‘tcb_export_data’, ‘TRUE’);
EXEC sqltxadmin.sqlt$a.set_sess_param(‘tcb_export_pkg_body’, ‘TRUE’);
EXEC sqltxadmin.sqlt$a.set_sess_param(‘tcb_sampling_percent’, ’10’);
请注意表中数据不要太大，清理数据的同时请保持能够重现问题。
请注意使用tcb_sampling_percent可能会导致无法重现问题。基于这个原因，以下步骤去掉了这个设置，您可以根据需要增加。
更早版本的设置请参考：
How to Use SQLT (SQLTXPLAIN) to Create a Testcase Containing Application Data (Doc ID 1465741.1)
以下是具体步骤：
cd sqlt/run
sqlplus / as sysdba
START sqltxtract.sql <SQL_ID> <======替换<SQL_ID>成你想要生成报告的SQL ID.
比如：
EXEC sqltxadmin.sqlt$a.set_sess_param(‘tcb_export_data’, ‘TRUE’);
EXEC sqltxadmin.sqlt$a.set_sess_param(‘tcb_export_pkg_body’, ‘TRUE’);
START sqltxtract.sql bkvbqs9tjpufv
如果是12c以上版本，使用SYSDBA是无法正确生成报告的，必须赋权或者使用其他用户：
sqlplus / as sysdba
GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;
EXEC sqltxadmin.sqlt$a.set_sess_param(‘tcb_export_data’, ‘TRUE’);
EXEC sqltxadmin.sqlt$a.set_sess_param(‘tcb_export_pkg_body’, ‘TRUE’);
START sqltxtract.sql bkvbqs9tjpufv
或者使用其他用户
sqlplus oracle/oracle
EXEC sqltxadmin.sqlt$a.set_sess_param(‘tcb_export_data’, ‘TRUE’);
EXEC sqltxadmin.sqlt$a.set_sess_param(‘tcb_export_pkg_body’, ‘TRUE’);
START sqltxtract.sql bkvbqs9tjpufv
…
…
中间过程忽略，在这一步输入SQLTXPLAIN用户的密码(希望你还记得安装时输入过的）
Paremeter 2:
SQLTXPLAIN password (required)
Enter value for 2: <====输入SQLTXPLAIN用户的密码(希望你还记得安装时输入过的）
其他一路回车
…
在当前目录下生成以下文件：
ls -l
-rw-r–r– 1 oracle dba 2055250 Nov 9 15:17 sqlt_s48361_xtract_bkvbqs9tjpufv.zip
-rw-r–r– 1 oracle dba 238 Nov 9 15:17 sqlt_s48361_purge.sql
如何使用这个报告呢？
在目标机器解压：
unzip sqlt_s48361_xtract_bkvbqs9tjpufv.zip -d s48361
[oracle@nascds18 run]$ cd s48361
[oracle@nascds18 s48357]$ ls
sqlt_s48361_10053_explain.trc sqlt_s48361_readme.html
sqlt_s48361_10053_i1_c0_extract.trc sqlt_s48361_sql_detail_active.html
sqlt_s48361_cell_state.zip sqlt_s48361_sqldx.zip
sqlt_s48361_driver.zip sqlt_s48361_tcb.zip
sqlt_s48361_lite.html sqlt_s48361_tc_script.sql
sqlt_s48361_log.zip sqlt_s48361_tc_sql.sql
sqlt_s48361_main.html sqlt_s48361_tcx.zip
sqlt_s48361_opatch.zip sqlt_s48361_tc.zip
sqlt_s48361_perfhub_0001__.html sqlt_s48361_trc.zip
unzip sqlt_s48361_tc.zip -d tc
cd tc
./xpress.sh
…
3/7 Press ENTER to import SQLT repository for statement_id 48357.
SQL> HOS imp SQLTXPLAIN FILE=sqlt_s48357_exp.dmp LOG=sqlt_s48357_imp.log TABLES=sqlt% IGNORE=Y
Import: Release 12.2.0.1.0 – Production on Thu Nov 9 14:21:58 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
<====输入SQLTXPLAIN用户的密码(希望你还记得安装时输入过的）
然后使用unzip sqlt_s48361_tcb.zip文件，tcb才会包含数据：
unzip sqlt_s48361_tcb.zip -d tcb
cd tcb
[oracle@nascds18 tcb]$ ls
sqlt_s48361_tcb_dpexp.dmp sqlt_s48361_tcb_prmimp.sql sqlt_s48361_tcb_ts.xml
sqlt_s48361_tcb_dpexp.log sqlt_s48361_tcb_README.txt sqlt_s48361_tcb_xplf.sql
sqlt_s48361_tcb_dpexp.sql sqlt_s48361_tcb_smrpt.html sqlt_s48361_tcb_xplo.sql
sqlt_s48361_tcb_dpimp.sql sqlt_s48361_tcb_sql.xml sqlt_s48361_tcb_xpls.sql
sqlt_s48361_tcb_main.xml sqlt_s48361_tcb_ssimp.sql sqlt_s48361_tcb_xpl.txt
sqlt_s48361_tcb_ol.xml sqlt_s48361_tcb_.trc
检查下这些文件所在的文件夹：
pwd
/home/oracle/sqlt/run/s48361/tcb
sqlplus / as sysdba
create directory imp_tc as ‘/home/oracle/feng/sqlt/run/s48361/tcb’;<====将这个文件夹建成目录
grant read on directory imp_tc to TC48361;
grant write on directory imp_tc to TC48361;
connect TC48361/TC48361
begin
dbms_sqldiag.import_sql_testcase(directory=>’IMP_TC’, filename =>’sqlt_s48361_tcb_main.xml’);
end;
/
SQL> select * from TC48361.test;
A
———-
1
<===========数据被导入