[Oracle DB] Thủ tục xoay vòng dữ liệu trên bảng online
"Bảng NGHIEPVU online có dữ liệu lịch sử dẫn đến dung lượng lớn (Vài trăm GB) gây chậm cho các câu lệnh DML, yêu cầu của nghiệp vụ chỉ cần lưu các bản ghi trong vòng 3 ngày. Vậy làm thế nào để bảng này có cơ chế xoay vòng, chỉ lưu bản ghi của 3 ngày gần nhất, các bản ghi cũ hơn sẽ định kì insert vào bảng khác để lưu trữ"
## Bài toán này cần được chia nhỏ ra thành 2 bài toán con như sau:
- Cắt lấy dữ liệu của 3 ngày gần nhất lưu lại bảng NGHIEPVU để giảm dung lượng cho bảng online, các dữ liệu cũ sẽ được lưu ở bảng khác.
- Bảng có dung lượng lớn, nếu sử dụng phương án DELETE dữ liệu cũ sẽ gây tăng sinh Redo log, Archives log dẫn đến tăng I/O có thể gây down hệ thống (Trường hợp treo disk vì xóa số lượng lớn bản ghi bằng cách delete) -> Cần cắt chuyển bảng bằng cách tạo bảng mới từ dữ liệu gần nhất, sau đó bảng hiện hành sẽ trở thành bảng lưu trữ, bảng mới trở thành bảng hiện hành phục vụ nghiệp vụ.
- Khi bảng chỉ còn dữ liệu của 3 ngày, thiết lập cơ chế định kì xóa các bản ghi cũ hơn 3 ngày, các bản ghi thuộc diện "cũ" này sẽ được insert sang bảng khác có chức năng lưu trữ.
- Khi này dữ liệu bảng đã nhỏ, có thể tiến hành select, insert bản ghi cũ sang bảng lưu trữ, delete bản ghi cũ ở bảng hiện hành.
## Lưu trình:
- Cắt dữ liệu lần 1:
- Select dữ liệu trong vòng 3 ngày để tạo thành bảng mới NGHIEPVU_NEW.
- Đổi tên bảng hiện hành thành NGHIEPVU_OLD.
- Đổi tên bảng NGHIEPVU_NEW thành NGHIEPVU.
- Cắt dữ liệu định kì từ lần 2 trở đi:
- Select dữ liệu cũ, insert vào bảng NGHIEPVU_OLD.
- Delete dữ liệu cũ tại bảng NGHIEPVU.
CREATE TABLESPACE EMPLOYEE DATAFILE '/u01/app/oracle/oradata/CDB1/pdb1/EMPLOYEE.DBF' SIZE 250M AUTOEXTEND ON NEXT 250M MAXSIZE 1000M; CREATE TABLE OAZ21.NGHIEPVU ( ID NUMBER, NAME VARCHAR2(255 BYTE), DATETIME TIMESTAMP(6), SALARY NUMBER(10,2), CREDITCARD CHAR(12 BYTE) ) TABLESPACE EMPLOYEE NOLOGGING NOCOMPRESS NOCACHE ;
-- Thủ tục chèn 10 triệu bản ghi vào bảng với trường thời gian từ 2024-01-01 00:00:01 đến 2024-12-31 23:59:59
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INSERT01',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_start_date TIMESTAMP := TO_TIMESTAMP('2024-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS');
v_end_date TIMESTAMP := TO_TIMESTAMP('2024-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
v_diff_seconds NUMBER;
v_name VARCHAR2(255);
v_creditcard CHAR(12);
v_datetime TIMESTAMP;
BEGIN
-- Calculate the difference in seconds between the start and end dates
SELECT (EXTRACT(DAY FROM (v_end_date - v_start_date)) * 86400 +
EXTRACT(HOUR FROM (v_end_date - v_start_date)) * 3600 +
EXTRACT(MINUTE FROM (v_end_date - v_start_date)) * 60 +
EXTRACT(SECOND FROM (v_end_date - v_start_date)))
INTO v_diff_seconds
FROM dual;
FOR i IN 1..10000000 LOOP
v_name := 'Employee ' || TO_CHAR(i);
v_creditcard := LPAD(TRUNC(DBMS_RANDOM.VALUE(100000000000, 999999999999)), 12, '0');
-- Add a random interval, up to the difference calculated, to the start date
v_datetime := v_start_date + NUMTODSINTERVAL(TRUNC(DBMS_RANDOM.VALUE(0, v_diff_seconds)), 'SECOND');
INSERT /*+ APPEND*/ INTO NGHIEPVU (ID, Name, Datetime, Salary, CreditCard)
VALUES (i, v_name, v_datetime, DBMS_RANDOM.VALUE(10000, 20000), v_creditcard);
IF MOD(i, 500000) = 0 THEN
COMMIT;
DBMS_LOCK.SLEEP(5); -- Pause for 5 seconds after each commit to reduce load
END IF;
END LOOP;
COMMIT; -- Ensure the final commit
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/I. Cắt chuyển lần 1
-- Select, chuyển dữ liệu, đổi tên bảng. Để phù hợp với ví dụ thì tôi lấy mốc cụ thể là 2024-12-31 (Thực tế sẽ lấy theo thời gian thực lúc chạy job):
-- Tạo bảng mới với cấu trúc giống bảng cũ
CREATE TABLE NGHIEPVU_NEW
TABLESPACE EMPLOYEE
NOLOGGING
NOCACHE
AS SELECT * FROM NGHIEPVU WHERE 0=1;
-- Chèn dữ liệu vào bảng mới từ bảng NGHIEPVU trong vòng 3 ngày
INSERT /*+ APPEND*/ INTO NGHIEPVU_NEW SELECT * FROM NGHIEPVU WHERE datetime >= to_date('2024-12-31','yyyy-mm-dd') - 3
order by datetime asc;
RENAME NGHIEPVU TO NGHIEPVU_OLD;
RENAME NGHIEPVU_NEW TO NGHIEPVU;-- Như thường lệ, tôi sẽ không chạy câu lệnh riêng lẻ mà nhét chúng vào một job để không phụ thuộc vào sesion, câu lệnh được thực hiện liền mạch, hạn chế khoảng downtime. Cao cấp hơn, ta có thể cân nhắc chèn các khoảng sleep để giàn trải áp lực lên hệ thống, tránh ảnh hưởng nghiệp vụ online:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'ROTATE_04',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
EXECUTE IMMEDIATE '
CREATE TABLE NGHIEPVU_NEW
TABLESPACE EMPLOYEE
NOLOGGING
NOCACHE
AS SELECT * FROM NGHIEPVU WHERE 0=1
';
EXECUTE IMMEDIATE '
INSERT /*+ APPEND*/ INTO NGHIEPVU_NEW SELECT * FROM NGHIEPVU WHERE datetime >= to_date(''2024-12-31'',''yyyy-mm-dd'') - 3
';
EXECUTE IMMEDIATE 'ALTER TABLE NGHIEPVU RENAME TO NGHIEPVU_OLD';
EXECUTE IMMEDIATE 'ALTER TABLE NGHIEPVU_NEW RENAME TO NGHIEPVU';
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/-- Chạy job gather để lấy thông tin thống kê của bảng NGHIEPVU:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'GATHER_11',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
l_table_name VARCHAR2 (30) := 'NGHIEPVU';
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'OAZ21',
tabname => l_table_name,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate_percent => NULL,
granularity => 'ALL',
cascade => TRUE
);
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/-- Hoàn thành bước cắt chuyển bảng lớn thành bảng nhỏ:
-- Hoàn thành bước này, bảng NGHIEPVU giờ đã có dung lượng nhỏ, không phân mảnh, sẵn sàng phục vụ cho các nghiệp vụ OLTP yêu cầu thời gian phản hồi nhanh 24/7. Dữ liệu cũ trong bảng NGHIEPVU_OLD có thể truncate để lấy lại dung lượng hoặc thường được qui hoạch vào một tablespace có datafile nằm trên phân vùng khác phục vụ lưu trữ.
II. Xoay vòng dữ liệu từ lần 2 trở đi
-- Để minh họa việc xoay vòng dữ liệu, tôi sẽ giả lập thêm dữ liệu phát sinh từ 2025-01-01 đến 2025-01-05. Như vậy bảng NGHIEPVU sẽ có dữ liệu từ 2024-12-28 đến 2025-01-05.
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INSERT02',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_start_date TIMESTAMP := TO_TIMESTAMP('2025-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS');
v_end_date TIMESTAMP := TO_TIMESTAMP('2025-01-05 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
v_diff_seconds NUMBER;
v_name VARCHAR2(255);
v_creditcard CHAR(12);
v_datetime TIMESTAMP;
BEGIN
-- Calculate the difference in seconds between the start and end dates
SELECT (EXTRACT(DAY FROM (v_end_date - v_start_date)) * 86400 +
EXTRACT(HOUR FROM (v_end_date - v_start_date)) * 3600 +
EXTRACT(MINUTE FROM (v_end_date - v_start_date)) * 60 +
EXTRACT(SECOND FROM (v_end_date - v_start_date)))
INTO v_diff_seconds
FROM dual;
FOR i IN 1..60000 LOOP
v_name := 'Employee ' || TO_CHAR(i);
v_creditcard := LPAD(TRUNC(DBMS_RANDOM.VALUE(100000000000, 999999999999)), 12, '0');
-- Add a random interval, up to the difference calculated, to the start date
v_datetime := v_start_date + NUMTODSINTERVAL(TRUNC(DBMS_RANDOM.VALUE(0, v_diff_seconds)), 'SECOND');
INSERT /*+ APPEND*/ INTO NGHIEPVU (ID, Name, Datetime, Salary, CreditCard)
VALUES (i, v_name, v_datetime, DBMS_RANDOM.VALUE(10000, 20000), v_creditcard);
IF MOD(i, 500000) = 0 THEN
COMMIT;
DBMS_LOCK.SLEEP(5); -- Pause for 5 seconds after each commit to reduce load
END IF;
END LOOP;
COMMIT; -- Ensure the final commit
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/-- Bảng đã có dữ liệu đến 2025-01-05 như vậy chúng ta sẽ phải xóa bỏ dữ liệu cũ hơn 3 ngày, tức là trước 2025-01-02.
-- Lúc này bảng NGHIEPVU đã đủ nhỏ để xóa dữ liệu cũ bằng lệnh DELETE mà không làm tăng đột biến lượng Redo log, Archive log gây nguy hiểm cho hệ thống. Thủ tục xóa và insert dữ liệu cũ sang bảng lưu trữ:
CREATE OR REPLACE PROCEDURE rotate_data AS BEGIN -- Chèn dữ liệu từ bảng data sang bảng data_old INSERT /*+ APPEND*/ INTO NGHIEPVU_OLD SELECT * FROM NGHIEPVU WHERE DATETIME < TRUNC(SYSDATE) - 3 ORDER BY DATETIME ASC; -- Xóa dữ liệu quá 3 ngày từ bảng data DELETE FROM NGHIEPVU WHERE DATETIME < TRUNC(SYSDATE) - 3; COMMIT; END; /
-- Đặt job chạy theo tần suất mong muốn, ví dụ như hệ thống sẽ thực hiện rotate vào 23h đêm hàng ngày:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'rotate_data_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN rotate_data; END;',
start_date => TRUNC(SYSDATE) + INTERVAL '23' HOUR, -- Starts today at 23:00
repeat_interval => 'FREQ=DAILY; BYHOUR=23', -- Runs daily at 23:00
end_date => NULL, -- No end date, so it continues indefinitely
enabled => TRUE, -- Start the job immediately
auto_drop => FALSE, -- Do not automatically drop the job after completion
comments => 'Job to rotate data from NGHIEPVU to NGHIEPVU_OLD'
);
END;
/-- Trong ví dụ này, tôi sẽ chạy trực tiếp thủ tục xóa từ mốc thời gian 2025-01-05 và cũ hơn 3 ngày:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'ROTATE_07',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
EXECUTE IMMEDIATE '
-- Chèn dữ liệu từ bảng data sang bảng data_old
INSERT /*+ APPEND*/ INTO NGHIEPVU_OLD
SELECT * FROM NGHIEPVU
WHERE DATETIME < to_date(''2025-01-05'',''yyyy-mm-dd'') - 3
ORDER BY DATETIME ASC
';
EXECUTE IMMEDIATE '
-- Xóa dữ liệu quá 3 ngày từ bảng data
DELETE FROM NGHIEPVU
WHERE DATETIME < to_date(''2025-01-05'',''yyyy-mm-dd'') - 3
';
COMMIT;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/-- Như vậy, trong bảng NGHIEPVU đã không còn dữ liệu trước 2025-01-02:
-- Đối với các hệ thống lớn như core viễn thông, ngân hàng, chứng khoán.. thì lượng log giao dịch sinh ra trong một ngày có thể lên đến hàng TB, gâp áp lực lên hiệu suất I/O và lưu trữ. Việc quay vòng dữ liệu cùng với kĩ thuật partitioning, indexing là chìa khoá giúp duy trì và đảm bảo hiệu suất của các hệ thống OLTP.
-- Câu lệnh stop job nếu cần:
--watch -n 5 'echo "SELECT JOB_NAME,SLAVE_OS_PROCESS_ID,ELAPSED_TIME,CPU_USED FROM USER_SCHEDULER_RUNNING_JOBS;" | sqlplus tuantn/*****@cdb1'
SELECT *--JOB_NAME,SLAVE_OS_PROCESS_ID,ELAPSED_TIME,CPU_USED
FROM USER_SCHEDULER_RUNNING_JOBS;
SELECT * FROM ALL_SCHEDULER_JOB_RUN_DETAILS
ORDER BY LOG_ID DESC;
--GRANT EXECUTE ON DBMS_LOCK TO TUANTN;
BEGIN
-- Stop the job first
DBMS_SCHEDULER.STOP_JOB(
job_name => 'TUANTN.INSERT_EMPLOYEE_S01',
force => TRUE -- Use force to immediately stop the job
);
-- Removed the sleep delay
-- Now drop the job
DBMS_SCHEDULER.DROP_JOB(
job_name => 'TUANTN.INSERT_EMPLOYEE_S01'
);
END;
/====
Nhận xét
Đăng nhận xét