[20240327]使用cursor_sharing_exact与给sql打补丁问题5.txt

[20240327]使用cursor_sharing_exact与给sql打补丁问题5.txt --//以前写的例子,https://blog.itpub.net/267265/viewspace-2737353/,如果没有使用绑定变量,当使用cursor_sharing_exact=force --//参数,选择打补丁的sql语句必须使用在cursor_sharing_exact=force下的sql_id. --//前几天遇到的问题,我也想打类似的sql patch,但是遇到一个问题,通过例子说明问题. 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.建立例子: SCOTT@book> create table empx as select * from emp; Table created. SCOTT@book> create index if_empx_hiredate on empx(to_char(hiredate,'yyyymmdd')); Index created. --//分析略。 3.建立sql patch. SCOTT@book> alter session set cursor_sharing=force; Session altered. SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd') = '20220302'; no rows selected SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 1735729828 f567tudmra8p4            0      74404       722738080  677522a4  2024-03-27 09:55:56    16777217 SCOTT@book> select ename from empx where to_char(hiredate,'yyyymmdd') = '20220302'; no rows selected SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 2157876896 gcf40ju09x5p0            0      38560       722738080  809e96a0  2024-03-27 09:56:41    16777216 --//记下2条sql语句的sql_id = f567tudmra8p4 , gcf40ju09x5p0 . SYS@book> @ sqlpatch f567tudmra8p4 cursor_sharing_exact input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12) drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_f567tudmra8p4'); display sql path message , run @spext f567tudmra8p4 PL/SQL procedure successfully completed. SYS@book> @ sqlpatch gcf40ju09x5p0 cursor_sharing_exact input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12) drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_gcf40ju09x5p0'); display sql path message , run @spext gcf40ju09x5p0 PL/SQL procedure successfully completed. SYS@book> @ spext f567tudmra8p4 SYS@book> @ pr ============================== HINT                          : cursor_sharing_exact NAME                          : sqlpatch_f567tudmra8p4 DESCRIPTION                   : LAST_MODIFIED                 : 2024-03-27 09:57:53.000000 PL/SQL procedure successfully completed. SYS@book> @ spext gcf40ju09x5p0 SYS@book> @ pr ============================== HINT                          : cursor_sharing_exact NAME                          : sqlpatch_gcf40ju09x5p0 DESCRIPTION                   : LAST_MODIFIED                 : 2024-03-27 09:58:02.000000 PL/SQL procedure successfully completed. SYS@book> select * from dba_sql_patches   2  @ pr ============================== NAME                          : sqlpatch_f567tudmra8p4 CATEGORY                      : DEFAULT SIGNATURE                     : 16840958163875944576 SQL_TEXT                      : select * from empx where to_char(hiredate,:"SYS_B_0") = :"SYS_B_1" CREATED                       : 2024-03-27 09:57:53.000000 LAST_MODIFIED                 : 2024-03-27 09:57:53.000000 DESCRIPTION                   : STATUS                        : ENABLED FORCE_MATCHING                : NO TASK_ID                       : TASK_EXEC_NAME                : TASK_OBJ_ID                   : TASK_FND_ID                   : TASK_REC_ID                   : ============================== NAME                          : sqlpatch_gcf40ju09x5p0 CATEGORY                      : DEFAULT SIGNATURE                     : 4176241062076114549 SQL_TEXT                      : select ename from empx where to_char(hiredate,:"SYS_B_0") = :"SYS_B_1" CREATED                       : 2024-03-27 09:58:02.000000 LAST_MODIFIED                 : 2024-03-27 09:58:02.000000 DESCRIPTION                   : STATUS                        : ENABLED FORCE_MATCHING                : NO TASK_ID                       : TASK_EXEC_NAME                : TASK_OBJ_ID                   : TASK_FND_ID                   : TASK_REC_ID                   : PL/SQL procedure successfully completed. 4.开始测试: --//为了避免各种干扰,我选择关闭数据库重启数据库来测试.过程略. SCOTT@book> alter session set cursor_sharing=force; Session altered. SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd') = '20220302'; no rows selected SCOTT@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  g8kxvufv836rw, child number 0 ------------------------------------- select * from empx where to_char(hiredate,'yyyymmdd') = '20220302' Plan hash value: 3554333430 ------------------------------------------------------------------------------------------------- | Id  | Operation                   | Name             | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                  |        |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| EMPX             |      1 |    44 |     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$"='20220302') Note -----    - SQL patch "sqlpatch_f567tudmra8p4" used for this statement    - 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 32 rows selected. --//可以发现这样的模式可以使用IF_EMPX_HIREDATE索引.你也可以写成如下,只要FORCE_MATCHING_SIGNATURE不变就ok了. Select * from empx where to_char(hiredate,'yyyymmdD') = '20220301'; Select * from empx where to_char(hiredate,'yyyymmdd') = '20220301'; Select * from empx where TO_CHAR(hiredate,'yyyymmdd') = '20220201'; --//当然如果写成如下肯定不会使用IF_EMPX_HIREDATE索引,虽然FORCE_MATCHING_SIGNATURE不变: Select * from empx where TO_CHAR(hiredate,'yyyy') = '2022'; SCOTT@book> select ename from empx where to_char(hiredate,'yyyymmdd') = '20220302'; no rows selected SCOTT@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  gcf40ju09x5p0, child number 0 ------------------------------------- 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  |      |        |       |     4 (100)|          | |*  1 |  TABLE ACCESS FULL| EMPX |      1 |    14 |     4   (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 -----    - SQL patch "sqlpatch_gcf40ju09x5p0" used for this statement    - 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 30 rows selected. --//问题来了,我仅仅将里面*换成ename,竟然不会使用IF_EMPX_HIREDATE索引,执行计划走的是全表扫描. --//也就是在11g下这种打补丁cursor_sharing_exact的方式完全没有可用性. 5.继续: SCOTT@book> alter session set cursor_sharing=force; Session altered. SCOTT@book> SELECT ENAME FROM EMPX WHERE to_char(hiredate,'yyyymmdd') = '20220301'; no rows selected SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 1181011318 f1xnshj369mbq            0      52598       722738080  4664cd76  2024-03-27 10:10:24    16777216 SYS@book> @sqlpatch f1xnshj369mbq cursor_sharing_exact input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12) drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_f1xnshj369mbq'); display sql path message , run @spext f1xnshj369mbq declare * ERROR at line 1: ORA-13830: SQL profile with category DEFAULT already exists for this SQL statement ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16180 ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 204 ORA-06512: at line 8 --//因为FORCE_MATCHING_SIGNATURE相同的已经存在,要删除才能加入. SYS@book> exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_gcf40ju09x5p0'); PL/SQL procedure successfully completed. SYS@book> @sqlpatch f1xnshj369mbq cursor_sharing_exact input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12) drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_f1xnshj369mbq'); display sql path message , run @spext f1xnshj369mbq PL/SQL procedure successfully completed. SYS@book> select name, status, created c30 , sql_text c80 from dba_sql_patches; NAME                                     STATUS   C30                            C80 ---------------------------------------- -------- ------------------------------ -------------------------------------------------------------------------------- sqlpatch_f1xnshj369mbq                   ENABLED  2024-03-27 10:10:52.000000     SELECT ENAME FROM EMPX WHERE to_char(hiredate,:"SYS_B_0") = :"SYS_B_1" sqlpatch_f567tudmra8p4                   ENABLED  2024-03-27 09:57:53.000000     select * from empx where to_char(hiredate,:"SYS_B_0") = :"SYS_B_1" --//重启尝试还是不行.换一句话将https://hourim.wordpress.com/2020/10/24/function-based-indexes-and-cursor-sharing/ --//的测试存在问题,应用局限性很窄,也不知道为什么出现上面遇到的情况. 6.总结: --//总之,使用cursor_sharing_exact给sql语句打补丁在11g下应该不可行,我不知道目前遇到的问题在那里. --//更加无法解析 select ename from empx where to_char(hiredate,'yyyymmdd') = '20220302';这样简单的语句都不行为什么.