[20201126]使用cursor_sharing_exact与给sql打补丁2.txt

--//以前我记忆里给sql语句打补丁，好像在11g下打上cursor_sharing_exact提示无效的，看链接
--//https://hourim.wordpress.com/2020/10/24/function-based-indexes-and-cursor-sharing/
--//感觉我视乎做错一些步骤，自己重复测试：

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(dept) */ * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
3262421396 g0qybdz1796cn            0  c2749994

declare
   v_sql CLOB;
begin
   select distinct sql_text into v_sql from v$sql where sql_id='&sql_id';
   sys.dbms_sqldiag_internal.i_create_patch(
      sql_text  => v_sql,
      hint_text => 'cursor_sharing_exact IGNORE_OPTIM_EMBEDDED_HINTS)',
      name      => 'user_extents_patch');
end;
/
--//输入sql_id=g0qybdz1796cn.

SYS@book>  select name, status, created, sql_text from dba_sql_patches where name='user_extents_patch';
NAME               STATUS   CREATED                    SQL_TEXT
------------------ -------- -------------------------- ----------------------------------------------------
user_extents_patch ENABLED  2020-11-26 11:14:51.000000 select /*+ full(dept) */ * from dept where deptno=10

SYS@book> alter system flush shared_pool;
System altered.

SYS@book> alter system flush shared_pool;
System altered.

--//退出会话重新登录：
SCOTT@book> alter session set cursor_sharing=force;

Session altered.

SCOTT@book> select /*+ full(dept) */ * from dept where deptno=10;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  73trg5tn9pzqf, child number 0
-------------------------------------
select /*+ full(dept) */ * from dept where deptno=:"SYS_B_0"
Plan hash value: 3383998547
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEPT |      1 |    20 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DEPT@SEL$1

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

   1 - filter("DEPTNO"=:SYS_B_0)

Note
-----
   - 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

--//可以发现我这样操作不行，实际上应该使用sql_id=73trg5tn9pzqf来打补丁，我以前这样做是错误的。

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

SYS@book> select name, status, created, sql_text from dba_sql_patches where name='user_extents_patch';
no rows selected

declare
   v_sql CLOB;
begin
   select distinct sql_text into v_sql from v$sql where sql_id='&sql_id';
   sys.dbms_sqldiag_internal.i_create_patch(
      sql_text  => v_sql,
      hint_text => 'cursor_sharing_exact IGNORE_OPTIM_EMBEDDED_HINTS)',
      name      => 'user_extents_patch');
end;
/
--//注意输入sql_id=73trg5tn9pzqf

SYS@book> select name, status, created, sql_text from dba_sql_patches where name='user_extents_patch'
  2  @ prxx
==============================
NAME                          : user_extents_patch
STATUS                        : ENABLED
CREATED                       : 2020-11-26 11:17:05.000000
SQL_TEXT                      : select /*+ full(dept) */ * from dept where deptno=:"SYS_B_0"
PL/SQL procedure successfully completed.

--//退出会话重新登录：
SYS@book> alter system flush shared_pool;
System altered.

SYS@book> alter system flush shared_pool;
System altered.

SCOTT@book> alter session set cursor_sharing=force;
Session altered.

SCOTT@book> select /*+ full(dept) */ * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  g0qybdz1796cn, child number 0
-------------------------------------
select /*+ full(dept) */ * from dept where deptno=10
Plan hash value: 2852011669
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT@SEL$1
   2 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=10)
Note
-----
   - SQL patch "user_extents_patch" 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
32 rows selected.
--//注意看下执行计划sql_id=g0qybdz1796cn.对于的是select /*+ full(dept) */ * from dept where deptno=10。
--//语句做一些改动。
SCOTT@book> Select /*+ full(dept) */ * from dept where deptno= 40;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
--//注意我写的S是大写，后面的参数带入40. 参数40前我还加了一个空格。

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  943r9wd5qa6hg, child number 0
-------------------------------------
Select /*+ full(dept) */ * from dept where deptno= 40
Plan hash value: 2852011669
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT@SEL$1
   2 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=40)
Note
-----
   - SQL patch "user_extents_patch" 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
32 rows selected.
--//一样生效。看来以前的学习不认真，犯了一个小错误。
--//有了这个就可以在使用 cursor_sharing=force的情况下，在一些谓词条件to_char的情况下实现使用函数索引。
--//当然缺点就是无法共享相同光标了，消耗大量共享池资源，每次语句不同可能都需要硬解析。
--//有机会再测试看看。顺便看看解析的情况：


--//收尾：
SYS@book> exec sys.dbms_sqldiag.drop_sql_patch('user_extents_patch');
PL/SQL procedure successfully completed.