暂无图片
暂无图片
4
暂无图片
暂无图片
暂无图片
首页 / Oracle SQL patch---另外一种不调整SQL可以更改语句的执行计划的方法

Oracle SQL patch---另外一种不调整SQL可以更改语句的执行计划的方法

原创 听见风的声音 2025-09-03
468

1 SQL patch及适用场合

在数据库运维中，经常有需要对sql的hints进行操作，有时需要增加hint以优化语句的性能，也有的语句因为用了hint反而导致性能下降，这时就需要对语句已有的hint进行删除。生产环境中，对SQL语句的调整并不总是可行的，这时就需要在不更改SQL的情况下对它的hint进行操作，SQL patch就是一个可以选择的工具，sqlpatch对标准化的SQL语句生效（这意味这空格和非字面值的大小写不影响sqlpatch的选择），直接对语句的hint进行操作，这个工具在dbms_sqldiag包中，可以由sqldiag任务生成，也可以手动生成，手动生成在Oracle Database 12c Release 2之后成了公开特性，操作起来更加简易，之前的版本中，手动创建sqlpatch是内部特性，没有公开。

2 SQL patch的基本操作

2.1 CREATE_SQL_PATCH函数–手动创建SQL patch
DBMS_SQLDIAG.CREATE_SQL_PATCH (
    sql_text        IN   CLOB,
    hint_text       IN   CLOB,
    name            IN   VARCHAR2   := NULL,
    description     IN   VARCHAR2   := NULL,
    category        IN   VARCHAR2   := NULL,
    validate        IN   BOOLEAN    := TRUE)
RETURN VARCHAR2;

这个函数还有另一种形式，参数中sql_text可以替换为sql_id（参数类型是varchar2），返回SQL patch名。

2.2 ALTER_SQL_PATCH存储过程—更改SQL patch属性
DBMS_SQLDIAG.ALTER_SQL_PATCH (
   name            IN  VARCHAR2,
   attribute_name  IN  VARCHAR2,
   attribute_value IN  VARCHAR2);

这个存储过程可以更改SQL patch的下列属性

  • STATUS -> can be set to ENABLED or DISABLED
  • NAME -> can be reset to a valid name (must be a valid Oracle identifier and must be unique).
  • DESCRIPTION -> can be set to any string of size no more than 500
  • CATEGORY -> can be reset to a valid category name (must be valid Oracle identifier and must be unique when combined with normalized SQL text)
2.3 DROP_SQL_PATCH存储过程—删除SQL patch
DBMS_SQLDIAG.DROP_SQL_PATCH (
   name     IN  VARCHAR2,   ignore   IN  BOOLEAN := FALSE);

这个存储过程接受两个参数，name是要删除的SQL patch的名称，ignore参数指定是否忽略因对象不存在导致的错误。

3 取消SQL语句现有的hints

3.1 创建SQL patch
SELECT /*+ full(c) */ * FROM CUSTOMER_ORDERS c WHEREc.ORDER_DATE>sysdate-3
Plan hash value: 86929056
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |       |       |  1893 (100)|          |
|*  1 |  TABLE ACCESS FULL| CUSTOMER_ORDERS |   484 | 21780 |  1893   (2)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C"."ORDER_DATE">SYSDATE@!-3)

上面的语句使用了不恰当的hint导致了全表扫描，可以使用sqlpatch使语句中的hint失效，创建hint的语句如下

SQL> declare patch_name varchar2(30);
begin
patch_name := dbms_sqldiag.create_sql_patch(
     sql_id=>'2j68tj85209wp',
     hint_text=>' IGNORE_OPTIM_EMBEDDED_HINTS');
end;  2    3    4    5    6
  7  /

PL/SQL procedure successfully completed.
3.2 检查SQL patch是否生效

这是再看这条语句的执行计划

SQL_ID  2j68tj85209wp, child number 0
-------------------------------------
SELECT /*+ full(c) */ * FROM CUSTOMER_ORDERS c WHERE
c.ORDER_DATE>sysdate-3
Plan hash value: 1825996753
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |       |       |   462 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_ORDERS     |   484 | 21780 |   462   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_CUST_ORDER_DATE |   484 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."ORDER_DATE">SYSDATE@!-3)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$1 / "C"@"SEL$1"
         U -  full(c) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
Note
-----
   - SQL patch "SYS_SQLPTCH_019904398ed20000" used for this statement

创建的SQL patch也也可以用于标准化后相同的其它SQL语句

SQL_ID  0hfaz2uztd3w5, child number 0
-------------------------------------
SELECT /*+ full(c) */ * from customer_orders c WHERE
c.ORDER_DATE>sysdate-3

Plan hash value: 1825996753

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |       |       |   462 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_ORDERS     |   484 | 21780 |   462   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_CUST_ORDER_DATE |   484 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."ORDER_DATE">SYSDATE@!-3)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
   1 -  SEL$1 / "C"@"SEL$1"
         U -  full(c) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
Note
-----
   - SQL patch "SYS_SQLPTCH_019904398ed20000" used for this statement

这条语句和上一条语句的sql id并不相同，原因是它们有几处大小写不同，但是创建的SQL patch同样可以用于这条语句，两条语句的执行计划hash值也相同。

3.3 查看创建的SQL patch

可以从DBA_SQL_PATCHES视图中查询到刚才创建的SQL patch

NAME                             SQL_TEXT                                                         STATUS
-------------------------------- ---------------------------------------------------------------- --------
SYS_SQLPTCH_019904398ed20000     SELECT /*+ full(c) */ * FROM CUSTOMER_ORDERS c WHERE c.ORDER_DAT ENABLED
                                 E>sysdate-3

虽然在创建SQL patch中使用的参数是sql id，这个视图保存的信息却是sql_text,这个文本是起初的sql语句，而不是标准化之后的文本。

4 为SQL语句添加hint

4.1 获得系统产生的查询块（query block）名称

在为语句添加hint时，往往需要获得hint要作用的查询块名称，这个名称可以在显示语句的执行计划时，用格式alias来获得：

SQL> select * from table(dbms_xplan.display_cursor('d1v2wt8n1tfrd',FORMAT=>'basic +alias'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM test.CUSTOMER_ORDERS c WHERE c.CUSTOMER_ID = 492
Plan hash value: 86929056
---------------------------------------------
| Id  | Operation         | Name            |
---------------------------------------------
|   0 | SELECT STATEMENT  |                 |
|   1 |  TABLE ACCESS FULL| CUSTOMER_ORDERS |
---------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "C"@"SEL$1"

上面的语句只有一个查询块，SEL是它的操作，这里是select，还有其它的操作，如CRI,CREATE INDEX statements,DEL$ DELETE statements,INS$ INSERT statements,后面的数字是操作的id，按照SQL书写的顺序从左向右依次递增。如果优化器对语句进行了转换，情况又有所不同，例如下面的语句

SQL> select * from table(dbms_xplan.display_cursor('g1mu28k4n5wmt',FORMAT=>'basic +alias'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM CUSTOMER_ORDERS c  WHERE c.CUSTOMER_ID IN (SELECT
customer_id FROM CUSTOMER_ADDRESSES c2 WHERE city='Tokyo') and
c.ORDER_DATE > sysdate -5

Plan hash value: 2850931541
--------------------------------------------------------------------
| Id  | Operation                            | Name                |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |
|   1 |  HASH JOIN RIGHT SEMI                |                     |
|   2 |   TABLE ACCESS FULL                  | CUSTOMER_ADDRESSES  |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_ORDERS     |
|   4 |    INDEX RANGE SCAN                  | IDX_CUST_ORDER_DATE |
--------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / "C2"@"SEL$2"
   3 - SEL$5DA710D3 / "C"@"SEL$1"
   4 - SEL$5DA710D3 / "C"@"SEL$1"

Oracle 优化器对语句进行了转换，这个查询转换也产生了一个查询块，这个查询块在解析的SQL语句中并不存在，因此不能像语句中的查询块那样命名，查询优化器为它产生了一个8位的hash值，如上图中的5DA710D3。在hints中，这个查询块名称和SQL语句中的查询块名称（如SEL$2,SEL$1)都可以用，建议使用SQL语句中原有的查询快名称(SEL$1,SEL$2)，因为如果转换不发生或者发生的转换不同，这个产生的查询块的名称就不存在了。

4.2 给语句添加hint
SQL> declare patch_name varchar2(30);
begin
patch_name := dbms_sqldiag.create_sql_patch(
     sql_id=>'g1mu28k4n5wmt',
     hint_text=>' FULL(@"SEL$1" "C"@"SEL$1")');
end;  2    3    4    5    6
  7  /

PL/SQL procedure successfully completed.

执行语句后再查看语句的执行计划如下

SQL> select * from table(dbms_xplan.display_cursor('g1mu28k4n5wmt'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g1mu28k4n5wmt, child number 0
-------------------------------------
SELECT * FROM CUSTOMER_ORDERS c  WHERE c.CUSTOMER_ID IN (SELECT
customer_id FROM CUSTOMER_ADDRESSES c2 WHERE city='Tokyo') and
c.ORDER_DATE > sysdate -5

Plan hash value: 3000344517

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |       |       |  1907 (100)|          |
|*  1 |  HASH JOIN RIGHT SEMI|                    |   471 | 26847 |  1907   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | CUSTOMER_ADDRESSES |   447 |  5364 |    14   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL  | CUSTOMER_ORDERS    |   484 | 21780 |  1893   (2)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - access("C"."CUSTOMER_ID"="CUSTOMER_ID")
   2 - filter("CITY"='Tokyo')
   3 - filter("C"."ORDER_DATE">SYSDATE@!-5)

Note
-----
   - SQL patch "SYS_SQLPTCH_01990d591e790000" used for this statement

SQL patch生效，CUSTOMER_ORDERS的访问方式变为全表扫描。

4.3 直接替换原来的hint

针对第三节的SQL语句，创建SQL patch，将原来的全表扫描替换为索引扫描

SQL> declare patch_name varchar2(30);
begin
patch_name := dbms_sqldiag.create_sql_patch(
     sql_id=>'892xgtkd5jx55',
     hint_text=>'INDEX(@"SEL$1" "C"@"SEL$1")');
end;  2    3    4    5    6
  7  /

PL/SQL procedure successfully completed.

语句的执行计划变为

SQL>  select * from table(dbms_xplan.display_cursor('892xgtkd5jx55'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  892xgtkd5jx55, child number 0
-------------------------------------
SELECT /*+ full(c) */ * FROM CUSTOMER_ORDERS c WHERE
c.ORDER_DATE>sysdate-3

Plan hash value: 1825996753

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |       |       |   462 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_ORDERS     |   484 | 21780 |   462   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_CUST_ORDER_DATE |   484 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   2 - access("C"."ORDER_DATE">SYSDATE@!-3)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$1 / "C"@"SEL$1"
         U -  full(c) / hint overridden by another in parent query block

Note
-----
   - SQL patch "SYS_SQLPTCH_01990d6992dd0001" used for this statement

对CUSTOMER_ORDERS表的访问方式变为索引范围扫描，hint报告里显示使用了SQL patch，full©未使用，原因时被父查询块里的其它hint覆盖。如果hint作用的查询块不同，则SQL patch的hint同语句原来的hint一起作用，这里不再演示。

5 小结

从database 12.2开始，SQL patch已经变成公开特性，在Oracle 官网里可以查到，这为DBA提供了另一种调整语句hint的方法，SQL patch里hint的优先级高于语句里hint的优先级。

墨力计划数据库实操oraclesql优化
最后修改时间：2025-09-04 11:01:00
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

墨天轮「实操看我的」征文-数据库实操优质文章合辑（共69篇）
本合辑汇总了「实操看我的」数据库主题征文活动中“最佳实操奖” 文章及部分高阅读量合格文章，欢迎参考~

评论