oracle 等待事件 enq:SQ-contention 模拟

enq:SQ-contention 等待： 频繁的数据字典更新会导致要使用该sequence的session产生该等待事件

--创建sequence
drop SEQUENCE scott.seq_test;
CREATE SEQUENCE scott.seq_test
  START WITH 1
  MAXVALUE 99999999
  MINVALUE 1
  CYCLE
  CACHE 10
  ORDER;
  
--创建一个plsql来消耗seq_test
create or replace procedure scott.p_seq_test is
  seq_value    number ;
  begin
  for seq in 1..4000 loop
    select seq_test.nextval into seq_value from dual;
  end loop;
  end ;  
  /



  
--创建200个job来调用该pl/sql
--每隔1s执行一次  
create or replace procedure scott.create_more_job is
  v_counter    number;begin
  for v_counter in 1..200 loop
    declare
      job1 number;
    begin
      sys.dbms_job.submit(job => job1,
                          what => 'p_seq_test;',
                          next_date => sysdate,
                          interval => 'sysdate + 1/(1440*60)');
    commit;
    end;
  end loop;
  end create_more_job;  
  /
  
 exec   scott.create_more_job
  
--通过修改cache来查看等待
alter sequence scott.seq_test cache {cache数量};  

-- order+cache 10
select trunc(sample_time,'mi') sample_time ,count(1) from v$active_session_history
where sample_time > sysdate -1/24 and sql_id='1p7u83wurqjr4'
group by  trunc(sample_time,'mi')
order by 1;

SAMPLE_TIME                                COUNT(1)
---------------------------------------- ----------
2024-09-23 22:12:00                            9587
2024-09-23 22:13:00                            9896
2024-09-23 22:14:00                            9910

-- enq: SQ - contention

-- order+cache 1000
alter sequence scott.seq_test cache 1000;
select trunc(sample_time,'mi') sample_time ,count(1) from v$active_session_history
where sample_time > sysdate -1/24 and sql_id='1p7u83wurqjr4'
group by  trunc(sample_time,'mi')
order by 1;
SAMPLE_TIME                                COUNT(1)
---------------------------------------- ----------
2024-09-23 22:19:00                            3298
2024-09-23 22:20:00                            3208
2024-09-23 22:21:00                            3225


-- order+cache 10000
alter sequence scott.seq_test cache 10000;
SAMPLE_TIME                                COUNT(1)
---------------------------------------- ----------
2024-09-23 22:21:00                            3225
2024-09-23 22:22:00                            3134
2024-09-23 22:23:00                            1860
2024-09-23 22:24:00                            1903
2024-09-23 22:25:00                            2055
2024-09-23 22:26:00                            1999


--noorder +cache 10
alter sequence scott.seq_test cache 10 noorder;
SAMPLE_TIME                                COUNT(1)
---------------------------------------- ----------
2024-09-23 22:39:00                           10659
2024-09-23 22:40:00                           10620
2024-09-23 22:41:00                           10586

--noorder +cache 1000
alter sequence scott.seq_test cache 1000 noorder;


SAMPLE_TIME                                COUNT(1)
---------------------------------------- ----------
2024-09-23 22:48:00                            3608
2024-09-23 22:49:00                            3609
2024-09-23 22:50:00                            3619
2024-09-23 22:51:00                            3625


--noorder +cache 10000
alter sequence scott.seq_test cache 10000 noorder;
2024-09-23 22:59:00                            2623
2024-09-23 23:00:00                            2582
2024-09-23 23:01:00                            2518

--清理job
BEGIN
 FOR job_id in(select job,log_user,priv_user,schema_user from dba_jobs)
   LOOP
    IF(job_id.log_user  LIKE '%SCOTT%') THEN
       BEGIN  
          dbms_ijob.remove(job_id.job);
          commit;
        end;
    end if;
  end loop;
end;
/



--本次实验证明：
 enq: SQ - contention 和cache 有关系，和有序无序关系不大
--从其他资料发现：
 enq: SV - contention  和有序无序有关系，和cache关系较小
复制