latch: cache buffers chains-热块的简单模拟实验

1.创建测试数据.

create table t1 as select rownum id from dba_objects;
CREATE INDEX T1_IDX ON T1(ID) ;

2.创建两个存储过程.

create or replace
PROCEDURE pselect2
AS
l_num number;
BEGIN
  FOR i IN 1..1000000
  LOOP
    SELECT count(*) into l_num FROM T1 where t1.id between 20000 and 30000;
  END LOOP;
END;


create or replace
procedure pupdate2
AS
BEGIN
  FOR i IN 1..1000000
  LOOP
    UPDATE t1 SET id = rownum where t1.id  between 20000 and 30000;
    COMMIT;
  END LOOP;
END;

3.简单模拟几个并发
session 1:exec pselect2;
session 2:exec pselect2;
session 3:exec pselect2;
session 4:exec pupdate2;
session 5:exec pupdate2;
session 6:exec pupdate2;
...


4.查询等待事件

SELECT EVENT,
  P1,
  P1RAW,
  P2,
  P2RAW,
  state
FROM v$session_wait
WHERE sid IN
  (SELECT SID FROM V$SESS