[20220329]19c sql語句打補丁.txt

[20220329]19c sql語句打補丁.txt

--//在19c最佳化sql語句使用sql profile模式交換執行計劃時，出現無法穩定執行計劃的情況。
--//嘗試打補丁的方式，19c以上版本與以前11g的命令有一點點不同，做一個簡單記錄。

1.環境：
> @pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

1.打補丁：
--//首先確定sql_id,執行如下命令：
DECLARE
   v_sql        CLOB;
   patch_name   VARCHAR2 (100);
BEGIN
   SELECT SQL_FULLTEXT
     INTO v_sql
     FROM v$sql
    WHERE sql_id = '&sql_id' AND ROWNUM = 1;

   patch_name :=
      sys.DBMS_SQLDIAG.create_sql_patch
      (
         sql_text    => v_sql
        ,hint_text   => 'USE_CONCAT(@"SEL$2BFA4EE4" 8 OR_PREDICATES(5)))'
        ,name        => 'user_extents_patch &sql_id'
      );
END;
/

--//說明：我以前寫的指令碼使用SQL_TEXT，實際上其型別VARCHAR2(1000),不是clob，做測試可以，生產系統語句一般都很長不行。
--//以前11g使用sys.dbms_sqldiag_internal.i_create_patch，這個是一個儲存過程，呼叫就ok了，無法定義變數patch_name。

YS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ desc sys.dbms_sqldiag_internal
PROCEDURE I_CREATE_HINTSET
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 HINT_TEXT                      VARCHAR2                IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT
PROCEDURE I_CREATE_PATCH
~~~~~~~~~~~~~~~~~~~~~~~~
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 HINT_TEXT                      VARCHAR2                IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT
FUNCTION I_GENERATE_SS_IMPORT RETURNS VARCHAR2
FUNCTION I_GET_DBVERSION RETURNS VARCHAR2
FUNCTION I_GET_INCIDENTID RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             VARCHAR2                IN
PROCEDURE I_INCIDENTID_2_SQL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 INCIDENT_ID                    VARCHAR2                IN
 SQL_STMT                       SQLSET_ROW              OUT
 PROBLEM_TYPE                   NUMBER                  OUT
 ERR_CODE                       BINARY_INTEGER          OUT
 ERR_MESG                       VARCHAR2                OUT

--//而19c使用sys.dbms_sqldiag包，CREATE_SQL_PATCH是一個函式，需要一個變數接收返回。
> @ desc sys.dbms_sqldiag
...
FUNCTION CREATE_SQL_PATCH RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 HINT_TEXT                      CLOB                    IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT


2.檢視打補丁資訊：
$ cat spext.sql
/* Formatted on 2015/4/10 17:03:49 (QP5 v5.252.13127.32867) */
column hint format a200
column name format a30
SELECT EXTRACTVALUE (VALUE (h), '.') AS hint,so.name
  FROM SYS.sqlobj$data od
      ,SYS.sqlobj$ so
      ,TABLE
       (
          XMLSEQUENCE
          (
             EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint')
          )
       ) h
 WHERE    ( so.NAME in ( 'profile &&1', 'tuning &&1','switch tuning &&1') or lower(so.name) like lower('%&&1%'))
       AND so.signature = od.signature
       AND so.CATEGORY = od.CATEGORY
       AND so.obj_type = od.obj_type
       AND so.plan_id = od.plan_id;


> @ spext gk5ttf0jpf88k
HINT                                             NAME
------------------------------------------------ ------------------------------
USE_CONCAT(@"SEL$2BFA4EE4" 8 OR_PREDICATES(5)))  user_extents_patch gk5ttf0jpf88k

3.刪除sql補丁執行如下：
exec sys.dbms_sqldiag.drop_sql_patch('user_extents_patch &sql_id');

4.檢查是否生效：
@ dpc gk5ttf0jpf88k outline ''

--//始終搞不明白為什麼sql profile交換執行計劃不行。