xplore是SQLT中带的一个工具，它可以帮助我们查证CBO fixes和parameters对SQL语句的影响。这次做SPA，遇到一个SQL，搞了一天都没办法搞定。尝试了原库的统计信息和直方图，收集新库的统计信息和直方图，重建索引，都没办法解决。那么这个时候，可能就是10g和11g优化器参数不同导致的了。但是优化器的_fix_control和parameter太多了，你要一个一个的去测试，工作量巨大。但是使用xplore确非常简单，咱们只需要把脚本一挂，xplore就会自动去循环试验每一个优化器的_fix_control和parameter。运行完成之后，它会打包生成一个.zip的文件，然后我们解压开里面会有一个html的report，它会把每一个参数修改后的执行计划列出来，并把逻辑读物理读还有cost一一列出。这样我们就可以分析出是那个优化器Bug导致的执行计划问题。
1.要使用xplore首先得进行安装。你先要考虑安装在具体的某个用户下，这个用户有执行你要分析SQL的权限（避免出现表和视图不存在的错误）。
SQL> @/home/oracle/sqlt/utl/xplore/install.sql Test Case User: SPA Password: SPA 安装完成之后，会提示下列信息。 Installation completed. You are now connected as SPA. 1. Set CBO env if needed 2. Execute @create_xplore_script.sql
2.如果我们现在需要特定的CBO环境，那么在开始create_xplore_script.sql之前，需要设置所需的参数(比如我们经常设置的alter session set statistics_level=all)。如果没有就直接开始第二步。执行create_xplore_script.sql脚本
SQL> @create_xplore_script.sql Parameter 1: XPLORE Method: XECUTE (default) or XPLAIN "XECUTE" requires /* ^^unique_id */ token in SQL "XPLAIN" uses "EXPLAIN PLAN FOR" command Remember EXPLAIN PLAN FOR does not perform bind peeking Enter "XPLORE Method" [XECUTE]: XPLAIN Parameter 2: Include CBO Parameters: Y (default) or N Enter "CBO Parameters" [Y]: Parameter 3: Include Exadata Parameters: Y (default) or N Enter "EXADATA Parameters" [Y]: Parameter 4: Include Fix Control: Y (default) or N Enter "Fix Control" [Y]: Parameter 5: Generate SQL Monitor Reports: N (default) or Y Only applicable when XPLORE Method is XECUTE Enter "SQL Monitor" [N]:N Review and execute @xplore_script_1.sql
第一个参数是XECUTE和XPLAIN，XECUTE是真正的执行。XPLAIN是使用EXPLAIN PLAN FOR生成执行计划，但是这里它提醒一点是不执行绑定变量。所有如果你的SQL带绑定变量的话，就把值直接带入。统计信息如果足够准确，可以选择XPLAIN，XPLAIN的速度要快很多。
第二个参数是是否包含CBO Parameters，这里选择Y。
第三个参数是是否包含EXADATA Parameters,因为不是Exadata,所以这里选择N。
第四个参数是否包含”Fix Control”,这里选择Y。
第五个参数是否包生成SQL Monitor报告，只有你选择了XECUTE，你这里才能选择Y，我们选择XPLAIN的话，这里选择N。
3.接下来我们要创建一个SQL脚本，把想要执行的SQL放在脚本里面。例如a1.sql，这里要给弄个标识符号，我这里随便弄了一个/* ^^ t1*/，这个标识符它不是hint，没有+号
[oracle@pfdb1 xplore]$ more a1.sql SELECT /* ^^ t1*/ * FROM ( SELECT A.ACC_NBR,A.ACT_TYPE,A.ALARM_DATE,A.AREA_ID,A.AR_FLAG,A.ASGN_DATE, A.AZ_FLAG,A.BOOKED_FLAG,A.BOOK_FLAG,A.BRANCH_ID,A.BUSI_STS,A.BUSI_STS_DATE,A.COLLAB_WO_NBR, A.COMPL_DATE,A.DEAL_FLAG,A.DO_TIME,A.DYN_FLAG,A.EXCH_ID,A.FAIL_REASON_ID,A.FULL_FLAG,A.HALT, A.HALT_DATE,A.LOCAL_NET_ID,A.MAINT_AREA_ID,A.MAIN_FLAG,A.MERG_FLAG,A.MT_AREA_NAME,A.NOTIFY_FLAG, A.N_WO_NBR,A.OVERTIME_ID,A.PRE_ALARM_DATE,A.PRINT_COUNT,A.PRIORITY,A.REASGN_COUNT,A.RELA_WO_NBR, A.REMARKS,A.RT_STAFF_ID,A.RT_STAFF_NAME,A.RUN_STS,A.RUN_STS_DATE,A.SERV_DEPT_ID,A.SERV_INST_ID, A.SO_NBR,A.STEP_ID,A.WORK_AREA_ID,A.WORK_ITEM_ID,A.WORK_MODE,A.WO_NBR,A.WO_STAFF_ID,A.WO_STAFF_NAME, A.WO_TYPE,B.CSO_NBR FROM BDWSPS.WO A,BDWSPS.SO B,BDWSPS.STEP S WHERE 1=1 AND (A.LOCAL_NET_ID = 1005 ) AND A.WORK_MODE = 'A' AND B.STS = 'A' AND A.SO_NBR = B.SO_NBR AND((A.RUN_STS = 'D' AND B.SO_LOCK_STS = 'N') OR A.RUN_STS = 'W') AND S.STEP_ID = A.STEP_ID AND (S.WORK_SYSTEM = 'RMS') AND A.REASGN_COUNT <= 0 AND ((B.REAL_TIME_FLAG='Y' AND A.BUSI_STS='I') OR (B.REAL_TIME_FLAG='N' AND (A.BUSI_STS='N' OR A.BUSI_STS='I'))) AND MOD(B.CSO_NBR,5) = 2 ORDER BY A.PRIORITY DESC,A.WO_NBR) WHERE ROWNUM <= 100;
4.运行xplore_script_1.sql脚本，并带入参数a1.sql。
SQL> @xplore_script_1.sql a1.sql
CONNECTED_USER
------------------------------
SPA
Parameter 1:
Name of SCRIPT file that contains SQL to be xplored (required)
Parameter 2:
Password for SPA (required)
Enter value for 2: SPA
Value passed to xplore_script.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SCRIPT_WITH_SQL: a1.sql
-- begin common
DEF _SQLPLUS_RELEASE
SELECT USER FROM DUAL
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') current_time FROM DUAL
SELECT * FROM v$version
SELECT * FROM v$instance
SELECT name, value FROM v$parameter2 WHERE name LIKE '%dump_dest'
SELECT directory_name||' '||directory_path directories FROM dba_directories WHERE directory_name LIKE 'SQLT$%' OR directory_name LIKE 'TRCA$%' ORDER BY 1
-- end common
SQL>--in case of disconnects, suspect 6356566 and un-comment workaround in line below if needed
SQL>--ALTER SESSION SET "_cursor_plan_unparse_enabled" = FALSE;
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>--
SQL>COL run_id NEW_V run_id FOR A4;
SQL>SELECT LPAD((NVL(MAX(run_id), 0) + 1), 4, '0') run_id FROM xplore_test;
RUN_
----
0001
SQL>--
SQL>DELETE plan_table_all WHERE statement_id LIKE 'xplore_{001}_[^^run_id.]_(%)';
old 1: DELETE plan_table_all WHERE statement_id LIKE 'xplore_{001}_[^^run_id.]_(%)'
new 1: DELETE plan_table_all WHERE statement_id LIKE 'xplore_{001}_[0001]_(%)'
0 rows deleted.
SQL>EXEC xplore.set_baseline(1);
--
-- begin set_baseline
--
--
-- end set_baseline
--
PL/SQL procedure successfully completed.
SQL>--
SQL>SET BLO .
SQL>GET ^^script_with_sql.
1 SELECT /* ^^ t1*/ * FROM ( SELECT A.ACC_NBR,A.ACT_TYPE,A.ALARM_DATE,A.AREA_ID,A.AR_FLAG,A.ASGN_DATE,
2 A.AZ_FLAG,A.BOOKED_FLAG,A.BOOK_FLAG,A.BRANCH_ID,A.BUSI_STS,A.BUSI_STS_DATE,A.COLLAB_WO_NBR,
3 A.COMPL_DATE,A.DEAL_FLAG,A.DO_TIME,A.DYN_FLAG,A.EXCH_ID,A.FAIL_REASON_ID,A.FULL_FLAG,A.HALT,
4 A.HALT_DATE,A.LOCAL_NET_ID,A.MAINT_AREA_ID,A.MAIN_FLAG,A.MERG_FLAG,A.MT_AREA_NAME,A.NOTIFY_FLAG,
5 A.N_WO_NBR,A.OVERTIME_ID,A.PRE_ALARM_DATE,A.PRINT_COUNT,A.PRIORITY,A.REASGN_COUNT,A.RELA_WO_NBR,
6 A.REMARKS,A.RT_STAFF_ID,A.RT_STAFF_NAME,A.RUN_STS,A.RUN_STS_DATE,A.SERV_DEPT_ID,A.SERV_INST_ID,
7 A.SO_NBR,A.STEP_ID,A.WORK_AREA_ID,A.WORK_ITEM_ID,A.WORK_MODE,A.WO_NBR,A.WO_STAFF_ID,A.WO_STAFF_NAME,
8 A.WO_TYPE,B.CSO_NBR FROM BDWSPS.WO A,BDWSPS.SO B,BDWSPS.STEP S WHERE 1=1 AND (A.LOCAL_NET_ID = 1005 )
9 AND A.WORK_MODE = 'A' AND B.STS = 'A' AND A.SO_NBR = B.SO_NBR AND((A.RUN_STS = 'D' AND B.SO_LOCK_STS = 'N') OR A.RUN_STS = 'W')
10 AND S.STEP_ID = A.STEP_ID AND (S.WORK_SYSTEM = 'RMS') AND A.REASGN_COUNT <= 0
11 AND ((B.REAL_TIME_FLAG='Y' AND A.BUSI_STS='I') OR (B.REAL_TIME_FLAG='N' AND (A.BUSI_STS='N' OR A.BUSI_STS='I')))
12* AND MOD(B.CSO_NBR,5) = 2 ORDER BY A.PRIORITY DESC,A.WO_NBR) WHERE ROWNUM <= 100; SQL>.
SQL>C/;/
12* AND MOD(B.CSO_NBR,5) = 2 ORDER BY A.PRIORITY DESC,A.WO_NBR) WHERE ROWNUM <= 100 SQL>0 EXPLAIN PLAN SET statement_id = 'xplore_{001}_[^^run_id.]_(00000)' INTO plan_table_all FOR
SQL>L
1 EXPLAIN PLAN SET statement_id = 'xplore_{001}_[^^run_id.]_(00000)' INTO plan_table_all FOR
2 SELECT /* ^^ t1*/ * FROM ( SELECT A.ACC_NBR,A.ACT_TYPE,A.ALARM_DATE,A.AREA_ID,A.AR_FLAG,A.ASGN_DATE,
3 A.AZ_FLAG,A.BOOKED_FLAG,A.BOOK_FLAG,A.BRANCH_ID,A.BUSI_STS,A.BUSI_STS_DATE,A.COLLAB_WO_NBR,
4 A.COMPL_DATE,A.DEAL_FLAG,A.DO_TIME,A.DYN_FLAG,A.EXCH_ID,A.FAIL_REASON_ID,A.FULL_FLAG,A.HALT,
5 A.HALT_DATE,A.LOCAL_NET_ID,A.MAINT_AREA_ID,A.MAIN_FLAG,A.MERG_FLAG,A.MT_AREA_NAME,A.NOTIFY_FLAG,
6 A.N_WO_NBR,A.OVERTIME_ID,A.PRE_ALARM_DATE,A.PRINT_COUNT,A.PRIORITY,A.REASGN_COUNT,A.RELA_WO_NBR,
7 A.REMARKS,A.RT_STAFF_ID,A.RT_STAFF_NAME,A.RUN_STS,A.RUN_STS_DATE,A.SERV_DEPT_ID,A.SERV_INST_ID,
8 A.SO_NBR,A.STEP_ID,A.WORK_AREA_ID,A.WORK_ITEM_ID,A.WORK_MODE,A.WO_NBR,A.WO_STAFF_ID,A.WO_STAFF_NAME,
9 A.WO_TYPE,B.CSO_NBR FROM BDWSPS.WO A,BDWSPS.SO B,BDWSPS.STEP S WHERE 1=1 AND (A.LOCAL_NET_ID = 1005 )
10 AND A.WORK_MODE = 'A' AND B.STS = 'A' AND A.SO_NBR = B.SO_NBR AND((A.RUN_STS = 'D' AND B.SO_LOCK_STS = 'N') OR A.RUN_STS = 'W')
11 AND S.STEP_ID = A.STEP_ID AND (S.WORK_SYSTEM = 'RMS') AND A.REASGN_COUNT <= 0
12 AND ((B.REAL_TIME_FLAG='Y' AND A.BUSI_STS='I') OR (B.REAL_TIME_FLAG='N' AND (A.BUSI_STS='N' OR A.BUSI_STS='I')))
13* AND MOD(B.CSO_NBR,5) = 2 ORDER BY A.PRIORITY DESC,A.WO_NBR) WHERE ROWNUM <= 100 SQL>/
old 1: EXPLAIN PLAN SET statement_id = 'xplore_{001}_[^^run_id.]_(00000)' INTO plan_table_all FOR
new 1: EXPLAIN PLAN SET statement_id = 'xplore_{001}_[0001]_(00000)' INTO plan_table_all FOR
Enter value for t1:
这里需要输入两个参数，第一个是SPA用户的密码，第二个是随便设置一个标识符，这个t1就是我们在上面设置的/* ^^ t1*/里面的，这里我们随便输入一个什么标识，它就会自动把t1给替换掉。输入完成之后，这个脚本就开始运行了。
5.检查xplore_1.zip文件中的报告。
这里可以看到有很多不同的计划。接下来我们找个Buffer少的看一下。
这里提供的解决方案是“ALTER SESSION SET “_fix_control” = ‘12555499:0’;”。然后我们继续看它的执行计划。
我们根据这个可以去MOS上查，查到了相关的Bug：Bug 12555499 : EXECUTION PLAN IS DIFFERENT WHEN USING NCHAR OR VARCHAR2 。该Bug会导致执行计划不一致。