189 8069 5689

分析SQL中parametertablemanagement问题

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

创新互联公司专注于垣曲网站建设服务及定制,我们拥有丰富的企业做网站经验。 热诚为您提供垣曲营销型网站建设,垣曲网站制作、垣曲网页设计、垣曲网站官网定制、重庆小程序开发服务,打造垣曲网络公司原创品牌,更为您提供垣曲网站排名全网营销落地服务。

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)

分析SQL中parameter table management问题


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

     

  • 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

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


网站名称:分析SQL中parametertablemanagement问题
转载注明:http://cdxtjz.cn/article/pohoei.html

其他资讯