[20201126]使用cursor_sharing_exact與給sql打補丁2.txt

[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.