Sử dụng Bulk và Parallel PL/SQL để cập nhập dữ liệu lớn

Anh bạn làm cùng công ty có nhờ tôi chuyển giúp dữ liệu từ một bảng trên một DB cũ sang DB mới vừa cài đặt. Dữ liệu cần chuyển khoảng 2.7 tỷ bản ghi. DB cũ thì chỉ có 01 máy chủ khá cũ. DB mới khỏe hơn, chạy 02 máy chủ ảo hóa, cài Oracle 11g RAC. Anh ta cũng đã thử nhiều cách và cách nào cũng không ổn. Nếu insert trực tiếp qua dblink thì đòi hỏi rollback segment rất lớn trong khi dung lượng lưu trữ của DB khá nhỏ. Nếu dùng tool exp/imp thì lại quá lâu, mất cả ngày để export ra rồi lại cả ngày import ngược vào DB mới, chưa kể không có chỗ để lưu file dmp nữa.

Tôi hăm hở vào làm và cuối cùng cũng "tắc tị". Nào là create table... as, bulk/forall, sqlplus copy... Tất cả không ăn thua. Chán, tôi vào sục xạo trên mạng xem có cách nào hay không và tình cờ tìm được một bài viết rất hay, giới thiệu và so sánh hiệu năng của nhiều phương thức update dữ liệu (http://www.orafaq.com/node/2450). Mặc dù không hoàn toàn giống như yêu cầu của bạn tôi nhưng có thể tham khảo được. Tôi lấy code hướng dẫn thực hiện cơ chế Parallel PL/SQL (method 8) và sửa lại. Kết quả tổng thời gian để di chuyển toàn bộ chỉ mất 8h, tương đương với khoảng 5.5 triệu bản ghi/phút. Thật ra tốc độ này đâu có nhanh nếu trên DB local nhưng nên nhớ là ta cập nhập qua dblink và kết nối mạng giữa 2 DB chỉ là đường 100MBps. Thực sự quá khả quan!!!

Phương thức thực hiện cơ bản là sử dụng bulk/forall lấy lượng lớn dữ liệu mỗi lần và insert vào bảng đích. Cách này rất nhiều anh em ta biết, tuy nhiên cái đặc biệt ở đây là sử dụng Parallel PL/SQL để xử lý song song việc này.

Rất hay! Chia sẻ cùng mọi người.

Đầu tiên, ta tạo bảng đích trên DB mới có cấu trúc giống hệt bảng cũ. Bảng được partition theo ngày, sử dụng cơ chế nén để tiết kiệm không gian lưu trữ.

create table rep_cdr (   rep_date              date,   ... -- có khá nhiều field nữa, vì lý do bảo mật tôi không đưa lên đây được. ) partition by range (rep_date) (   partition rep_cdr_01012015 values less than (to_date('2015-01-02', 'yyyy-mm-dd')) ) tablespace cdr compress nologging;

Sử dụng đoạn script sau để add thêm các partition:

declare   l_start_date date:= to_date('02/01/2015', 'dd/mm/yyyy');   l_end_date   date:= to_date('31/12/2015', 'dd/mm/yyyy');   l_date       date:= l_start_date;   l_partition_name  varchar2(30); begin   l_date:= l_start_date;   while l_date <= l_end_date loop     l_partition_name:= 'rep_cdr_' || to_char(l_date, 'ddmmyyyy');     execute immediate       'alter table rep_cdr add partition ' || l_partition_name || ' ' ||       'values less than (to_date(''' || to_char(l_date + 1, 'yyyy-mm-dd') || ''', ''yyyy-mm-dd''))';     l_date:= l_date + 1;   end loop; end; /

Viết function để insert dữ liệu, sử dụng cơ chế Parallel PL/SQL. Trong function này ta sẽ sử dụng bulk để lấy 1 triệu bản ghi mỗi lần và sử dụng forall để insert dữ liệu vào bảng đích.

create or replace type number_tab as table of integer; / create or replace function insert_rep_cdr (   c_data in sys_refcursor) return number_tab parallel_enable (partition c_data by any) pipelined is   pragma autonomous_transaction;   l_scope logger_logs.scope%type:= 'insert_rep_cdr';   type t_data_tab is table of rep_cdr%rowtype;   l_data_tab t_data_tab;   cnt integer := 0; begin   loop     fetch c_data     bulk collect into l_data_tab limit 1000000;     exit when l_data_tab.count = 0;    logger.log_information('Inserting...', l_scope, null);    forall i in 1..l_data_tab.count       insert /*+ append */ into rep_cdr values l_data_tab(i);     logger.log_information(sql%rowcount || ' rows inserted.', l_scope, null);     commit;     cnt:= cnt + l_data_tab.count;   end loop;   close c_data;   pipe row(cnt);   return; exception   when OTHERS then     logger.log_error('Unhandled exception', l_scope, null); end; /

Tôi thường sử dụng mã nguồn mở PL/Logger để ghi lại các thông tin xử lý trong PL/SQL. Đoạn code trên cũng vậy. Các bạn có thể bỏ qua các câu lệnh ghi log.

Lát nữa chúng ta sẽ sử dụng câu lệnh select với hint parallel để thực hiện song song function này. Tuy nhiên vấn đề ở đây là bình thường Oracle đâu có cho phép thay đổi dữ liệu trong câu lệnh query. Để làm được điều này chúng ta lưu ý từ khóa “autonomous_transaction”. Với chỉ thị này trong PL/SQL, Oracle sẽ cho phép khởi tạo một transaction mới từ một transaction khác. Và transaction mới sẽ độc lập với transaction ban đầu. Cụ thể ở đây, transaction ban đầu của ta là câu lệnh select, sau đó ta khởi tạo transaction để insert dữ liệu từ câu lệnh select. Đây chính là mẹo để chúng ta vượt qua hạn chế trên của Oracle. Rất hay!!!

Hệ thống mạng ở chỗ tôi được thiết lập FW để kiểm soát kết nối giữa các hệ thống máy chủ và máy trạm. Nếu kết nối mà idle time quá 30 phút thì sẽ bị ngắt ra, do đó để tránh bị interrupt giữa chừng, tôi viết thêm đoạn script tạo job trên DB để thực hiện function này. Job này thực hiện 1 lần, sau khi xong sẽ tự động xóa khỏi DB.

declare   x number; begin   sys.dbms_job.submit(     job       => x,     what      =>       'declare         l_rec_count number;       begin         select /*+ parallel(a 8) */ column_value into l_rec_count         from table(insert_rep_cdr (cursor(select * from rep_cdr@dblink))) a;       end;',     next_date => sysdate + 1/86400, -- thực hiện tại thời điểm sysdate + 1 second.     interval  => 'null',     no_parse  => false   );   sys.dbms_output.put_line('job number is: ' || to_char(x));   commit; end; /

Chúng ta để ý câu lệnh "select /*+ parallel(a 8) */..." ở trên. Ta sử dụng hint parallel(a 8) để thực hiện song song cho function insert_rep_cdr. Và đầu vào của function này chính là câu lệnh select dữ liệu từ bảng nguồn qua dbink "select * from rep_cdr@dblink".

Như vậy là ta đã running rồi, giờ chỉ ngồi chờ kết quả thôi. :-)

Như ở trên tôi có nói, tôi sử dụng PL/Logger để ghi log xử lý. Nhờ đó, chúng ta có thể theo dõi quá trình thực hiện thông qua câu lệnh sau:

select * from logger_logs where scope = 'insert_rep_cdr' order by time_stamp desc;

Tóm lại, phương thức này giúp ta không phải tạo rollback thật lớn (vì 1 triệu bản ghi đã commit rồi), ảnh hưởng đến tải DB cũng thấp, chẳng phải phiền phức gì nhiều (exp/imp), và hơn hết tốc độ lại rất đảm bảo.

Chúc vui!