[20241012]cursor_sharing=force與函式索引.txt

[20241012]cursor_sharing=force與函式索引.txt

--//今天聽了一個課程，提到使用cursor_sharing=force解決sql語句使用文字變數問題以及侷限性，實際上一些侷限性在新的oracle版
--//本已經不存在， 突然想起今年3月份的事情，使用cursor_sharing_exact給sql語句打補丁5條，僅僅1條有效(11g環境)。
--//參考連結：[20240327]使用cursor_sharing_exact與給sql打補丁問題5.txt
--//也就是在11g下可以使用提示cursor_sharing_exact打補丁解決時，我遇到了問題，我記憶裡使用12.2 測試過，今天在21c下重複測
--//試.
--//關於這部分內容可以參考連結：https://hourim.wordpress.com/2020/10/24/function-based-indexes-and-cursor-sharing/

1.環境：
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.

2.測試建立：
SCOTT@book01p> create table empx as select * from emp;
Table created.

SCOTT@book01p> create index if_empx_hiredate on empx(to_char(hiredate,'yyyymmdd'));
Index created.
--//分析略。

3.測試：

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

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

SCOTT@book01p> @dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f567tudmra8p4, child number 0
-------------------------------------
select * from empx where to_char(hiredate,:"SYS_B_0") = :"SYS_B_1"
Plan hash value: 976799893
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| 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$"=:SYS_B_1)
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
31 rows selected.
--//注意看到sql語句select * from empx where to_char(hiredate,:"SYS_B_0") = :"SYS_B_1"，它不像使用提示
--//cursor_sharing_exact打補丁，會產生大量的文字變數sql語句。

SCOTT@book01p> select ename from empx Where to_char(hiredate,'yyyymmdd') = '20220302';
no rows selected

SCOTT@book01p> @dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 776d1zq9amy2p, child number 0
-------------------------------------
select ename from empx Where to_char(hiredate,:"SYS_B_0") = :"SYS_B_1"
Plan hash value: 976799893
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPX | 1 | 15 | 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$"=:SYS_B_1)
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
31 rows selected.

--//謂詞條件換成to_char(hiredate,'yyyy') = '2022'。
SCOTT@book01p> select ename from empx Where to_char(hiredate,'yyyy') = '2022';
no rows selected

SCOTT@book01p> @dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 776d1zq9amy2p, child number 1
-------------------------------------
select ename 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 | 14 | 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"),:SYS_B_0)=:SYS_B_1)
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
29 rows selected.
--//函式索引不支援，選擇全表掃描。產生新的子游標。

SCOTT@book01p> @ gunshare 776d1zq9amy2p
--- host vim /tmp/unshare.tmp
--- host cat /tmp/unshare.tmp
REASON_NOT_SHARED CURSORS SQL_IDS
----------------------------- ---------- ----------
HASH_MATCH_FAILED 1 1

4.簡單總結：
--//至少目前的版本解決cursor_sharing=force一些侷限性，至於複雜的sql語句是否有效，我沒有測試。
--//我看了以前的測試11g下使用cursor_sharing_exact給sql語句打補丁還是存在許多問題。