[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交换执行计划不行。