[20220330]編寫sql打補丁的指令碼.txt

[20220330]編寫sql打補丁的指令碼.txt

--//給sql語句打補丁適用包不同，必須執行不同的命令，我將兩者合在一起，寫一個指令碼。
$ cat sqlpatch.sql
prompt
prompt input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
prompt drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_&1');;
prompt display sql path message , run @spext &1
prompt

define noprint='noprint'
set term off
col tpt_version_old  &noprint new_value _tpt_version_old
col tpt_version_new  &noprint new_value _tpt_version_new
col tpt_noprint      &noprint new_value _tpt_noprint

WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 11 THEN '' ELSE '--' END tpt_version_old
      ,CASE WHEN v > 11  THEN '' ELSE '--' END tpt_version_new
  FROM version;

set term on

declare
   v_sql CLOB;
   patch_name   VARCHAR2 (100);
begin
   select sql_fulltext into v_sql from v$sql where sql_id='&1' and rownum=1;

&&_tpt_version_old   sys.dbms_sqldiag_internal.i_create_patch(
&&_tpt_version_old      sql_text  => v_sql,
&&_tpt_version_old      hint_text => '&2',
&&_tpt_version_old      name      => 'sqlpatch_&1');

&&_tpt_version_new   patch_name :=
&&_tpt_version_new       sys.DBMS_SQLDIAG.create_sql_patch
&&_tpt_version_new       (
&&_tpt_version_new          sql_text    => v_sql
&&_tpt_version_new         ,hint_text   => '&2'
&&_tpt_version_new         ,name        => 'sqlpatch_&1'
&&_tpt_version_new       );

end;
/

--//測試看看。
1.測試一：
SCOTT@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

SCOTT@book> select /*+ full(emp) */  * from emp where empno=111;
no rows selected

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
 297818799 8skxyzn8w0qpg            0      23215      3956160932  11c05aaf  2022-03-30 10:02:08    16777222

SYS@book>  @ sqlpatch 8skxyzn8w0qpg 'INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))'

input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_8skxyzn8w0qpg');
display sql path message , run @spext 8skxyzn8w0qpg


PL/SQL procedure successfully completed.
--//注：必須以使用者執行。

SCOTT@book> select /*+ full(emp) */  * from emp where empno=111;
no rows selected

SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8skxyzn8w0qpg, child number 0
-------------------------------------
select /*+ full(emp) */  * from emp where empno=111
Plan hash value: 2949544139
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |    39 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |       |     0   (0)|          |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1
   2 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=111)
Note
-----
   - SQL patch "sqlpatch_8skxyzn8w0qpg" used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

SYS@book>  @spext 8skxyzn8w0qpg
HINT                                                 NAME
---------------------------------------------------- ------------------------------
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) sqlpatch_8skxyzn8w0qpg

SYS@book>  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_8skxyzn8w0qpg');
PL/SQL procedure successfully completed.

2.測試二：
TTT@xxxx:1521/orcl> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 18.0.0.0.0
BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

TTT@xxxx:1521/orcl> select /*+ full(emp) */  * from emp where empno=111;
no rows selected

TTT@xxxx:1521/orcl> @ hash
HASH_VALUE SQL_ID                     CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- -------------------------- ------------ ---------- --------------- ---------- ------------------- -----------
 297818799 8skxyzn8w0qpg                         0      23215      3956160932  11c05aaf  2022-03-30 10:05:10    16777216

SYS@xxxx:1521/orcl> @ sqlpatch 8skxyzn8w0qpg 'INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))'
input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_8skxyzn8w0qpg');
display sql path message , run @spext 8skxyzn8w0qpg

PL/SQL procedure successfully completed.

SYS@xxxx:1521/orcl> @ spext 8skxyzn8w0qpg
HINT                                                 NAME
---------------------------------------------------- ------------------------------
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) sqlpatch_8skxyzn8w0qpg

TTT@xxxx:1521/orcl> select /*+ full(emp) */  * from emp where empno=111;

no rows selected

TTT@xxxx:1521/orcl> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8skxyzn8w0qpg, child number 0
-------------------------------------
select /*+ full(emp) */  * from emp where empno=111
Plan hash value: 2949544139
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |       |     0   (0)|          |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1
   2 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=111)
Note
-----
   - SQL patch "sqlpatch_8skxyzn8w0qpg" used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

SYS@xxxx:1521/orcl> exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_8skxyzn8w0qpg');
PL/SQL procedure successfully completed.

3.附上spext.sql指令碼：

$ 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;