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

[20201126]使用cursor_sharing_exact与给sql打补丁2.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> select /*+ full(dept) */ * from dept where deptno=10;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 3262421396 g0qybdz1796cn            0  c2749994 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 IGNORE_OPTIM_EMBEDDED_HINTS)',       name      => 'user_extents_patch'); end; / --//输入sql_id=g0qybdz1796cn. SYS@book>  select name, status, created, sql_text from dba_sql_patches where name='user_extents_patch'; NAME               STATUS   CREATED                    SQL_TEXT ------------------ -------- -------------------------- ---------------------------------------------------- user_extents_patch ENABLED  2020-11-26 11:14:51.000000 select /*+ full(dept) */ * from dept where deptno=10 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 /*+ full(dept) */ * from dept where deptno=10;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  73trg5tn9pzqf, child number 0 ------------------------------------- select /*+ full(dept) */ * from dept where deptno=:"SYS_B_0" Plan hash value: 3383998547 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          | |*  1 |  TABLE ACCESS FULL| DEPT |      1 |    20 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("DEPTNO"=:SYS_B_0) 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 --//可以发现我这样操作不行，实际上应该使用sql_id=73trg5tn9pzqf来打补丁，我以前这样做是错误的。 SYS@book> exec sys.dbms_sqldiag.drop_sql_patch('user_extents_patch'); PL/SQL procedure successfully completed. SYS@book> select name, status, created, sql_text from dba_sql_patches where name='user_extents_patch'; no rows selected 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 IGNORE_OPTIM_EMBEDDED_HINTS)',       name      => 'user_extents_patch'); end; / --//注意输入sql_id=73trg5tn9pzqf 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 11:17:05.000000 SQL_TEXT                      : select /*+ full(dept) */ * from dept where deptno=:"SYS_B_0" 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 /*+ full(dept) */ * from dept where deptno=10;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  g0qybdz1796cn, child number 0 ------------------------------------- select /*+ full(dept) */ * from dept where deptno=10 Plan hash value: 2852011669 ---------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |        |       |     1 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     1   (0)| 00:00:01 | |*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@SEL$1    2 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("DEPTNO"=10) 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. --//注意看下执行计划sql_id=g0qybdz1796cn.对于的是select /*+ full(dept) */ * from dept where deptno=10。 --//语句做一些改动。 SCOTT@book> Select /*+ full(dept) */ * from dept where deptno= 40;     DEPTNO DNAME          LOC ---------- -------------- -------------         40 OPERATIONS     BOSTON --//注意我写的S是大写，后面的参数带入40. 参数40前我还加了一个空格。 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  943r9wd5qa6hg, child number 0 ------------------------------------- Select /*+ full(dept) */ * from dept where deptno= 40 Plan hash value: 2852011669 ---------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |        |       |     1 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     1   (0)| 00:00:01 | |*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@SEL$1    2 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("DEPTNO"=40) 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. --//一样生效。看来以前的学习不认真，犯了一个小错误。 --//有了这个就可以在使用 cursor_sharing=force的情况下，在一些谓词条件to_char的情况下实现使用函数索引。 --//当然缺点就是无法共享相同光标了，消耗大量共享池资源，每次语句不同可能都需要硬解析。 --//有机会再测试看看。顺便看看解析的情况： --//收尾： SYS@book> exec sys.dbms_sqldiag.drop_sql_patch('user_extents_patch'); PL/SQL procedure successfully completed.