在 Oracle 11G 单机（单实例） 上，用 SQL*Plus 多会话并发 即可同时产生两个等待时间

• latch: shared pool
• cursor: pin S wait on X

的可复现脚本。

核心思路：

1. 用 ALTER SYSTEM FLUSH SHARED_POOL; 把共享池清空 → 强制硬解析；
2. 多会话同时执行无绑定变量的 SELECT → 大量硬解析抢 shared pool latch；
3. 同一 SQL 文本会被串行硬解析 → 后到的会话在 cursor: pin S wait on X 上排队。

0 基础知识复习

1) latch: shared pool 等待事件分析

latch: shared pool 是一个典型的资源争用等待事件。Latch（闩锁）是一种轻量级的、用于保护SGA（System Global Area）中共享内存结构的内部锁机制
。

• 产生核心原因：当多个数据库进程需要访问或修改共享池（Shared Pool）中的数据结构时，它们必须先获取shared pool latch。如果获取失败，进程就会等待，从而产生latch: shared pool等待。最常见的诱因是高频率的硬解析
  。

硬解析为何导致Latch争用?

• 内存分配：当一个全新的SQL语句进入系统时，Oracle需要在共享池中为其分配内存（称为“chunk”），这个分配过程需要shared pool latch的保护。
  数据结构查找与链接：解析过程中，Oracle需要在Library Cache（库缓存，共享池的一部分）中查找对象、检查语法和语义，并将新的游标链接到哈希链表中，这些操作同样受到latch的保护
  。
• 未使用绑定变量：大量使用字面量（Literals）而非绑定变量（Bind Variables）的SQL语句，即使逻辑相同，也会被视为不同的SQL，从而导致无法共享，引发大量不必要的硬解析
  。
• 模拟思路：要模拟此等待，关键在于制造海量的硬解析。最佳方式是通过多个并发会话，循环执行包含动态变化字面量的SQL语句，强制数据库对每一条SQL都进行硬解析。

---

2)cursor: pin S wait on X 等待事件分析

cursor: pin S wait on X 是Oracle 10g引入Mutex（互斥锁）机制后，用于替代部分library cache pin等待的新事件 。它表示一个会话在尝试以共享模式（Share, S）钉住（pin）一个游标对象时，发现该游标已被另一个会话以排他模式（eXclusive, X）持有。

• 产生核心原因：对同一个游标（Cursor）对象句柄（Handle）的并发访问产生了冲突。

• 解析（X锁）vs 执行（S锁） ：当一个会话正在解析或编译一个SQL或PL/SQL对象时（例如，首次执行、对象失效后的重编译），它会以排他模式（X）持有该对象上的Mutex。此时，其他试图执行（需要共享模式S锁）该对象的会话就必须等待，产生cursor: pin S wait on X等待。

• 高并发执行：即使没有重编译，在极高的并发下，多个会话同时涌入，争抢执行同一个SQL，也可能在管理游标状态的短暂瞬间产生Mutex争用 。

• 高版本计数（High Version Count） ：当一个父游标因为各种原因（如优化器环境不匹配）产生大量子游标时，查找合适子游标的过程会加长，增加持有Mutex的时间，从而加剧争用。

• 模拟思路：要模拟此等待，关键在于让多个并发会话高密度地争用同一个SQL或PL/SQL对象。一个有效的方法是让大量会话循环执行同一个存储过程或SQL语句。

一、准备

环境:Oracle 11.2.0.4

–
建一张小表并收集统计信息

sqlplus WEWIN/Oracle123#
CREATE TABLE tb_101 AS SELECT level id, rpad('x',100,'x') pad FROM dual CONNECT BY level<=100;
EXEC dbms_stats.gather_table_stats(user,'tb_101');

二、开四个会话

（session-1 … session-N，N≥4 效果更明显）

开四个会话，执行同样的命令：.

sqlplus WEWIN/Oracle123#
-- 连接进去后执行

ALTER SYSTEM FLUSH SHARED_POOL;

SET termout off feedback off arraysize 1
ALTER SESSION SET cursor_sharing=EXACT;   -- 强制 literal
EXEC dbms_random.seed(1);

DECLARE
   x NUMBER;
BEGIN
   FOR i IN 1..500000 LOOP
      -- 每条 SQL 文本都不一样 → 强制硬解析
      EXECUTE IMMEDIATE
         'SELECT /* literal_'||i||' */ count(*) FROM tb_101 WHERE id='||
         round(dbms_random.value(1,100));
   END LOOP;
END;
/

三、观察会话情况

-- 实时看等待事件
set lines 200
col tm          format a12
col sid         format 9999
col event       format a28
col bsid        format 9999
col sql_id      format a13
col sec         format 9990.00

SELECT to_char(sysdate,'hh24:mi:ss') tm,
       sid,
       event,
       blocking_session bsid,
       sql_id,
       seconds_in_wait sec
FROM   v$session
WHERE  username='WEWIN'
AND    type   ='USER'
AND    event  IN ('latch: shared pool','cursor: pin S wait on X')
ORDER  BY sec DESC;
TM             SID EVENT                         BSID SQL_ID             SEC
------------ ----- ---------------------------- ----- ------------- --------
18:07:10        29 latch: shared pool                 3jpvrrvsfyb0v     0.00
18:07:10       144 latch: shared pool                 dxbazrqqfmq3a     0.00
18:07:10       142 latch: shared pool                 11jz7tugk5j97     0.00
18:07:10       133 latch: shared pool                 558j1xmcxs3tp     0.00

-- 等待一会继续看
TM             SID EVENT                         BSID SQL_ID             SEC
------------ ----- ---------------------------- ----- ------------- --------
18:15:21        29 latch: shared pool                 589b4cvv36k68     0.00
18:15:21       133 latch: shared pool                 05z1m73f2z9b2     0.00
18:15:21       144 latch: shared pool                 g4f233w7hwk34     0.00

SQL> /

TM             SID EVENT                         BSID SQL_ID             SEC
------------ ----- ---------------------------- ----- ------------- --------
18:15:27        29 latch: shared pool                 1x1dq2fnx42wb     0.00
18:15:27       144 latch: shared pool                 558j1xmcxs3tp     0.00

-- 闩锁统计
col name for a15
SELECT name,gets,misses,sleeps
FROM   v$latch WHERE name='shared pool';
NAME                  GETS     MISSES     SLEEPS
--------------- ---------- ---------- ----------
shared pool       20124105    1412880      58737

18:08:23 SQL> /

NAME                  GETS     MISSES     SLEEPS
--------------- ---------- ---------- ----------
shared pool       20608769    1448228      60352

18:08:31 SQL> /

NAME                  GETS     MISSES     SLEEPS
--------------- ---------- ---------- ----------
shared pool       21115501    1483415      62085
NAME                  GETS     MISSES     SLEEPS
--------------- ---------- ---------- ----------
shared pool       40307409    2838842     123671

18:14:41 SQL> 

负载情况
top - 18:11:22 up  2:27,  8 users,  load average: 3.16, 2.65, 1.46
Tasks: 265 total,   6 running, 259 sleeping,   0 stopped,   0 zombie
%Cpu0  : 80.7 us,  9.3 sy,  0.0 ni,  2.1 id,  0.0 wa,  0.0 hi,  7.9 si,  0.0 st
%Cpu1  : 70.9 us,  8.1 sy,  0.0 ni, 11.5 id,  0.7 wa,  0.0 hi,  8.8 si,  0.0 st
KiB Mem : 16266516 total,  4563628 free,  8610356 used,  3092532 buff/cache
KiB Swap:  8388604 total,  8388604 free,        0 used.  6108332 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                             
 5935 oracle    20   0 2656480 647680 644784 R  46.9  4.0   3:46.57 oracle                                                              
 5997 oracle    20   0 2657508 618052 614180 R  45.2  3.8   3:31.27 oracle                                                              
 5929 oracle    20   0 2656612 663508 660364 R  44.2  4.1   4:06.08 oracle                                                              
 5999 oracle    20   0 2657508 611192 607344 R  43.2  3.8   3:20.58 oracle                                                              
 2256 oracle    -2   0 2654772  13520  11392 S   9.9  0.1   8:02.30 oracle                                                              
 4265 root      20   0 1197624  99436  51696 S   0.7  0.6   0:08.77 gnome-initial-s                                                     
 ...

取消四个会话后，latch shared pool 马上消失了

ctrl+c
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at line 6

SQL> /

SELECT to_char(sysdate,'hh24:mi:ss') tm,
       sid,
       event,
       blocking_session bsid,
       sql_id,
       seconds_in_wait sec
FROM   v$session
WHERE  username='WEWIN'
AND    type   ='USER'
AND    event  IN ('latch: shared pool','cursor: pin S wait on X')

no rows selected

新问题：为什么没有看到cursor: pin S wait on X ？

没看到 cursor: pin S wait on X 是因为 11gR2 的“无绑定变量”硬解析风暴主要抢的是「shared pool latch」本身，而 cursor pin 的 X 模式只在「构造父/子游标」瞬间持有，时间极短（微秒级）。
当 CPU 核数充足、SQL 文本又各不相同时，每个会话几乎立刻拿到 latch 并完成解析，于是 X pin 几乎不暴露到 v$session，只剩 latch: shared pool 持续可见。

---

11g 与 19c 的差异

版本	共享游标并发控制	典型暴露事件
11gR2	库缓存 hash bucket 保护 = latch，pin X 持有极短	latch: shared pool
12c+	mutex 替代大部分 latch，pin X 持有时间可被采样	cursor: pin S wait on X 明显

latch: shared pool: 压力分散在整个共享池，由大量不同的SQL引发。
cursor: pin S wait on X: 压力集中在某一个具体的SQL对象上。

四、重新模拟2个等待时间同时出现

再次模拟
定义两组不同的会话负载：一组用于制造硬解析，另一组用于制造游标争用。

• 并发执行：使用操作系统脚本或手动开启多个SQL*Plus窗口，同时启动这两组负载。
• 全局监控：在负载运行期间，通过查询动态性能视图可以观察到部分会话处于latch: shared pool等待，而另一部分会话处于cursor: pin S wait on X等待，从而达到同时模拟的目标。

4.1 创建测试表

此表将用于模拟脚本中的查询操作。

数据内容不重要，表结构存在即可。

-- 以具有DBA权限或足够权限的用户登录
CREATE TABLE t_big (
    id          NUMBER PRIMARY KEY,
    vc_data     VARCHAR2(100),
    create_date DATE
);

-- 插入少量数据以供查询
INSERT INTO t_big VALUES (1, 'test_data', SYSDATE);
COMMIT;

4.2 创建测试存储过程

此存储过程将被高并发调用，以模拟cursor: pin S wait on X。

CREATE OR REPLACE PROCEDURE p_test_cursor_pin
IS
    v_count NUMBER;
BEGIN
    -- 一个简单且执行快速的SQL，关键在于它是“同一个”SQL
    SELECT COUNT(*)
    INTO v_count
    FROM t_big
    WHERE id = 1;
END;
/

4.3 脚本A：模拟 latch: shared pool

此脚本通过在循环中生成并执行带有不同字面量的SQL语句，来制造大量的硬解析。

操作指南：
打开 至少5个 SQL*Plus或其它SQL客户端会话，在每个会话中执行以下PL/SQL块。

-- Script A: for generating 'latch: shared pool'
-- 在多个会话中同时运行此脚本

SET SERVEROUTPUT ON
DECLARE
    v_sql VARCHAR2(200);
    v_count NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting hard parse simulation for session: ' || USERENV('SID'));
    -- 循环执行100万次，制造持续的硬解析压力
    FOR i IN 1..1000000 LOOP
        BEGIN
            -- 关键点：通过拼接变量i，使得每次循环的SQL语句都不同，强制硬解析
            v_sql := 'SELECT COUNT(*) FROM t_big WHERE id = ' || TO_CHAR(i);
            
            -- 执行动态SQL
            EXECUTE IMMEDIATE v_sql INTO v_count;

            -- (可选) 每10000次输出一次，以观察进度
            IF MOD(i, 10000) = 0 THEN
                DBMS_OUTPUT.PUT_LINE('Session ' || USERENV('SID') || ' executed ' || i || ' hard parses.');
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                -- 忽略可能发生的任何错误，继续循环
                NULL;
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Simulation finished for session: ' || USERENV('SID'));
END;
/

4.4 脚本B：模拟 cursor: pin S wait on X

此脚本通过在循环中高频调用同一个存储过程，来制造对该游标的并发争用。

操作指南：
另外打开 至少5个 SQL*Plus或其它SQL客户端会话，在每个会话中执行以下PL/SQL块。

-- Script B: for generating 'cursor: pin S wait on X'
-- 在多个会话中同时运行此脚本

SET SERVEROUTPUT ON
BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting cursor pin contention simulation for session: ' || USERENV('SID'));
    -- 循环执行100万次，制造对p_test_cursor_pin过程的高并发调用
    FOR i IN 1..1000000 LOOP
        BEGIN
            -- 关键点：高频调用完全相同的存储过程
            p_test_cursor_pin;
            
            -- (可选) 每10000次输出一次，以观察进度
            IF MOD(i, 10000) = 0 THEN
                DBMS_OUTPUT.PUT_LINE('Session ' || USERENV('SID') || ' executed p_test_cursor_pin ' || i || ' times.');
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                -- 忽略可能发生的任何错误，继续循环
                NULL;
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Simulation finished for session: ' || USERENV('SID'));
END;
/

4.5 并发执行指令

准备好至少10个SQL客户端会话。
在会话1到5中，粘贴并准备运行 脚本A。
在会话6到10中，粘贴并准备运行 脚本B。
同时（或尽可能快地相继）在所有10个会话中启动脚本。
让脚本运行至少1-2分钟，以产生足够的等待事件样本。

SELECT to_char(sysdate,'hh24:mi:ss') tm,
       sid,
       event,
       blocking_session bsid,
       sql_id,
       seconds_in_wait sec
FROM   v$session
WHERE  username='WEWIN'
AND    type   ='USER'
AND    event  IN ('latch: shared pool','cursor: pin S wait on X')

TM             SID EVENT                         BSID SQL_ID             SEC
------------ ----- ---------------------------- ----- ------------- --------
18:49:02        16 latch: shared pool                                   0.00
18:49:02        29 cursor: pin S wait on X        133 brbb3ht15c9t4     0.00
18:49:02       125 cursor: pin S wait on X            3kzqyddprzz1k     0.00
18:49:02       142 latch: shared pool                 a3tag8tf2k3cn     0.00

SQL> /

TM             SID EVENT                         BSID SQL_ID             SEC
------------ ----- ---------------------------- ----- ------------- --------
18:50:55       133 latch: shared pool                 a3tag8tf2k3cn     0.00
18:50:55       144 cursor: pin S wait on X            a3tag8tf2k3cn     0.00

top - 18:51:24 up  3:08, 14 users,  load average: 6.87, 4.40, 2.38
Tasks: 298 total,   6 running, 292 sleeping,   0 stopped,   0 zombie
%Cpu0  : 49.5 us,  8.7 sy,  0.0 ni, 36.5 id,  1.4 wa,  0.0 hi,  3.8 si,  0.0 st
%Cpu1  : 65.7 us,  8.3 sy,  0.0 ni, 20.7 id,  0.0 wa,  0.0 hi,  5.3 si,  0.0 s

10个会话执行完成或者被取消（ctrl+c）后，两个等待时间马上消失。