Tự động hóa dọn dẹp phân vùng trong Oracle với PL/SQL và DBMS Scheduler

Quản lý phân vùng trong cơ sở dữ liệu Oracle là một yếu tố quan trọng, đặc biệt khi làm việc với các bảng lớn. Việc tự động hóa quy trình dọn dẹp phân vùng không chỉ giúp tiết kiệm thời gian mà còn cải thiện hiệu suất của cơ sở dữ liệu. Trong bài viết này, chúng ta sẽ khám phá cách tự động hóa quy trình dọn dẹp phân vùng bằng cách sử dụng PL/SQL và DBMS Scheduler.

1. Giới thiệu về quy trình dọn dẹp phân vùng

Quy trình bắt đầu bằng việc đọc từ bảng cấu hình CMN_DROP_PARTITION, xác định các bảng phân vùng cần dọn dẹp, số lượng phân vùng nên giữ lại và những phân vùng nào cần được loại trừ. Đối với mỗi bảng đang hoạt động, quy trình sẽ lấy tên chủ sở hữu bảng, tên bảng và số lượng phân vùng cần giữ lại. Sau đó, nó truy vấn các view từ điển dữ liệu của Oracle (dba_tab_partitions) để lấy tất cả các phân vùng có sẵn cho bảng đó theo thứ tự tăng dần.

Khi những phân vùng đã được liệt kê, quy trình sẽ kiểm tra các phân vùng bị loại trừ được cấu hình để đảm bảo rằng chúng không bao giờ bị xóa, ngay cả khi chúng nằm ngoài khoảng thời gian giữ lại. Sau đó, nó tính toán số lượng phân vùng cần giữ lại, đảm bảo rằng các phân vùng mới nhất (dựa trên vị trí phân vùng) sẽ không bị xóa. Các phân vùng cũ hơn vượt quá giới hạn giữ lại sẽ được xác định và đánh dấu là ứng cử viên để xóa. Sử dụng SQL động, nó tạo ra và thực thi các câu lệnh ALTER TABLE DROP PARTITION cho từng phân vùng lỗi thời này.

1.1. Cấu trúc quy trình

Dưới đây là cấu trúc của quy trình dọn dẹp:

Tạo quy trình PL/SQL: Quy trình CMN_DROP_PARTITIONS_PR sẽ quét tất cả các bảng phân vùng được cấu hình cho dọn dẹp.
Xử lý lỗi: Bất kỳ lỗi nào sẽ được ghi lại nhưng không làm gián đoạn quy trình tổng thể, đảm bảo rằng một phân vùng không tốt không dừng quy trình dọn dẹp.

plsql Copy

CREATE OR REPLACE PROCEDURE CMN_OWNR.CMN_DROP_PARTITIONS_PR IS
    v_Dropstr_Sql varchar2(4000);
BEGIN
    FOR i IN (
        SELECT * FROM (
            SELECT u.table_name,
                   partition_name,
                   partition_position,
                   RANK() OVER(PARTITION BY u.table_name ORDER BY u.partition_position DESC) rnk,
                   cdp.no_of_part_to_retn retn_prt_days
            FROM user_tab_partitions u, cmn_drop_partition cdp
            WHERE cdp.table_name = u.table_name
              AND cdp.exclude_partition != u.partition_name
              AND cdp.active = 'Y')
        WHERE rnk > retn_prt_days
        ORDER BY rnk
    ) LOOP
        BEGIN
            v_Dropstr_Sql := 'ALTER TABLE ' || i.table_name || ' DROP PARTITION ' || i.partition_name || ' UPDATE INDEXES';
            EXECUTE IMMEDIATE v_Dropstr_Sql;
            v_Dropstr_Sql := '';
        EXCEPTION WHEN OTHERS THEN
            ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR',i.table_name||':'||i.partition_name,NULL,SQLERRM,NULL,NULL);
        END;
    END LOOP;

ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR','Drop Partition',NULL,'CMN PARTITIONS DROP JOB GOT COMPLETED SUCCESSFULLY AT:'||systimestamp,NULL,NULL);
EXCEPTION WHEN OTHERS THEN
    ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR','Main EXCP',NULL,SQLERRM,NULL,NULL);
END CMN_DROP_PARTITIONS_PR;

2. Bảng cấu hình `CMN_DROP_PARTITION`

Bảng cấu hình CMN_DROP_PARTITION xác định các quy tắc dọn dẹp. Đối với mỗi bảng, chúng ta có thể chỉ định số lượng phân vùng cần giữ lại, các phân vùng nào cần bỏ qua trong quá trình xóa và trạng thái hoạt động của quy tắc. Điều này giúp quy trình trở nên linh hoạt và dựa trên dữ liệu.

sql Copy

CREATE TABLE CMN_DROP_PARTITION (
  table_name         VARCHAR2(100),
  exclude_partition  VARCHAR2(100),
  table_owner        VARCHAR2(15),
  no_of_part_to_retn NUMBER(5),
  active             VARCHAR2(1),
  created_date       TIMESTAMP(6),
  created_by         VARCHAR2(30),
  modified_date      TIMESTAMP(6),
  modified_by        VARCHAR2(30)
);

-- Ví dụ mẫu
INSERT INTO CMN_DROP_PARTITION (table_name, exclude_partition, 
active, table_owner, no_of_part_to_retn, created_date, created_by)
VALUES('CMN_UPLD_DATA', 'P_PREDEC24','Y', 'CMN_OWNR',31, SYSDATE, 'ACCELYA-IT');
INSERT INTO CMN_DROP_PARTITION (table_name, exclude_partition, 
active, table_owner, no_of_part_to_retn, created_date, created_by)
VALUES('CMN_LOG_REQ_RESP', 'P_PRE24','Y', 'CMN_OWNR',7, SYSDATE, 'ACCELYA-IT');
COMMIT;

2.1. Giải thích ví dụ

Đối với CMN_UPLD_DATA, 31 phân vùng cuối cùng được giữ lại, nhưng phân vùng P_PREDEC24 bị loại trừ khỏi quá trình xóa.
Đối với CMN_LOG_REQ_RESP, 7 phân vùng cuối cùng được giữ lại, loại trừ P_PRE24.

3. Lên lịch với DBMS Scheduler

Cuối cùng, quy trình được lên lịch sử dụng DBMS Scheduler của Oracle. Chúng ta tạo một job sẽ chạy quy trình dọn dẹp mỗi thứ Hai lúc 3 giờ sáng, đảm bảo rằng quy trình dọn dẹp diễn ra định kỳ mà không cần can thiệp thủ công.

plsql Copy

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'CMN_DROP_PARTITION_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'cmn_drop_partitions_pr;',
    start_date      => systimestamp,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=03; BYMINUTE=00; BYSECOND=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB To drop older partition from CMN tables');
END;

3.1. Lợi ích của việc lên lịch

Job này đảm bảo quy trình dọn dẹp chạy vào thời gian cố định, giữ cho cơ sở dữ liệu gọn nhẹ và các phân vùng được kiểm soát.

4. Các lưu ý và mẹo hiệu suất

Hiểu rõ cơ sở dữ liệu của bạn: Trước khi triển khai giải pháp này, hãy đảm bảo bạn hiểu rõ cách tổ chức và sử dụng phân vùng trong cơ sở dữ liệu của mình.
Theo dõi hiệu suất: Sử dụng các công cụ giám sát để theo dõi hiệu suất của cơ sở dữ liệu trước và sau khi thực hiện quy trình dọn dẹp.
Kiểm tra thường xuyên: Thực hiện kiểm tra định kỳ để đảm bảo rằng quy trình dọn dẹp hoạt động như mong đợi và không gây ra vấn đề gì trong việc truy cập dữ liệu.

5. Kết luận

Với việc triển khai giải pháp này, chúng ta đã đạt được một chiến lược dọn dẹp phân vùng tự động, đảm bảo rằng chỉ những phân vùng cần thiết nhất được giữ lại. Bảng cấu hình cung cấp tính linh hoạt và kiểm soát mà không cần thay đổi mã nguồn, trong khi job DBMS Scheduler tự động hóa việc thực hiện trong thời gian thấp điểm. Ba thành phần này cùng nhau tạo ra một khung bảo trì đáng tin cậy, cải thiện hiệu suất, giảm chi phí lưu trữ và tối thiểu hóa can thiệp thủ công trong quản lý phân vùng. Hãy thử nghiệm và áp dụng phương pháp này để cải thiện quản lý cơ sở dữ liệu của bạn ngay hôm nay!

Tự động hóa dọn dẹp phân vùng trong Oracle với PL/SQL