[Oracle DB] Chống phân mảnh bảng bằng phương pháp move partititon
Hiện tượng bảng bị phân mảnh thường xảy ra khi bảng đã trải qua các thao tác update/ inset/ delete phần lớn dữ liệu trong bảng. Điều này có thể nhận biết qua việc dung lượng của tablespace lớn hơn tổng dung lượng các bảng bên trong nó, không thể resize tablespace này xuống dưới mức High water mark hiện hành. Khi này việc shrink bảng cũng rất khó vì có thể gây chậm/ treo hệ thống, vậy làm thế nào để lấy lại dung lượng đã bị tiêu tốn bới việc phân mảnh?
==
Như chúng ta đã biết, để lấy lại dung lượng thì chúng ta có thể move các partition của bảng sang tablespace khác (datafile khác) nhưng mặt khác, chúng ta hoàn toàn có thể move partition ngay trong tablespace hiện tại, bảng vẫn nằm trên datafile hiện hành. Tất nhiên quá trình di chuyển này sẽ khiến datafile tăng dung lượng nhưng sau khi hoàn thành, ta có thể resize datafile để lấy lại dung lượng nếu cần. Để rõ ràng hơn ta có thể xem xét ví dụ dưới đây.
-- Tạo tablespase, tạo bảng với partition ban đầu, tạo tự động các partition tiếp theo, insert 10 triệu bản ghi vào bảng đã tạo:
--Tạo tablespace
CREATE TABLESPACE DATATEST
DATAFILE '/u01/app/oracle/oradata/CDB1/datatest.dbf' SIZE 512M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
NOLOGGING FLASHBACK OFF;
--Thủ tục tạo bảng mới
CREATE TABLE TUANTN.EMPLOYEES
(
ID NUMBER,
NAME VARCHAR2(255 BYTE),
DATETIME TIMESTAMP(6),
SALARY NUMBER(10,2),
CREDITCARD CHAR(12 BYTE)
)
TABLESPACE DATATEST
NOLOGGING
NOCOMPRESS
NOCACHE
PARTITION BY RANGE (DATETIME)
(
PARTITION DATA202312 VALUES LESS THAN (TO_DATE('2023-12', 'YYYY-MM'))
TABLESPACE DATATEST
)
;
--Tao partition theo thang
DECLARE
v_start_year NUMBER(4) := 2024; -- Nam b?t d?u
v_end_year NUMBER(4) := 2024; -- Nam k?t thúc
v_tablename VARCHAR2(50) := 'EMPLOYEES'; -- Tên b?ng
v_date_from DATE; -- Ngày b?t d?u
v_date_to DATE; -- Ngày k?t thúc
v_month_start DATE;
v_month_end DATE;
v_tablespace VARCHAR2(50) := 'DATATEST'; -- Tablespace
BEGIN
-- L?p qua t?ng nam t? nam b?t d?u d?n nam k?t thúc
FOR y IN v_start_year..v_end_year LOOP
-- L?p qua t?ng tháng trong nam
FOR m IN 1..12 LOOP
-- Ngày d?u tiên c?a tháng
v_month_start := TO_DATE(y || '-' || LPAD(m, 2, '0') || '-01', 'YYYY-MM-DD');
-- Ngày cu?i cùng c?a tháng
v_month_end := LAST_DAY(v_month_start);
-- Thêm partition cho tháng hi?n t?i
EXECUTE IMMEDIATE
'ALTER TABLE ' || v_tablename || ' ADD PARTITION DATA' || TO_CHAR(v_month_start, 'YYYYMM')
|| ' VALUES LESS THAN (TO_DATE('''
|| TO_CHAR(v_month_end + 1, 'YYYY-MM-DD')
|| ' 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')) TABLESPACE ' || v_tablespace;
END LOOP;
END LOOP;
END;
/
--Insert 10 triệu bản ghi
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INSERT_01',
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 EMPLOYEES (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;
/-- Sau khi hoàn thành quá trình ta được một bảng có dung lượng như sau, khớp với dung lượng datafile:
--Xem dung lượng bảng parrtition
SELECT segment_name "Table Name", --partition_name "Partition Name",
SUM(bytes/1024/1024) "Size (MB)"
FROM user_segments
WHERE segment_type = 'TABLE PARTITION'
AND segment_name = 'EMPLOYEES'
GROUP BY segment_name;
==
-- Xóa lượng lớn bản ghi trong bảng EMPLOYEES. Lưu ý, đây chỉ là ví dụ nên tôi dùng lệnh DELETE. Trong thực tế, tuyệt đối không dùng lệnh DELETE để xóa số lượng lớn bản ghi, nếu cần thì nên sử dụng phương án tạo bảng mới -> truncate, drop purge bảng cũ để lấy lại dung lượng:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'DELETE_05',
job_type => 'PLSQL_BLOCK',
job_action => q'[
EXECUTE IMMEDIATE 'DELETE FROM EMPLOYEES WHERE ROWNUM <= 9000000';
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/-- Hiện tại, bảng chỉ còn 1 triệu bản ghi nhưng dung lượng bảng thì không đổi, hay High water mark vẫn ở mức tương ứng với 10 triệu bản ghi:
==
-- Bây giờ tôi sẽ dùng phương pháp move partition để lấy lại phần dung lượng đã bị tiêu tốn vì phân mảnh trong bảng:
-- Xem các partition:
--Xem cac parrtition
SELECT segment_name "Table Name", partition_name "Partition Name",
bytes/1024/1024 "Size (MB)"
FROM user_segments
WHERE segment_type = 'TABLE PARTITION'
AND segment_name = 'EMPLOYEES';
-- Thủ tục move partition trong chính tablespace hiện hành, lưu ý tôi sử dụng từ khóa ONLINE để bảng vẫn có thể được truy xuất trong quá trình di chuyển:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'MOVE_PARTITION_01',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
-- Di chuy?n m?i phân vùng t?i tablespace m?i
FOR part_rec IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = 'EMPLOYEES') LOOP
EXECUTE IMMEDIATE 'ALTER TABLE EMPLOYEES MOVE PARTITION ' || part_rec.partition_name || ' ONLINE';
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/-- Khi này, dung lượng bảng đã giảm còn 152MB so với 664MB ban đầu:
-- Có một câu hỏi thú vị ở đây là, tuy datafile đã bị mở rộng để phục vụ quá trình move partition nhưng nếu muốn, bạn hoàn toàn có thể resize nó về mức dung lượng khi bảng có 10 triệu bản ghi ~ 670MB, vậy nếu muốn resize datafile này về mức dung lượng hiện tại của bảng 1 triệu bản ghi ~ 152MB thì làm thế nào? Tôi sẽ viết tiếp bài toán này.
-- Câu trả lời rất đơn giản, tạo bảng mới. Với bảng có partition thì dùng thủ tục chuyển bảng có partition:
--Tạo bảng mới cùng cấu trúc với bảng cũ
CREATE TABLE TUANTN.EMPLOYEES_NEW
TABLESPACE DATATEST
NOLOGGING
NOCOMPRESS
NOCACHE
PARTITION BY RANGE (DATETIME) ( PARTITION DATA202312 VALUES LESS THAN (TO_DATE('2023-12', 'YYYY-MM')) TABLESPACE DATATEST )
AS
SELECT * FROM EMPLOYEES WHERE 1=0;
--Tao partition theo thang
DECLARE
v_start_year NUMBER(4) := 2024; -- Nam b?t d?u
v_end_year NUMBER(4) := 2024; -- Nam k?t thúc
v_tablename VARCHAR2(50) := 'EMPLOYEES_NEW'; -- Tên b?ng
v_date_from DATE; -- Ngày b?t d?u
v_date_to DATE; -- Ngày k?t thúc
v_month_start DATE;
v_month_end DATE;
v_tablespace VARCHAR2(50) := 'DATATEST'; -- Tablespace
BEGIN
-- L?p qua t?ng nam t? nam b?t d?u d?n nam k?t thúc
FOR y IN v_start_year..v_end_year LOOP
-- L?p qua t?ng tháng trong nam
FOR m IN 1..12 LOOP
-- Ngày d?u tiên c?a tháng
v_month_start := TO_DATE(y || '-' || LPAD(m, 2, '0') || '-01', 'YYYY-MM-DD');
-- Ngày cu?i cùng c?a tháng
v_month_end := LAST_DAY(v_month_start);
-- Thêm partition cho tháng hi?n t?i
EXECUTE IMMEDIATE
'ALTER TABLE ' || v_tablename || ' ADD PARTITION DATA' || TO_CHAR(v_month_start, 'YYYYMM')
|| ' VALUES LESS THAN (TO_DATE('''
|| TO_CHAR(v_month_end + 1, 'YYYY-MM-DD')
|| ' 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')) TABLESPACE ' || v_tablespace;
END LOOP;
END LOOP;
END;
/
--insert dữ liệu từ bảng này sang bảng khác theo từng lô (bath)
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INSERT_01',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
CURSOR cur IS
SELECT * FROM EMPLOYEES;
cnt NUMBER := 0;
BEGIN
FOR rec IN cur LOOP
INSERT /*+ APPEND*/ INTO EMPLOYEES_NEW (ID,NAME,DATETIME,SALARY,CREDITCARD)
VALUES (rec.ID,REC.NAME,REC.DATETIME,REC.SALARY,REC.CREDITCARD);
cnt := cnt + 1;
IF cnt MOD 100000 = 0 THEN
COMMIT;
DBMS_LOCK.SLEEP(3);
END IF;
END LOOP;
COMMIT; -- To ensure any remaining rows are committed
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/
--Đổi tên bảng
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE EMPLOYEES RENAME TO EMPLOYEES_OLD';
EXECUTE IMMEDIATE 'ALTER TABLE EMPLOYEES_NEW RENAME TO EMPLOYEES';
END;
/
--Xóa bảng cũ
TRUNCATE TABLE EMPLOYEES_OLD;
DROP TABLE EMPLOYEES_OLD PURGE;-- Giờ hãy xem lại dung lượng bảng sau quá trình chuyển, chỉ còn 72MB cho 1 triệu bản ghi:
-- Nếu muốn, bạn có thể resize cả datafile. Từ 670MB còn 234MB:
===
Nhận xét
Đăng nhận xét