[20250525]设置cursor_sharing=force下PLSQL语句的光标缓存问题2.txt

[20250525]设置cursor_sharing=force下PLSQL语句的光标缓存问题2.txt --//测试cursor_sharing=force的情况下，PLSQL脚本SQL语句的光标缓存问题,补充一个测试。 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.测试脚本： $ cat loopf.txt set verify off DECLARE    l_count  PLS_INTEGER;    l_count1 PLS_INTEGER;    l_count2 PLS_INTEGER; BEGIN     FOR i IN 1..&&1     LOOP        select /*+ &2 */ count(*) into l_count from dept where deptno=１0;        select /*+ &2 */ count(*) into l_count1 from dept where deptno=20;        select /*+ &2 */ count(*) into l_count2 from dept where deptno=30;        select /*+ &2 */ count(*) into l_count  from dept where deptno=40;        select /*+ &2 */ count(*) into l_count  from dept where deptno=41;        select /*+ &2 */ count(*) into l_count  from dept where deptno=42     ;     END LOOP; END; / --//注意deptno=42结尾有许多空格。 3.测试： SCOTT@book01p> @ spid ============================== SID                           : 277 SERIAL#                       : 7336 PROCESS                       : 3461 SERVER                        : DEDICATED SPID                          : 3463 PID                           : 86 P_SERIAL#                     : 1 KILL_COMMAND                  : alter system kill session '277,7336' immediate; PL/SQL procedure successfully completed. SCOTT@book01p> alter session set cursor_sharing=force; Session altered. SCOTT@book01p> @ loopf.txt  1 AAAA PL/SQL procedure successfully completed. SYS@book> select * from gv$open_cursor where sid=277 and sql_text like '%AAAA%'; INST_ID SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                               LAST_SQL_ACTIVE_TIM CURSOR_TYPE          CHILD_ADDRESS    CON_ID ------- ---------------- --- --------- ---------------- ---------- ------------- ------------------------------------------------------ ------------------- -------------------- ---------------- ------       1 000000007EA26FA8 277 SCOTT     0000000070A1EDE8 3002496477 bp4mb6ytgcxfx SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=41  2025-05-25 09:34:57 PL/SQL CURSOR CACHED 0000000070BBDED0      3       1 000000007EA26FA8 277 SCOTT     000000006FF93090 1353645033 cc05gmd8axyz9 SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=20  2025-05-25 09:34:57 PL/SQL CURSOR CACHED 0000000070B06350      3       1 000000007EA26FA8 277 SCOTT     000000006A9AB540 1123750091 5xx33jp1gq46b SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=42  2025-05-25 09:34:57 PL/SQL CURSOR CACHED 000000006A9F5690      3       1 000000007EA26FA8 277 SCOTT     000000006FE6A310   45577732 cdqsh1n1bfxh4 SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO= １0  2025-05-25 09:34:57 PL/SQL CURSOR CACHED 0000000070AA2338      3       1 000000007EA26FA8 277 SCOTT     0000000070B8FA90 4206597783 7dkzgc3xbr5nr SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=40  2025-05-25 09:34:57 PL/SQL CURSOR CACHED 0000000070D4CC68      3       1 000000007EA26FA8 277 SCOTT     000000006FFA0970 3194081461 13jgdsaz63n5p SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=30  2025-05-25 09:34:57 PL/SQL CURSOR CACHED 0000000070DB72D8      3 6 rows selected. SYS@book> select SQL_ID,sql_text,length(sql_text) from gv$open_cursor where sid=277 and sql_text like '%AAAA%'; SQL_ID        SQL_TEXT                                                     LENGTH(SQL_TEXT) ------------- ------------------------------------------------------------ ---------------- bp4mb6ytgcxfx SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=41                      53 cc05gmd8axyz9 SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=20                      53 5xx33jp1gq46b SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=42                      54 cdqsh1n1bfxh4 SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO= １0                      53 7dkzgc3xbr5nr SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=40                      53 13jgdsaz63n5p SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=30                      53 6 rows selected. --//再次颠覆我以前的认知，全部sql语句除了做了格式化处理并且转换为大写外，并没有转换为设置cursor_sharing=force的情况。 $ sql_idz.sh "SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=42 \0" 3 sql_text = SELECT /*+ AAAA */ COUNT(*) FROM DEPT WHERE DEPTNO=42 \0 full_hash_value(16) = E5A980D7ED7FF1305EF4638D42FB10CB or e5a980d7ed7ff1305ef4638d42fb10cb xxxxx_matching_signature(10) = 6842203192247914699 or  25288947265957466315 hash_value(10) = 1123750091 or hash_value(16) = 42FB10CB or 42fb10cb sql_id(16) = 5EF4638D42FB10CB or 5ef4638d42fb10cb sql_id(32) = 5xx33jp1gq46b sql_id(32) = 5xx33jp1gq46b sql_id(32) = 5xx33jp1gq46b --//使用以前的写的sql_idz.sh脚本计算sql_id,可以发现计算结果一致。