Oracle SQL patch---另外一種不調整SQL可以更改語句的執行計劃的方法

原創聽見風的聲音 2025-09-03

845

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的優先級。

墨力計劃數據庫實操 oracle sql優化

最后修改時間：2025-09-04 11:01:00

「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」

關注作者

文章被以下合輯收錄

墨天輪「實操看我的」征文-數據庫實操優質文章合輯（共69篇）

本合輯匯總了「實操看我的」數據庫主題征文活動中“最佳實操獎” 文章及部分高閱讀量合格文章，歡迎參考~

特色一级强游戏,海奥华预言免费阅读,51漫画兑换码,美女裸体无遮挡永久免费观看网站,lubuntu线路检测入口