ITPub博客

首页 > 数据库 > Oracle > [20211025]11g sequemce nocahe测试.txt

[20211025]11g sequemce nocahe测试.txt

原创 Oracle 作者:lfree 时间:2021-10-25 16:27:11 0 删除 编辑

[20211025]11g sequemce nocahe测试.txt

--//上个星期我发现19c生产系统有几个sequence开发给设置为nocache属性.导致出现row cache lock.
--//当我单独查询该语句的等待事件时,出现一个我以前从来没见过的row cache mutex的等待事件.
--//难道19c做了某些改进,在字段管理上开始使用mutex.
--//我从来没有在11g测试使用sequence nocache,使用ashtop看过相关等待,测试看看.

> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

> @ ashtop event,sql_id "event like '%row cache%' and sql_id='dg0ftvs2tm5an'" sysdate-1/24 sysdate
    Total
  Seconds     AAS %This   EVENT            SQL_ID        FIRST_SEEN          LAST_SEEN
--------- ------- ------- ---------------- ------------- ------------------- -------------------
     1755    45.0   99% | row cache lock   dg0ftvs2tm5an 2021-10-22 15:02:30 2021-10-22 16:02:06
       15      .4    1% | row cache mutex  dg0ftvs2tm5an 2021-10-22 15:02:32 2021-10-22 16:02:06
--//row cache lock事件并且多了一个row cache mutex。

1.环境:
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table job_times (sid number, time_ela number,method varchar2(20));
create sequence seq1 nocache;

SCOTT@book> @ ddl scott.seq1
C300
--------------------------------------------------------------------------------------------------------------------------------------------
CREATE SEQUENCE  "SCOTT"."SEQ1"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;

$ cat m14.txt
set verify off
host sleep $(echo &&3/50 | bc -l )
variable vmethod varchar2(20);
exec :vmethod := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;
commit ;
declare
v_id number;
v_d date;
v_val VARCHAR2(1000);
l_count PLS_INTEGER;
begin
    for i in 1 .. &&1 loop
        --//select value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
        --//select value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
        --//select sql_text into v_val from v$sql where rownum=1;
        SELECT /* &&3 */ seq1.NEXTVAL into v_id FROM DUAL;
    end loop;
end ;
/
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod;
commit;
quit

2.测试:
--//我没有想到这么慢,在没有打开cache的情况下,只能减少并发数量以及循环次数1e4:

$ zzdate;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m14.txt 1e4 P=50 {} 1 >/dev/null;zzdate
trunc(sysdate)+09/24+12/1440+14/86400 == 2021/10/25 09:12:14
trunc(sysdate)+09/24+14/1440+24/86400 == 2021/10/25 09:14:24
--//2*60+24-14 = 130

SYS@book>  @ashtop event,sql_id "module='SQL*Plus' and username='SCOTT'" trunc(sysdate)+09/24+12/1440+14/86400 trunc(sysdate)+09/24+14/1440+24/86400
    Total
  Seconds     AAS %This   EVENT                                    SQL_ID        FIRST_SEEN          LAST_SEEN
--------- ------- ------- ---------------------------------------- ------------- ------------------- -------------------
     6288    48.4   97% | row cache lock                           9cp836a3k67w2 2021-10-25 09:12:14 2021-10-25 09:14:23
      152     1.2    2% |                                          9cp836a3k67w2 2021-10-25 09:12:14 2021-10-25 09:14:23
        4      .0    0% |                                                        2021-10-25 09:12:51 2021-10-25 09:14:21
        2      .0    0% |                                          a01fjfup7ruah 2021-10-25 09:12:52 2021-10-25 09:13:12
        2      .0    0% |                                          gxgnku5buj8cm 2021-10-25 09:13:51 2021-10-25 09:14:09
        1      .0    0% | log file switch (checkpoint incomplete)  9cp836a3k67w2 2021-10-25 09:14:07 2021-10-25 09:14:07
        1      .0    0% |                                          1mm9uymckm8z4 2021-10-25 09:12:28 2021-10-25 09:12:28
        1      .0    0% |                                          24c64p3xa1hr5 2021-10-25 09:13:28 2021-10-25 09:13:28
        1      .0    0% |                                          7b9znjngh150d 2021-10-25 09:13:17 2021-10-25 09:13:17
        1      .0    0% |                                          fsc8vbts8tu24 2021-10-25 09:12:26 2021-10-25 09:12:26
        1      .0    0% |                                          g2q7km1t9cdnj 2021-10-25 09:13:29 2021-10-25 09:13:29
        1      .0    0% |                                          gpa5h7jh3dg4d 2021-10-25 09:14:10 2021-10-25 09:14:10
        1      .0    0% |                                          gty6quqfxrgna 2021-10-25 09:13:39 2021-10-25 09:13:39
13 rows selected.
--//仅仅出现row cache lock 等待事件。

SYS@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
P=50                         50                  12924        646217

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2839138/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3085
  • 访问量
    6808133