ORACLE故障案例-BIND_LENGTH_UPGRADEABLE导致的游标不共享，硬解析阻塞交易超时

概述

某银行客户核心系统，发生交易超时，分析核心交易SQL等待事件cursor: pin S wait on X,该等待是由于SQL重新解析，最先解析的SESSION解析耗时长，产生排队阻塞，导致超时。

该客户大量使用RANGE+LIST/HASH的二级分区表，分区数量多，大量核心交易表都3000以上的分区，分区过多会导致加载TABLE LIBRARY CACHE OBJECT慢，导致解析慢，排队现象，放大了硬解析的影响

问题分析

发生问题的SQL是一个简单的insert values，执行频繁，刷出可能性低，为什么会发生硬解析？

1、表没有收集统计信息

2、没有做DDL

3、检查 shared pool不紧张，shared pool 没有resize

4、通过v$sql_shared_cursor定位到BIND_LENGTH_UPGRADEABLE

BIND_LENGTH_UPGRADEABLE是什么

Bind length upgradeable and could not be shared because a bind variable size was smaller than the new value being inserted (marked as BIND_MISMATCH in earlier versions). 绑定长度可升级，但由于绑定变量的大小小于要插入的新值而无法共享（在早期版本中标记为 BIND_MISMATCH）

在生成CURSOR时，变长类型会根据绑定变量实际值，创建合适大小的BUFFER大小,随着后面实际绑定变量长度的增加，逐步升级32，128，2000，4000，产生新的CURSOR

• 新的绑定变量值长度大于已有CURSOR BIND BUFFER大小，不能共享，需要硬解析产生新的子游标

• 新的绑定变量值长度小于已有CURSOR BIND BUFFER大小，可以共享，不需要硬解析

执行顺序，绑定变量值长度从小往大

 declare
 instring varchar2(4000);
   begin
    for i in 1..300 loop
      instring := rpad('X',i*10,'X');
      insert /*+ bind_length_testa */ into test values (instring);
    end loop;
 end;
 /
 
 
 SQL> select sql_id,child_number,executions,last_load_time,last_active_time from v$sql where sql_id='94rj3c1j49u7d';
 
 SQL_ID          CHILD_NUMBER EXECUTIONS LAST_LOAD_TIME                                                               LAST_ACTIVE_TIME
 --------------- ------------ ---------- ---------------------------------------------------------------------------- -------------------
 94rj3c1j49u7d              0          3 2025-09-28/14:07:21                                                          2025-09-28 14:07:20
 94rj3c1j49u7d              1          9 2025-09-28/14:07:21                                                          2025-09-28 14:07:20
 94rj3c1j49u7d              2        188 2025-09-28/14:07:21                                                          2025-09-28 14:07:21
 94rj3c1j49u7d              3        100 2025-09-28/14:07:21                                                          2025-09-28 14:07:21
 
 set pages 2000 lines 10000
 SELECT * FROM TABLE(version_rpt('94rj3c1j49u7d'));
 
 
 Versions Summary
 ----------------
 HASH_MATCH_FAILED :3
 BIND_LENGTH_UPGRADEABLE :3
 
 Total Versions:3
 
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 cursor_sharing = EXACT
 _cursor_obsolete_threshold = 8192 (See Note:10187168.8)
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 Plan Hash Value Summary
 -----------------------
 Plan Hash Value Count
 =============== =====
               0 4

执行顺序，绑定变量值长度从大往小

 declare
 instring varchar2(4000);
   begin
    for i in 1..300 loop
      instring := rpad('X',(300-i)*10,'X');
      insert /*+ bind_length_testb */ into test values (instring);
    end loop;
 end;
 /
 
 
 SQL> select sql_id,child_number,executions,last_load_time,last_active_time from v$sql where sql_id='1tcwkunfnhsr6';
 
 SQL_ID          CHILD_NUMBER EXECUTIONS LAST_LOAD_TIME                                                               LAST_ACTIVE_TIME
 --------------- ------------ ---------- ---------------------------------------------------------------------------- -------------------
 1tcwkunfnhsr6              0        300 2025-09-28/14:08:44                                                          2025-09-28 14:08:44
 
 SQL> 
 
 set pages 2000 lines 10000
 SELECT * FROM TABLE(version_rpt('1tcwkunfnhsr6'));
 

• 通过EVENT 10503来避免该问题

  event 10503 根据所设置的长度，子游标中的字符型绑定变量将统一使用该长度创建，从而跳过绑定变量长度逐步升级（bind graduation）的过程，并保持子游标链（child chain）相对较小，这有助于缓解与绑定变量长度升级相关的潜在游标共享问题。

   alter session set events '10503 trace name context forever, level 4000';
   
   declare
   instring varchar2(4000);
     begin
      for i in 1..300 loop
        instring := rpad('X',i*10,'X');
        insert /*+ bind_length_testd */ into test values (instring);
      end loop;
   end;
   /
   
   
   SQL> select sql_id,child_number,executions,last_load_time,last_active_time from v$sql where sql_id='aw4hwc9v88jgc';
   
   SQL_ID        CHILD_NUMBER EXECUTIONS LAST_LOAD_TIME                                                               LAST_ACTIVE_TIME
   ------------- ------------ ---------- ---------------------------------------------------------------------------- -------------------
   aw4hwc9v88jgc            0        300 2025-09-28/15:11:37                                                          2025-09-28 15:11:37
   
   SQL> 
   
   退出重新执行
   
   select sql_id,child_number,executions,last_load_time,last_active_time from v$sql where sql_id='aw4hwc9v88jgc';
   
   SQL_ID        CHILD_NUMBER EXECUTIONS LAST_LOAD_TIME                                                               LAST_ACTIVE_TIME
   ------------- ------------ ---------- ---------------------------------------------------------------------------- -------------------
   aw4hwc9v88jgc            0        600 2025-09-28/15:11:37                                                          2025-09-28 15:12:11
   

  该event相关的问题

  ORA-600: [kxspoac : EXL 1] Occuring During Query If Event 10503 Is Set (Doc ID 810194.1) 文档描述所有实例全局设置10503为相同的值可避免

  Bug 10274265 - Event 10503 does not work at session level (Doc ID 10274265.8) Versions >= 10.2 but BELOW 12.1

  
  

为什么之前没有问题？

该实例一周前发生过重启，考虑实例重启后shared pool被清空，逐步有更大长度的绑定变量,游标逐步升级导致，BIND BUFFER LENGTH增长到4000后，如果SQL执行频率高，不被刷出，再发生的概率较低