시퀀스와 채번 성능

CREATE SEQUENCE seq_test START WITH 1 INCREMENT BY 1 CACHE 2 NOORDER;

DECLARE
  v NUMBER;
BEGIN
  FOR i IN 1.. 100000 LOOP
    SELECT seq_test.NEXTVAL INTO v FROM dual;
  END LOOP;
END;
/

SELECT DISTINCT s.event, o.owner, o.object_name
  FROM gv$session s
  JOIN dba_objects o ON o.object_id = s.p2
 WHERE s.event = 'enq: SQ - contention'
   AND s.state = 'WAITING' AND s.username = '<SCHEMA>';

SELECT inst_id, total_waits, ROUND(time_waited_micro / 1000000, 1) AS wait_sec
  FROM gv$system_event
 WHERE event = 'enq: SQ - contention';

ALTER SEQUENCE seq_sq_enqueue NOCACHE;
-- CACHE_SIZE 0
SELECT sequence_name, cache_size, order_flag FROM user_sequences;

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

SELECT inst_id, sid, event FROM gv$session
 WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND (event LIKE '%lock%' OR event LIKE '%enq%');

SELECT s.inst_id, s.sid, s.sql_id, q.sql_text
  FROM gv$session s
  JOIN gv$sqlarea q ON q.inst_id = s.inst_id AND q.sql_id = s.sql_id
 WHERE s.sid = <SID> AND s.inst_id = <INST_ID>;

@?/rdbms/admin/addmrpt.sql
@?/rdbms/admin/awrrpt.sql

CREATE SEQUENCE seq_ord   START WITH 1 INCREMENT BY 1 CACHE 20 ORDER;
CREATE SEQUENCE seq_noord START WITH 1 INCREMENT BY 1 CACHE 20 NOORDER;

SELECT sequence_name, cache_size, order_flag, last_number
  FROM user_sequences
 WHERE sequence_name LIKE 'SEQ\_%' ESCAPE '\'
 ORDER BY 1;

CREATE OR REPLACE FUNCTION fn_get_no_후
RETURN NUMBER
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_no NUMBER;
BEGIN
  SELECT 현재번호 + 1 INTO v_no
    FROM 채번관리
   WHERE 테이블명 = '주문거래'
     FOR UPDATE;

  UPDATE 채번관리 SET 현재번호 = v_no WHERE 테이블명 = '주문거래';

  -- 채번 즉시 커밋, 락 해제
  COMMIT;
  RETURN v_no;
END;
/

CREATE SEQUENCE seq_order_no CACHE 20 ORDER;

SET TIMING ON
DECLARE v NUMBER;
BEGIN
  FOR i IN 1..100000 LOOP
    SELECT seq_order_no.NEXTVAL INTO v FROM dual;
  END LOOP;
END;
/

SELECT inst_id, event, COUNT(*)
  FROM gv$session
 WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND wait_class <> 'Idle'
 GROUP BY inst_id, event;

SELECT sequence_name, cache_size, order_flag
  FROM dba_sequences
 WHERE sequence_name = 'SEQ_ORDER_NO';

ALTER SEQUENCE <SCHEMA>.seq_order_no NOORDER CACHE 1000;

CREATE SEQUENCE <SCHEMA>.seq_point_hist CACHE 3 NOORDER;
CREATE SEQUENCE <SCHEMA>.seq_trade CACHE 1000 NOORDER;

SET TIMING ON
DECLARE v1 NUMBER; v2 NUMBER;
BEGIN
  FOR i IN 1..50000 LOOP
    SELECT seq_point_hist.NEXTVAL INTO v1 FROM dual;
    SELECT seq_trade.NEXTVAL INTO v2 FROM dual;
  END LOOP;
END;
/

SELECT DISTINCT o.owner, o.object_name
  FROM gv$session s
  JOIN dba_objects o ON o.object_id = s.p2
 WHERE s.event = 'enq: SQ - contention'
   AND s.state = 'WAITING' AND s.username = '<SCHEMA>';

ALTER SEQUENCE <SCHEMA>.seq_point_hist CACHE 1000;

SELECT inst_id, total_waits
  FROM gv$system_event
 WHERE event = 'enq: SQ - contention';

CREATE SEQUENCE <SCHEMA>.seq_member NOCACHE NOORDER;

SET TIMING ON
DECLARE v NUMBER;
BEGIN
  FOR i IN 1..50000 LOOP
    SELECT seq_member.NEXTVAL INTO v FROM dual;
  END LOOP;
END;
/

-- 부하 직전과 직후
EXEC dbms_workload_repository.create_snapshot();
@?/rdbms/admin/addmrpt.sql
ALTER SEQUENCE <SCHEMA>.seq_member CACHE 1000;