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

[20250524]设置cursor_sharing=force下PLSQL语句的光标缓存问题.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 loope.txt set verify off DECLARE    l_count PLS_INTEGER; BEGIN     FOR i IN 1..&&1     LOOP        EXECUTE IMMEDIATE 'Select /*+ &2 */ count(*) from t1 where id = :j '  INTO l_count using i;        EXECUTE IMMEDIATE 'Select /*+ &2 */ count(*) from t1 where id = ' || i  INTO l_count ;        Select /*+ &2 */ count(*) into l_count from t1 where id = i  ;     END LOOP; END; / --//3种执行方式。 3.测试： SCOTT@book01p> @ spid ============================== SID                           : 25 SERIAL#                       : 47259 PROCESS                       : 3431 SERVER                        : DEDICATED SPID                          : 3433 PID                           : 100 P_SERIAL#                     : 2 KILL_COMMAND                  : alter system kill session '25,47259' immediate; PL/SQL procedure successfully completed. SCOTT@book01p> alter session set cursor_sharing=force; Session altered. SCOTT@book01p> @ loope.txt 101 BBBB PL/SQL procedure successfully completed. SYS@book01p> select * from gv$open_cursor where sid=25 and sql_text like '%BBBB%'   2  @ pr ============================== INST_ID                       : 1 SADDR                         : 000000007E79D468 SID                           : 25 USER_NAME                     : SCOTT ADDRESS                       : 00000000639807F8 HASH_VALUE                    : 3858214758 SQL_ID                        : 8cgsmzmkzgbv6 SQL_TEXT                      : Select /*+ BBBB */ count(*) from t1 where id = :j LAST_SQL_ACTIVE_TIME          : 2025-05-24 10:25:00 SQL_EXEC_ID                   : CURSOR_TYPE                   : PL/SQL CURSOR CACHED CHILD_ADDRESS                 : 000000006397F098 CON_ID                        : 3 ============================== INST_ID                       : 1 SADDR                         : 000000007E79D468 SID                           : 25 USER_NAME                     : SCOTT ADDRESS                       : 0000000063980B28 HASH_VALUE                    : 4186563253 SQL_ID                        : 1d3hsrmwsmspp SQL_TEXT                      : SELECT /*+ BBBB */ COUNT(*) FROM T1 WHERE ID = :B1 LAST_SQL_ACTIVE_TIME          : 2025-05-24 10:25:00 SQL_EXEC_ID                   : CURSOR_TYPE                   : PL/SQL CURSOR CACHED CHILD_ADDRESS                 : 00000000639731E0 CON_ID                        : 3 PL/SQL procedure successfully completed. --//可以发现EXECUTE IMMEDIATE 'Select /*+ &2 */ count(*) from t1 where id = ' || i  INTO l_count ;的执行方式光标不缓存。 --//这样导致每次执行都是软解析，出现library cache: mutex X，library cache: bucket mutex X相关等待事件就很正常了。 SYS@book01p> select sql_id,sql_text,executions from v$sqlarea where  lower(sql_text) like 'select%bbbb%' and executions>100; SQL_ID        SQL_TEXT                                                     EXECUTIONS ------------- ------------------------------------------------------------ ---------- 5bfgs3zwnhydr Select /*+ BBBB */ count(*) from t1 where id = :"SYS_B_0"           101 8cgsmzmkzgbv6 Select /*+ BBBB */ count(*) from t1 where id = :j                   101 1d3hsrmwsmspp SELECT /*+ BBBB */ COUNT(*) FROM T1 WHERE ID = :B1                  101 --//还可以发现一个特点EXECUTE IMMEDIATE方式执行的sql语句没有转换。 --//而Select /*+ &2 */ count(*) into l_count from t1 where id = i  ;转换成大写。 SELECT /*+ BBBB */ COUNT(*) FROM T1 WHERE ID = :B1