[20201126]使用cursor_sharing_exact与给sql打补丁3.txt

[20201126]使用cursor_sharing_exact与给sql打补丁3.txt --//以前我记忆里给sql语句打补丁，好像在11g下打上cursor_sharing_exact提示无效的，看链接 --//https://hourim.wordpress.com/2020/10/24/function-based-indexes-and-cursor-sharing/ --//感觉我视乎做错一些步骤，自己重复测试： 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 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. --//分析略。 SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201126'; no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  du47xmcwj8j83, child number 0 ------------------------------------- select * from empx where to_char(hiredate,'yyyymmdd')='20201126' Plan hash value: 3554333430 ------------------------------------------------------------------------------------------------- | Id  | Operation                   | Name             | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                  |        |       |     1 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| EMPX             |      1 |    93 |     1   (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$"='20201126') --//发现可以使用索引，但是如果设置cursor_sharing=force，因为里面参数被替换，会导致建立的函数索引无法使用。 SCOTT@book> alter session set cursor_sharing=force; Session altered. SCOTT@book> alter system flush shared_pool; System altered. SCOTT@book> alter system flush shared_pool; System altered. SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201126'; no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  ff2sbvc2b3pyb, child number 0 ------------------------------------- select * 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 |    87 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / EMPX@SEL$1 Peeked Binds (identified by position): --------------------------------------    1 - :SYS_B_0 (VARCHAR2(30), CSID=852): 'yyyymmdd'    2 - :SYS_B_1 (VARCHAR2(30), CSID=852): '20201126' Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),:SYS_B_0)=:SYS_B_1) 2.给sql语句打补丁看看。 SYS@book> exec sys.dbms_sqldiag.drop_sql_patch('user_extents_patch'); PL/SQL procedure successfully completed. declare    v_sql CLOB; begin    select distinct sql_text into v_sql from v$sql where sql_id='&sql_id';    sys.dbms_sqldiag_internal.i_create_patch(       sql_text  => v_sql,       hint_text => 'cursor_sharing_exact)',       name      => 'user_extents_patch'); end; / --//输入sql_id=ff2sbvc2b3pyb SYS@book> select name, status, created, sql_text from dba_sql_patches where name='user_extents_patch'   2  @ prxx ============================== NAME                          : user_extents_patch STATUS                        : ENABLED CREATED                       : 2020-11-26 15:31:55.000000 SQL_TEXT                      : select * from empx where to_char(hiredate,:"SYS_B_0")=:"SYS_B_1" PL/SQL procedure successfully completed. SYS@book> alter system flush shared_pool; System altered. SYS@book> alter system flush shared_pool; System altered. --//退出会话重新登录： SCOTT@book> alter session set cursor_sharing=force; Session altered. SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201126'; no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  du47xmcwj8j83, child number 0 ------------------------------------- select * from empx where to_char(hiredate,'yyyymmdd')='20201126' 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 |    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$"='20201126') Note -----    - SQL patch "user_extents_patch" 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. --//OK,函数索引可以使用。修改语句看看： SCOTT@book> select * from empx where to_char(hiredate,'yyyy')='2020'; no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  9sqnzyt0mjkzs, child number 0 ------------------------------------- select * from empx where to_char(hiredate,'yyyy')='2020' 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 |    39 |     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"),'yyyy')='2020') Note -----    - SQL patch "user_extents_patch" 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. --//一样ok，但是注意函数索引不满足需求，走的是全表扫描。 --//有了这样的方式就可以解决生产系统遇到的垃圾问题，像这样的垃圾语句链接： --// http://blog.itpub.net/267265/viewspace-1771727/ --//建议开发不要在写这样垃圾的拼接sql语句，豆腐渣豆腐渣. --//当然缺点就是无法共享相同光标了，消耗大量共享池资源，每次可能都需要硬解析。 --//session 1: SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201128'; no rows selected --//session 2: SYS@book> @ viewsessx parse 325 NAME                   STATISTIC#      VALUE        SID ---------------------- ---------- ---------- ---------- parse time cpu                622         34        325 parse time elapsed            623         35        325 parse count (total)           624       1007        325 parse count (hard)            625        224        325 parse count (failures)        626         12        325 --//session 1: SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201128'; no rows selected --//session 2: SYS@book> @ viewsessx parse 325 NAME                   STATISTIC#      VALUE        SID ---------------------- ---------- ---------- ---------- parse time cpu                622         34        325 parse time elapsed            623         36        325 parse count (total)           624       1009        325 parse count (hard)            625        225        325 parse count (failures)        626         13        325 --//session 1: SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201130'; no rows selected --//session 2: SYS@book> @ viewsessx parse 325 NAME                   STATISTIC#      VALUE        SID ---------------------- ---------- ---------- ---------- parse time cpu                622         35        325 parse time elapsed            623         37        325 parse count (total)           624       1011        325 parse count (hard)            625        227        325 parse count (failures)        626         14        325 --//注意看parse count (failures),parse count (hard)计数。如果前面执行过，parse count (hard)增加1。224->225. --//如果从来没有执行过，parse count (hard)增加2.225->227. parse count (failures)从增加1次。 --//收尾： SYS@book> exec sys.dbms_sqldiag.drop_sql_patch('user_extents_patch'); PL/SQL procedure successfully completed.