[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';这样简单的语句都不行为什么.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3011592/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
