首页 » ORACLE » Index join SQL性能故障 之 disable index_join 使用sql profile hint, parameter 和sql patch

Index join SQL性能故障 之 disable index_join 使用sql profile hint, parameter 和sql patch

前面的方法我们还原了问题现象,因为CBO使用错误的统计信息,使用了INDEX_JOIN的方式, 现在尝试如何快速的解决该问题,也就是禁用INDEX_JOIN或使用我们指定的执行计划。

下面会使用以下几种方法来修正执行计划
sql profile + hint
sql profile + parameter
set index part stats
sql patch + hint

SQL> explain plan for SELECT    /*anbob*/   ATTRID,              ATTRVALUE   FROM       anbob.ANBOB_T1
    WHERE       RECOID = :RECOID    AND       REGION = 399;

Explained.

SQL> @x2

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 3978804789

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                      |   985K|   178M|   197   (1)| 00:00:03 |       |       |
|*  1 |  VIEW                      | index$_join$_001     |   985K|   178M|   197   (1)| 00:00:03 |       |       |
|*  2 |   HASH JOIN                |                      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|                      |   985K|   178M|   136  (92)| 00:00:02 |    65 |    74 |
|*  4 |     INDEX RANGE SCAN       | IDX_ANBOB_T1         |   985K|   178M|   136  (92)| 00:00:02 |    65 |    74 |
|   5 |    PARTITION RANGE ITERATOR|                      |   985K|   178M|   232   (1)| 00:00:03 |    65 |    74 |
|*  6 |     INDEX FAST FULL SCAN   | IDX_ANBOB_T1_VALUE   |   985K|   178M|   232   (1)| 00:00:03 |    65 |    74 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RECOID"=TO_NUMBER(:RECOID))
   2 - access(ROWID=ROWID)
   4 - access("RECOID"=TO_NUMBER(:RECOID))
   6 - filter("REGION"=399)
   
Note
-----
- dynamic sampling used for this statement (level=2)

25 rows selected.

note:
上面使用了INDEX JOIN的方法,我们发现之前使用的索引只是IDX_ANBOB_T1,并且效率比现在的index_join要高,下面尝试使用SQL中加HINT的方式对比一下效果
# 使用手动加no_index hint方法

SQL> explain plan for SELECT   /*+no_index(ANBOB_T1 IDX_ANBOB_T1_VALUE)*/    ATTRID,              ATTRVALUE   FROM       anbob.ANBOB_T1
  2      WHERE       RECOID = :RECOID    AND       REGION = 399;

Explained.

SQL> @x2

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 491038779

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |   985K|   178M|  3104   (1)| 00:00:38 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                 |   985K|   178M|  3104   (1)| 00:00:38 |    65 |    74 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| ANBOB_T1        |   985K|   178M|  3104   (1)| 00:00:38 |    65 |    74 |
|*  3 |    INDEX RANGE SCAN                | IDX_ANBOB_T1    |  5637K|       |    11   (0)| 00:00:01 |    65 |    74 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("REGION"=399)
   3 - access("RECOID"=TO_NUMBER(:RECOID))

Note
-----
   - dynamic sampling used for this statement (level=2)

Note:
手动用hint no_index(tablename indexname)的方法是可以禁用index_join的

-- 取到SQL_ID ,下面使用SQL PROFILE 的方法尝试禁用index_join
SQL> @sqlt anbob
HASH_VALUE SQL_ID             CHLD# OPT_MODE   SQL_TEXT
---------- ------------- ---------- ---------- ----------------------------------------------------------------------------------------------------
 983567814 03j7fz4xa03f6          0 ALL_ROWS   SELECT    /*anbob*/   ATTRID,              ATTRVALUE   FROM       anbob.ANBOB_T1     WHERE
                                               RECOID = :RECOID    AND       REGION = 399

3292410184 gbr0tjv23wca8          0 ALL_ROWS   EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR SELECT    /*anbob*/   ATTRID,
                                               ATTRVALUE   FROM       anbob.ANBOB_T1     WHERE       RECOID = :RECOID    AND       REGION = 399
SQL> DECLARE
  2    SQL_FTEXT CLOB;
  3   BEGIN
  4   SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '03j7fz4xa03f6';
  5   
  6   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  7     SQL_TEXT => SQL_FTEXT,
  8     PROFILE => SQLPROF_ATTR('no_index(ANBOB_T1 IDX_ANBOB_T1_VALUE)'),
  9     NAME => 'PROFILE_03j7fz4xa03f6',
 10     REPLACE => TRUE,
 11     FORCE_MATCH => TRUE
 12   );
 13   END;
 14  /

PL/SQL procedure successfully completed.


SQL> set autot trace exp 
SQL> SELECT    /*anbob*/   ATTRID,              ATTRVALUE   FROM       anbob.ANBOB_T1
  2          WHERE       RECOID = :RECOID    AND       REGION = 399;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3978804789

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                      |  1362K|   246M|   197   (1)| 00:00:03 |       |       |
|*  1 |  VIEW                      | index$_join$_001     |  1362K|   246M|   197   (1)| 00:00:03 |       |       |
|*  2 |   HASH JOIN                |                      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|                      |  1362K|   246M|   183  (94)| 00:00:03 |    65 |    74 |
|*  4 |     INDEX RANGE SCAN       | IDX_ANBOB_T1         |  1362K|   246M|   183  (94)| 00:00:03 |    65 |    74 |
|   5 |    PARTITION RANGE ITERATOR|                      |  1362K|   246M|   232   (1)| 00:00:03 |    65 |    74 |
|*  6 |     INDEX FAST FULL SCAN   | IDX_ANBOB_T1_VALUE   |  1362K|   246M|   232   (1)| 00:00:03 |    65 |    74 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RECOID"=TO_NUMBER(:RECOID))
   2 - access(ROWID=ROWID)
   4 - access("RECOID"=TO_NUMBER(:RECOID))
   6 - filter("REGION"=399)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL profile "PROFILE_03j7fz4xa03f6" used for this statement

Note:
使用sql profile的no_index(tablename indexname)的方法没有成功,原因后面会说。在Twitter上请问了JL大师,建议使用no_index具体的scan方式试试

SQL> DECLARE
  2    SQL_FTEXT CLOB;
  3   BEGIN
  4   SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '03j7fz4xa03f6';
  5   
  6   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  7     SQL_TEXT => SQL_FTEXT,
  8     PROFILE => SQLPROF_ATTR('NO_INDEX_FFS(ANBOB_T1 "IDX_ANBOB_T1_VALUE")'),
  9     NAME => 'PROFILE_03j7fz4xa03f6',
 10     REPLACE => TRUE,
 11     FORCE_MATCH => TRUE
 12   );
 13   END;
 14  /

PL/SQL procedure successfully completed.

SQL> SELECT    /*anbob*/   ATTRID,              ATTRVALUE   FROM       anbob.ANBOB_T1
  2          WHERE       RECOID = :RECOID    AND       REGION = 399;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3978804789
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                      |  1362K|   246M|   197   (1)| 00:00:03 |       |       |
|*  1 |  VIEW                      | index$_join$_001     |  1362K|   246M|   197   (1)| 00:00:03 |       |       |
|*  2 |   HASH JOIN                |                      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|                      |  1362K|   246M|   183  (94)| 00:00:03 |    65 |    74 |
|*  4 |     INDEX RANGE SCAN       | IDX_ANBOB_T1         |  1362K|   246M|   183  (94)| 00:00:03 |    65 |    74 |
|   5 |    PARTITION RANGE ITERATOR|                      |  1362K|   246M|   232   (1)| 00:00:03 |    65 |    74 |
|*  6 |     INDEX FAST FULL SCAN   | IDX_ANBOB_T1_VALUE   |  1362K|   246M|   232   (1)| 00:00:03 |    65 |    74 |
-------------------------------------------------------------------------------------------------------------------

NOTE:
使用no_index_ffs(tablename indexname)的profile也未成功。又使用了no_index 指定字段的方式

SQL> DECLARE
  2    SQL_FTEXT CLOB;
  3   BEGIN
  4   SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '03j7fz4xa03f6';
  5   
  6   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  7     SQL_TEXT => SQL_FTEXT,
  8     PROFILE => SQLPROF_ATTR('NO_INDEX(ANBOB_T1 (ATTRID,ATTRVALUE,REGION))'),
  9     NAME => 'PROFILE_03j7fz4xa03f6',
 10     REPLACE => TRUE,
 11     FORCE_MATCH => TRUE
 12   );
 13   END;
 14  /

PL/SQL procedure successfully completed.

SQL> SELECT    /*anbob*/   ATTRID,              ATTRVALUE   FROM       anbob.ANBOB_T1
  2          WHERE       RECOID = :RECOID    AND       REGION = 399;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3978804789

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                      |  1362K|   246M|   197   (1)| 00:00:03 |       |       |
|*  1 |  VIEW                      | index$_join$_001     |  1362K|   246M|   197   (1)| 00:00:03 |       |       |
|*  2 |   HASH JOIN                |                      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|                      |  1362K|   246M|   183  (94)| 00:00:03 |    65 |    74 |
|*  4 |     INDEX RANGE SCAN       | IDX_ANBOB_T1         |  1362K|   246M|   183  (94)| 00:00:03 |    65 |    74 |
|   5 |    PARTITION RANGE ITERATOR|                      |  1362K|   246M|   232   (1)| 00:00:03 |    65 |    74 |
|*  6 |     INDEX FAST FULL SCAN   | IDX_ANBOB_T1_VALUE   |  1362K|   246M|   232   (1)| 00:00:03 |    65 |    74 |
-------------------------------------------------------------------------------------------------------------------

Note:
使用SQL PROFILE 使用no_index(tablename (column_name,…))的方法也没有成功,实际上这个方法也是可行的。 其实是被绕进去了,并不是no_index的问题,而是sql profile的hint的用法, 需要使用query block name. 这里先用sel$ 默认的尝试

SQL> DECLARE
  2    SQL_FTEXT CLOB;
  3   BEGIN
  4   SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '03j7fz4xa03f6';
  5   
  6   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  7     SQL_TEXT => SQL_FTEXT,
  8     PROFILE => SQLPROF_ATTR('NO_INDEX(@sel$1  ANBOB_T1@sel$1 "IDX_ANBOB_T1_VALUE")'),
  9     NAME => 'PROFILE_03j7fz4xa03f6',
 10     REPLACE => TRUE,
 11     FORCE_MATCH => TRUE
 12   );
 13   END;
 14  /
PL/SQL procedure successfully completed.

SQL> SELECT    /*anbob*/   ATTRID,              ATTRVALUE   FROM       anbob.ANBOB_T1
  2          WHERE       RECOID = :RECOID    AND       REGION = 399;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 491038779

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |  1362K|   246M|  3149   (1)| 00:00:38 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                 |  1362K|   246M|  3149   (1)| 00:00:38 |    65 |    74 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| ANBOB_T1        |  1362K|   246M|  3149   (1)| 00:00:38 |    65 |    74 |
|*  3 |    INDEX RANGE SCAN                | IDX_ANBOB_T1    |  7792K|       |    11   (0)| 00:00:01 |    65 |    74 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("REGION"=399)
   3 - access("RECOID"=TO_NUMBER(:RECOID))

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL profile "PROFILE_03j7fz4xa03f6" used for this statement

Note:
这次使用profile no_index(sel$ indexname)的方式成功禁掉了其中一个索引,间接禁用了index_join,上面失败的原因就在这里,no_index(tablealiasname indexname)是这次我们要使用的方法,因为开始表中没有使用别名,而且query block在的名字因为index_join的原因转换的更加特殊,开始忽略了query block的名字,为什么使用在SQL 中hint的方法可以呢?如果hint 写在sql中,SQL在解析阶段会识别到,ORACLE 优化器在hint有效的情况就可以实现hint的意图,如果使用sql profile的方式增加的hint,那是不属于SQL PARSE的一部分,而是SQL查询转换后,干扰QUERY BLOCK执行计划部分的行为,所以使用sql profile加hint的方式对于no_index应该是 no_index(query_block_name obj_alias_name@query_block_name index_name), 以前的blog中也写过可以从v$sql_plan找到query block name.
oracle系统生成的query block有一些规律,比如select通常会以sel$前头加数字序列,如上面的sel$1,下面是一些query blcok前缀列表:
Prefix Used For
CRI$ CREATE INDEX statements
DEL$ DELETE statements
INS$ INSERT statements
MISC$ Miscellaneous SQL statements like LOCK TABLE
MRG$ MERGE statements
SEL$ SELECT statements
SET$ Set operators like UNION and MINUS
UPD$ UPDATE statements

对于本次index_join 时的query block可能不是sel$1,有时oracle系统生成的block可能会是sel$跟8位的hash值,因为这是查询转换后的query block,有些是不可控的. 对于本案例如果加上query block name在sql profile中,
PROFILE => SQLPROF_ATTR(‘INDEX_RS_ASC(@”SEL$1″ “ANBOB_T1″@”SEL$1″ (“ANBOB_T1″.”RECOID”))’),PROFILE => SQLPROF_ATTR(‘NO_INDEX(@sel$1 ANBOB_T1@sel$1 (ATTRID,ATTRVALUE,REGION))’) 等方法都是可行的。

第二个方法,index_join 还有一个CBO的系统参数可以禁用该特性,下面是使用SQL PROFILE把该参数禁掉实现disable index_join

SQL> DECLARE
  2    SQL_FTEXT CLOB;
  3   BEGIN
  4   SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '03j7fz4xa03f6';
  5   
  6   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  7     SQL_TEXT => SQL_FTEXT,
  8     PROFILE => SQLPROF_ATTR(' opt_param(''_index_join_enabled'',''false'') '),
  9     NAME => 'PROFILE_03j7fz4xa03f6',
 10     REPLACE => TRUE,
 11     FORCE_MATCH => TRUE
 12   );
 13   END;
 14  /

PL/SQL procedure successfully completed.

SQL> SELECT    /*anbob*/   ATTRID,              ATTRVALUE   FROM       anbob.ANBOB_T1
  2          WHERE       RECOID = :RECOID    AND       REGION = 399;

no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 491038779

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |  1362K|   246M|  3149   (1)| 00:00:38 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                 |  1362K|   246M|  3149   (1)| 00:00:38 |    65 |    74 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| ANBOB_T1     |  1362K|   246M|  3149   (1)| 00:00:38 |    65 |    74 |
|*  3 |    INDEX RANGE SCAN                | IDX_ANBOB_T1 |  7792K|       |    11   (0)| 00:00:01 |    65 |    74 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("REGION"=399)
   3 - access("RECOID"=TO_NUMBER(:RECOID))

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL profile "PROFILE_03j7fz4xa03f6" used for this statement

第三个方法用set index stats修改统计信息更接近真实的方法也是可行的,有时因为分区过大,短时间可能无法收集全部分区,可以先收集一个分区然后复制到其它分区(数据分布相差不大)
比如复制月份的分区

SQL> DECLARE
  2     v_numrows   NUMBER := 16915333;
  3     v_numlblks   NUMBER := 129733;
  4     v_numdist   NUMBER := 9424;
  5     v_clstfct   NUMBER := 1376533;
  6     v_indlevel  NUMBER := 4;
  7     v_partend   DATE := DATE '2016-4-1';                              -- ??????
  8     v_partcur   DATE := DATE '2015-7-1';                              -- ??????
  9     v_sql       VARCHAR2 (100);
 10  BEGIN
 11     WHILE v_partcur < v_partend  12     LOOP  13        v_partcur := ADD_MONTHS (v_partcur, 1);  14    15        v_sql := 'PART_399_' || TO_CHAR (ADD_MONTHS (v_partcur, -1), 'yyyymm');  16    17        DBMS_STATS.set_index_STATS (ownname    => 'anbob',
 18                                    indname    => 'IDX_ANBOB_T1_VALUE',
 19                                    partname   => v_sql,
 20                                    numrows    => v_numrows,
 21                                    numlblks    => v_numlblks,
 22                                    numdist    => v_numdist,
 23                                                               clstfct    => v_clstfct,
 24                                                               indlevel   => v_indlevel,
 25                                                               no_invalidate=>false);
 26        DBMS_OUTPUT.put_line (v_sql);
 27     END LOOP;
 28  END;
 29  /

TIP:

In Oracle 10g the default for the NO_INVALIDATE parameter is now AUTO_INVALIDATE.
This means that Oracle will not immediately invalidate the cached cursors on gathering of fresh statistics, but wait for a period of time to elapse first.This period of time is controlled by the parameter _optimizer_invalidation_period which defaults to a value of 18000 (seconds) or 5 hours.

第四个方法,使用SQL PATCH的方法

SQL> begin
  2    DBMS_SQLDIAG.DROP_SQL_PATCH(name  => 'test_no_index_patch');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> DECLARE
  2    SQL_T varchar2(1000);
  3   BEGIN
  4   SELECT SQL_TEXT INTO SQL_T FROM V$SQLAREA WHERE SQL_ID = '03j7fz4xa03f6';   
  5       dbms_sqldiag_internal.i_create_patch(
  6                           sql_text => SQL_T,
  7                           hint_text => 'no_index(@"SEL$1" "ANBOB_T1"@"SEL$1" IDX_ANBOB_T1_VALUE)',
  8                           name  => 'test_no_index_patch');
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> SELECT    /*anbob*/   ATTRID,              ATTRVALUE   FROM       ANBOB.ANBOB_T1
  2          WHERE       RECOID = :RECOID    AND       REGION = 315;

no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 491038779

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |  1362K|   246M|  3149   (1)| 00:00:38 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                 |  1362K|   246M|  3149   (1)| 00:00:38 |    65 |    74 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| ANBOB_T1        |  1362K|   246M|  3149   (1)| 00:00:38 |    65 |    74 |
|*  3 |    INDEX RANGE SCAN                | IDX_ANBOB_T1    |  7792K|       |    11   (0)| 00:00:01 |    65 |    74 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("REGION"=399)
   3 - access("RECOID"=TO_NUMBER(:RECOID))

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL patch "test_no_index_patch" used for this statement

Note:
SQL PATCH 和SQL profile一样也可以在不修改SQL TEXT的情况下,增加HINT固定执行计划, SQL PATCH 也是包含在EE版本中,不需要购买额外的License.

 

打赏

对不起,这篇文章暂时关闭评论。