[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