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