[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语句打补丁还是存在许多问题。