parameter table management，11.2.0.4 Bug 20564072

今天做测试，本来想测试library cache mutex X，结果意外发现11.2.0.4的一个bug 开始测试 1.查询要执行的SQL，是否有子游标，没有子游标

SYS@honor > select sql_id,child_number,sql_text from v$sql where sql_text like '%SELECT count(*) FROM t%';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
gxfdy6zpjvmtc            0 select sql_id,child_number,sql_text from v$sql where sql_text like '%SELECT coun

2.查询当前会话sid，session1执行相关存储过程

session1 > select * from v$mystat where statistic#=0;
                                     SID                               STATISTIC#                                    VALUE
---------------------------------------- ---------------------------------------- ----------------------------------------
                                      49                                        0                                        0
session1 > DECLARE
  2    a number;
  3    v varchar2(20):='haha';
  4  BEGIN
  5      FOR c IN 1..100000
  6      LOOP
  7        EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = 1';
      EXECUTE IMMEDIATE 'SELECT count(*) FROM t' into a;
  9      END LOOP;
 10  END;
 11  /
PL/SQL procedure successfully completed.

2.查询当前会话sid，session2执行相关存储过程

session2 > select * from v$mystat where statistic#=0;
                                     SID                               STATISTIC#      VALUE
---------------------------------------- ---------------------------------------- ----------
                                      58                                        0 ##########
session2 > DECLARE
  2    a number;
  3    v varchar2(20):='haha';
  4  BEGIN
  5      FOR c IN 1..100000
  6      LOOP
  7        EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = 2';
      EXECUTE IMMEDIATE 'SELECT count(*) FROM t' into a;
  9      END LOOP;
 10  END;
 11  /
PL/SQL procedure successfully completed.

3.查询查询SQL子游标，发现有两个子游标

SYS@honor > select sql_id,child_number,sql_text from v$sql where sql_text like '%SELECT count(*) FROM t%';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
5nbjnx26pn4rh            0 DECLARE   a number; BEGIN     FOR c IN 1..100000     LOOP       EXECUTE IMMEDIAT
5tjqf7sx5dzmj            0 SELECT count(*) FROM t
5tjqf7sx5dzmj            1 SELECT count(*) FROM t
f14srtthcadyq            0 DECLARE   a number;   v varchar2(20):='haha'; BEGIN     FOR c IN 1..100000     L
gxfdy6zpjvmtc            0 select sql_id,child_number,sql_text from v$sql where sql_text like '%SELECT coun

4.查询会话执行期间，等待事件，根据原理，两个存储过程执行过程中，会引发select语句子游标持有父游标的指向子游标的handle导致的cursor pin S wait on x争用，但是意外发现发生了latch free，经过查询latch类型，为parameter table management，并没有见过相关latch，去查Mos，发现命中bug，在12.2中修复，或者打补丁

SYS@honor1 > select sid,USERNAME,EVENT,sql_id,SQL_CHILD_NUMBER,p1,p2,p3,WAIT_CLASS,WAIT_TIME,BLOCKING_SESSION,BLOCKING_SESSION_STATUS from v$session where sid in ('49','58');
       SID USERNAME    EVENT               SQL_ID        SQL_CHILD_NUMBER         P1         P2         P3 WAIT_CLASS   WAIT_TIME BLOCKING_SESSION BLOCKING_SESSION_STATUS
---------- ----------- ------------------- ------------- ---------------- ---------- ---------- ---------- ----------- ---------- ---------------- -----------------------
        49 LIBAI       latch free          5nbjnx26pn4rh                0 1610665040         24          0 Other               -1                  NOT IN WAIT
        58 LIBAI       latch free          5tjqf7sx5dzmj                1 1610665040         24          0 Other               -1                  NOT IN WAIT
        
SYS@honor1 > select to_char('1610665040','xxxxxxxxxxxx') from dual;
TO_CHAR('1610
-------------
     6000cc50
SYS@honor1 > select addr,latch#,hash,name from v$latch where addr like '%6000CC50%';
ADDR                                               LATCH#                                     HASH NAME
---------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
000000006000CC50                                       24                                722869772 parameter table management

5.bug说明：

Session Waiting on 'Parameter Table Management' Latch (Doc ID 2271591.1)

APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.1.0.2 [Release 11.2 to 12.1] Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Information in this document applies to any platform. SYMPTOMS Database wait's for latch free  ASH report shows following event values: Top Event P1/P2/P3 Values Event % Event P1 Value, P2 Value, P3 Value % Activity Parameter 1 Parameter 2 Parameter 3 latch free 24.60 "1610670304","30","0" 24.52 address number tries <---------------- P2 is 30   Query from v$latchname shows latch to be 'parameter table management':     SQL> select name from v$latchname where latch#=30; NAME ---------------------------------------------------------------- parameter table managementAnd it turns out the session is executing this pl/sql blockBEGIN EXECUTE IMMEDIATE 'alter session set current_schema = SCHEMA1' ; EXECUTE IMMEDIATE 'alter session set nls_date_format = ''MM/DD/YYYY''' ; DBMS_APPLICATION_INFO.SET_MODULE(:p1, NULL); DBMS_SESSION.SET_IDENTIFIER(:p2); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(:p3); DBMS_APPLICATION_INFO.SET_ACTION(:p4); END;      CAUSE This is due to following bug: Bug 20564072 : RELAX EXCLUSIVE GET ON PARAMETER TABLE MANAGEMENT LATCH FOR ALTER SESSION This bug can be encountered if processes are contending for the "parameter table management" latch. SOLUTION Apply  Patch 20564072