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