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