这篇文章主要讲解了“分析SQL中parameter table management问题”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“分析SQL中parameter table management问题”吧！

1.查询要执行的SQL，是否有子游标，没有子游标

SYS@honor>selectsql_id,child_number,sql_textfromv$sqlwheresql_textlike'%SELECTcount(*)FROMt%';SQL_IDCHILD_NUMBERSQL_TEXT---------------------------------------------------------------------------------------------------------gxfdy6zpjvmtc0selectsql_id,child_number,sql_textfromv$sqlwheresql_textlike'%SELECTcoun

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

session1>select*fromv$mystatwherestatistic#=0;SIDSTATISTIC#VALUE------------------------------------------------------------------------------------------------------------------------4900session1>DECLARE2anumber;3vvarchar2(20):='haha';4BEGIN5FORcIN1..1000006LOOP7EXECUTEIMMEDIATE'ALTERSESSIONSEToptimizer_index_cost_adj=1';EXECUTEIMMEDIATE'SELECTcount(*)FROMt'intoa;9ENDLOOP;10END;11/PL/SQLproceduresuccessfullycompleted.

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

session2>select*fromv$mystatwherestatistic#=0;SIDSTATISTIC#VALUE------------------------------------------------------------------------------------------580##########session2>DECLARE2anumber;3vvarchar2(20):='haha';4BEGIN5FORcIN1..1000006LOOP7EXECUTEIMMEDIATE'ALTERSESSIONSEToptimizer_index_cost_adj=2';EXECUTEIMMEDIATE'SELECTcount(*)FROMt'intoa;9ENDLOOP;10END;11/PL/SQLproceduresuccessfullycompleted.

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

SYS@honor>selectsql_id,child_number,sql_textfromv$sqlwheresql_textlike'%SELECTcount(*)FROMt%';SQL_IDCHILD_NUMBERSQL_TEXT---------------------------------------------------------------------------------------------------------5nbjnx26pn4rh0DECLAREanumber;BEGINFORcIN1..100000LOOPEXECUTEIMMEDIAT5tjqf7sx5dzmj0SELECTcount(*)FROMt5tjqf7sx5dzmj1SELECTcount(*)FROMtf14srtthcadyq0DECLAREanumber;vvarchar2(20):='haha';BEGINFORcIN1..100000Lgxfdy6zpjvmtc0selectsql_id,child_number,sql_textfromv$sqlwheresql_textlike'%SELECTcoun

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

SYS@honor1>selectsid,USERNAME,EVENT,sql_id,SQL_CHILD_NUMBER,p1,p2,p3,WAIT_CLASS,WAIT_TIME,BLOCKING_SESSION,BLOCKING_SESSION_STATUSfromv$sessionwheresidin('49','58');SIDUSERNAMEEVENTSQL_IDSQL_CHILD_NUMBERP1P2P3WAIT_CLASSWAIT_TIMEBLOCKING_SESSIONBLOCKING_SESSION_STATUS---------------------------------------------------------------------------------------------------------------------------------------------------------------49LIBAIlatchfree5nbjnx26pn4rh01610665040240Other-1NOTINWAIT58LIBAIlatchfree5tjqf7sx5dzmj11610665040240Other-1NOTINWAITSYS@honor1>selectto_char('1610665040','xxxxxxxxxxxx')fromdual;TO_CHAR('1610-------------6000cc50SYS@honor1>selectaddr,latch#,hash,namefromv$latchwhereaddrlike'%6000CC50%';ADDRLATCH#HASHNAME----------------------------------------------------------------------------------------------------------------------------------------000000006000CC5024722869772parametertablemanagement

5.bug说明：

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.

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

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 "parametertable management" latch.

ApplyPatch 20564072

感谢各位的阅读，以上就是“分析SQL中parameter table management问题”的内容了，经过本文的学习后，相信大家对分析SQL中parameter table management问题这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！