xplore-SQL优化的高级武器

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会导致执行计划不一致。