这篇文章主要讲解了“分析SQL中parameter table management问题”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“分析SQL中parameter table management问题”吧！
创新互联长期为1000多家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为两当企业提供专业的
网站设计制作、网站设计，
两当网站改版等技术服务。拥有10多年丰富建站经验和众多成功案例,为您定制开发。
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 management5.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. SYMPTOMSDatabase 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 "parameter table management" latch. SOLUTIONApply
Patch 20564072
|
感谢各位的阅读，以上就是“分析SQL中parameter table management问题”的内容了，经过本文的学习后，相信大家对分析SQL中parameter table management问题这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
分享名称：分析SQL中parametertablemanagement问题-创新互联
分享网址：
http://cdkjz.cn/article/dcodgp.html
