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