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

[20201126]使用cursor_sharing_exact與給sql打補丁3.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> create table empx as select * from emp;
Table created.

SCOTT@book> create index if_empx_hiredate on empx(to_char(hiredate,'yyyymmdd'));
Index created.

--//分析略。

SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201126';
no rows selected

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  du47xmcwj8j83, child number 0
-------------------------------------
select * from empx where to_char(hiredate,'yyyymmdd')='20201126'
Plan hash value: 3554333430
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |        |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPX             |      1 |    93 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IF_EMPX_HIREDATE |      1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMPX@SEL$1
   2 - SEL$1 / EMPX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPX"."SYS_NC00009$"='20201126')

--//發現可以使用索引，但是如果設定cursor_sharing=force，因為裡面引數被替換，會導致建立的函式索引無法使用。
SCOTT@book> alter session set cursor_sharing=force;
Session altered.

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

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

SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201126';
no rows selected

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ff2sbvc2b3pyb, child number 0
-------------------------------------
select * from empx where to_char(hiredate,:"SYS_B_0")=:"SYS_B_1"
Plan hash value: 722738080
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPX |      1 |    87 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMPX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - :SYS_B_0 (VARCHAR2(30), CSID=852): 'yyyymmdd'
   2 - :SYS_B_1 (VARCHAR2(30), CSID=852): '20201126'
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),:SYS_B_0)=:SYS_B_1)

2.給sql語句打補丁看看。
SYS@book> exec sys.dbms_sqldiag.drop_sql_patch('user_extents_patch');
PL/SQL procedure successfully completed.

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)',
      name      => 'user_extents_patch');
end;
/
--//輸入sql_id=ff2sbvc2b3pyb

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 15:31:55.000000
SQL_TEXT                      : select * from empx where to_char(hiredate,:"SYS_B_0")=:"SYS_B_1"

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 * from empx where to_char(hiredate,'yyyymmdd')='20201126';
no rows selected

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  du47xmcwj8j83, child number 0
-------------------------------------
select * from empx where to_char(hiredate,'yyyymmdd')='20201126'
Plan hash value: 3554333430
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |        |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPX             |      1 |    47 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IF_EMPX_HIREDATE |      1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMPX@SEL$1
   2 - SEL$1 / EMPX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPX"."SYS_NC00009$"='20201126')

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.
--//OK,函式索引可以使用。修改語句看看：

SCOTT@book> select * from empx where to_char(hiredate,'yyyy')='2020';
no rows selected

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9sqnzyt0mjkzs, child number 0
-------------------------------------
select * from empx where to_char(hiredate,'yyyy')='2020'
Plan hash value: 722738080
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPX |      1 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMPX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'yyyy')='2020')
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
30 rows selected.
--//一樣ok，但是注意函式索引不滿足需求，走的是全表掃描。
--//有了這樣的方式就可以解決生產系統遇到的垃圾問題，像這樣的垃圾語句連結：
--// http://blog.itpub.net/267265/viewspace-1771727/
--//建議開發不要在寫這樣垃圾的拼接sql語句，豆腐渣豆腐渣.
--//當然缺點就是無法共享相同游標了，消耗大量共享池資源，每次可能都需要硬解析。

--//session 1:
SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201128';
no rows selected

--//session 2:
SYS@book> @ viewsessx parse 325
NAME                   STATISTIC#      VALUE        SID
---------------------- ---------- ---------- ----------
parse time cpu                622         34        325
parse time elapsed            623         35        325
parse count (total)           624       1007        325
parse count (hard)            625        224        325
parse count (failures)        626         12        325

--//session 1:
SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201128';
no rows selected

--//session 2:
SYS@book> @ viewsessx parse 325
NAME                   STATISTIC#      VALUE        SID
---------------------- ---------- ---------- ----------
parse time cpu                622         34        325
parse time elapsed            623         36        325
parse count (total)           624       1009        325
parse count (hard)            625        225        325
parse count (failures)        626         13        325

--//session 1:
SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201130';
no rows selected

--//session 2:
SYS@book> @ viewsessx parse 325
NAME                   STATISTIC#      VALUE        SID
---------------------- ---------- ---------- ----------
parse time cpu                622         35        325
parse time elapsed            623         37        325
parse count (total)           624       1011        325
parse count (hard)            625        227        325
parse count (failures)        626         14        325

--//注意看parse count (failures),parse count (hard)計數。如果前面執行過，parse count (hard)增加1。224->225.
--//如果從來沒有執行過，parse count (hard)增加2.225->227. parse count (failures)從增加1次。
--//收尾：
SYS@book> exec sys.dbms_sqldiag.drop_sql_patch('user_extents_patch');
PL/SQL procedure successfully completed.