首页 » ORACLE 9i-23c » 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.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。