今天做测试,本来想测试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说明:
今天做测试,本来想测试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说明:
|
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.1.0.2 [Release 11.2 to 12.1] |