前面的方法我们还原了问题现象，因为CBO使用错误的统计信息，使用了INDEX_JOIN的方式， 现在尝试如何快速的解决该问题，也就是禁用INDEX_JOIN或使用我们指定的执行计划。
下面会使用以下几种方法来修正执行计划:
sql profile + hint
sql profile + parameter
set index part stats
sql patch + hint
数据存储在 SQLOBJ 开头的internal table, 如SYS.SQLOBJ$, SYS.SQLOBJ$PLAN, SYS.SQLOBJ$DATA, and SYS.SQLOBJ$AUXDATA.
SYS.SQLOBJ$ stores SQL Patch, SQL Profile and SQL Plan Baseline Data for a specific sql statement
下面举个例子
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
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
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))’) 等方法都是可行的。
第二个方法SQL PROFILE把该参数禁掉
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);
v_sql := 'PART_399_' || TO_CHAR (ADD_MONTHS (v_partcur, -1), 'yyyymm');
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.
SQL PATCH介绍
SQL 补丁是使用 DBMS_SQLDIAG 包创建的。它们在优化器级别应用提示，影响执行计划的生成方式。与 SQL PROFILE 不同，SQL PROFILE 调整优化器的估计值，而 SQL PATCH 则明确修改优化器的决策过程，但不像 SQL baseline 那样直接强制执行计划。
当有问题的SQL语句需要特定的优化器提示以获得更好的性能时，或者当处理无法修改SQL代码的第三方应用程序时，SQL Patch是一种方法。
通过SQL补丁添加提示从Oracle Database 12c Release 2开始可用，使用DBMS_SQLDIAG.CREATE_SQL_PATCH。也可以在11g中使用一个未记录的名为DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH的过程来完成。
sql patch创建
-- The DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH actually requires the SQL Text (as a CLOB) instead of just
-- the SQL_ID, in 12.1 and earlier this script fetches the SQL Text from v$sql (the cursor needs
-- to be in library cache). Similarly the DBMS_SQLDIAG.CREATE_PATCH(sql_id=>...) needs the cursor to be
-- in library cache in order to find the corresponding SQL text.
SET SERVEROUT ON SIZE 1000000
DECLARE
v_sql_text CLOB;
ret VARCHAR2(100);
BEGIN
-- rownum = 1 because there may be multiple children with this SQL_ID
DBMS_OUTPUT.PUT_LINE(q'[Looking up SQL_ID &1]');
SELECT sql_fulltext INTO v_sql_text FROM v$sql WHERE sql_id = '&1' AND rownum = 1;
DBMS_OUTPUT.PUT_LINE('Found: '||SUBSTR(v_sql_text,1,80)||'...');
-- TODO: should use PL/SQL conditional compilation here
-- The leading space in hint_text is intentional.
-- 12.2+
ret := DBMS_SQLDIAG.CREATE_SQL_PATCH(sql_id=>'&1', hint_text=>q'[ &2]', name=>'SQL_PATCH_&1');
-- 11g and 12.1
--DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(sql_text=>v_sql_text, hint_text=>q'[ &2]', name=>'SQL_PATCH_&1');
DBMS_OUTPUT.PUT_LINE(q'[SQL Patch Name = SQL_PATCH_&1]');
END;
/
-- or --
SQL>DECLARE
patch_name VARCHAR2 (4000);
BEGIN
patch_name :=
DBMS_SQLDIAG.create_sql_patch (
sql_id => 'cafx9pnjmr7v3',
hint_text => q'{index_rs(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))}',
name => 'SQL_PATCH_EMPID_RANGESCAN',
validate => true
);
END;
/
SQL> SELECT name,
signature,
TO_CHAR (Created, 'DD-MON-YY') as Created_Date,
status
FROM dba_sql_patches;
NAME SIGNATURE CREATED_DATE STATUS
-------- --------------- ------------ --------
SQL_PATCH_EMPID_RANGESCAN 5519277933637383967 25-MAR-25 ENABLED
SQL Patch存储在哪
和sql profile一样在sys.sqlobj$data
select
obj.name,
dat.comp_data,
dat.category,
obj.last_executed,
txt.sql_handle,
sql.inuse_features,
sql.flags,
txt.sql_text
from
sys.sql$ sql join
sys.sql$text txt on sql.signature = txt.signature join
sys.sqlobj$ obj on sql.signature = obj.signature join
sys.sqlobj$data dat on obj.signature = dat.signature and
obj.category = dat.category
where
-- d.signature = dbms_sqltune.sqltext_to_signature(q'[select * from tq84_A t where col_2 like 'ABC%']')
obj.name = 'TQ84_A_NO_INDEX'
;
-- or --
SELECT trim(extractValue(value(h),'.')) AS hint
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
WHERE so.name = 'TEST'
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id