[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,可以发现计算结果一致。