Trong phần trước, chúng ta đã tìm hiểu về các hoạt động như theo dỗi việc sử dụng tablespace trong oracle databse, cũng như cơ chế hoạt động, lợi ích và những lưu ý trong việc shrinking segments để quản lý bộ nhớ lưu trữ trong oracle database.

Tiếp tục, trong phần này cũng sẽ nói về các hoạt động quản lý không gian trống trong db oracle bằng phương pháp shrinking segments. Ngoài ra, trong phần này mình sẽ trình bài về chế độ Using resumable Space allocation và Resuming Suspeded statements chi tiết hơn.

Sử dụng segment Advisor trong DB Oracle

Bộ phân tích segment (Segment Advisor) xác định các segment có không gian có sẵn để thu hồi. Nó thực hiện phân tích bằng cách xem xét các thống kê về sử dụng và tăng trưởng trong Automatic Workload Repository (AWR), và bằng cách lấy mẫu dữ liệu trong segment. Nó được cấu hình để chạy tự động theo khoảng thời gian đều đặn, và bạn cũng có thể chạy nó khi cần (thủ công). Việc chạy Bộ phân tích segment theo lịch trình định kỳ được gọi là Bộ phân tích segment tự động (Automatic Segment Advisor).

Sau khi đưa ra các khuyến nghị, bạn có thể chọn thực hiện các khuyến nghị đó. Bộ phân tích thu hẹp (shrink advisor) có thể được triệu hồi ở mức segment hoặc mức tablespace.

Sử dụng Enterprise Manager Database Cloud Control để triệu hồi Bộ phân tích segment. Bạn có thể truy cập Bộ phân tích segment từ một số vị trí trong Enterprise Manager Cloud Control:

Enterprise Manager Cloud Control cung cấp tùy chọn để chọn đầu vào khác nhau và lên lịch một công việc gọi Bộ phân tích segment để nhận lời khuyên về thu hẹp. Công cụ Hướng dẫn Bộ phân tích segment có thể được triệu hồi mà không có ngữ cảnh, trong ngữ cảnh của một tablespace, hoặc trong ngữ cảnh của một đối tượng Schema.

Bộ phân tích segment đưa ra các khuyến nghị dựa trên phân tích theo mẫu, thông tin lịch sử và xu hướng tăng trưởng trong tương lai.

Tự động hóa Segment Advisor

Bộ phân tích segment tự động (Automatic Segment Advisor) được kích hoạt thông qua một công việc lập lịch (Scheduler job) được cấu hình để chạy trong khung thời gian bảo trì mặc định. Khung thời gian bảo trì mặc định được thiết lập trong Scheduler và ban đầu được xác định như sau:

1. Trong các đêm trong tuần, từ thứ Hai đến thứ Sáu, từ 10:00 PM đến 2:00 AM (tổng cộng 4 giờ mỗi đêm).
2. Trong cuối tuần, vào buổi sáng thứ Bảy và Chủ Nhật, lúc 6:00 AM và kéo dài trong 20 giờ mỗi ngày.

Bộ phân tích segment tự động không phân tích tất cả các đối tượng trong cơ sở dữ liệu. Thay vào đó, nó xem xét thống kê cơ sở dữ liệu, lấy mẫu dữ liệu từ các segment và sau đó chọn các đối tượng sau để phân tích:

1. Tablespaces vượt quá ngưỡng không gian quan trọng hoặc cảnh báo.
2. Các segment có hoạt động nhiều nhất.
3. Các segment có tỷ lệ tăng trưởng cao nhất.

Nếu một đối tượng được chọn để phân tích nhưng khung thời gian bảo trì kết thúc trước khi Bộ phân tích segment tự động xử lý đối tượng đó, đối tượng sẽ được bao gồm trong lần chạy Bộ phân tích segment tự động tiếp theo. Bạn không thể thay đổi danh sách tablespace và segment mà Bộ phân tích segment tự động chọn để phân tích, tuy nhiên bạn có thể bật hoặc tắt công việc Bộ phân tích segment tự động, thay đổi thời gian chạy và điều chỉnh việc sử dụng tài nguyên hệ thống của Bộ phân tích segment tự động.

Tiến hành hoạt động Shringking Segments bằng câu lệnh SQL

Do một hoạt động shrink có thể làm thay đổi các ROWID trong các segment heap-organized, bạn phải kích hoạt chuyển động hàng (row movement) trên segment tương ứng trước khi thực hiện hoạt động shrink trên segment đó. Mặc định, chuyển động hàng bị vô hiệu hóa ở mức segment. Để kích hoạt chuyển động hàng, ta sử dụng ENABLE ROW MOVEMENT trong câu lệnh CREATE TABLE hoặc ALTER TABLE. Ví dụ đầu tiên trong hình trên là đang minh họa cho việc này.

Sử dụng câu lệnh ALTER để thực hiện hoạt động shrink trên một đối tượng. Loại đối tượng có thể là bảng (heap-organized hoặc index-organized), phân vùng, phân phối con, segment LOB (dữ liệu và segment chỉ mục), chỉ mục, materialized view hoặc materialized view log.

Sử dụng SHRINK SPACE để thu nhỏ không gian trong một segment. Nếu chỉ định CASCADE, hành vi thu nhỏ sẽ được truyền xuống tất cả các segment phụ thuộc hỗ trợ hoạt động shrink, trừ materialized view, LOB indexes và bảng ánh xạ IOT.

Ví dụ thứ hai minh họa SHRINK SPACE.Trong một segment chỉ mục, hoạt động shrink sẽ kết hợp các chỉ mục trước khi thu gọn dữ liệu.

Ví dụ số 3 cho thấy một lệnh thu nhỏ segment LOB, với giả định cột RESUME là một CLOB.

Ví dụ số 4 cho thấy một lệnh thu nhỏ segment IOT overflow thuộc về bảng EMPLOYEES.

Shrinking Segments bằng Enterprise Manager

Bạn có thể thu nhỏ thủ công các segment riêng lẻ liên kết với các đối tượng cơ sở dữ liệu cụ thể bằng cách sử dụng Enterprise Manager Cloud Control. Trên trang Tables, chọn bảng của bạn, sau đó chọn Shrink Segment trong danh sách thả xuống Actions. Tiếp theo, nhấp vào nút Go. Điều này đưa bạn đến trang Shrink Segment, nơi bạn có thể chọn các segment phụ thuộc để thu nhỏ. Bạn có thể chọn chỉ thu gọn hoặc thu gọn và giải phóng không gian. Bạn cũng có thể chọn tùy chọn CASCADE.

Khi hoàn thành, nhấp vào liên kết Continue. Điều này gửi các lệnh thu nhỏ dưới dạng công việc đã được lên lịch.

Quản lý phân bổ không gian có thể tiếp tục - Managing Resumable Space Allocation

Trong Oracle Database, có một tính năng gọi là "resumable space allocation" cho phép tạm dừng và tiếp tục thực thi các hoạt động cơ sở dữ liệu lớn khi xảy ra lỗi không đủ không gian. Thay vì trả về lỗi cho người dùng, tính năng này cho phép bạn thực hiện các biện pháp khắc phục và tiếp tục thực thi sau khi lỗi được sửa chữa.

Các câu lệnh được thực thi trong chế độ có thể tiếp tục chỉ khi tính năng này được kích hoạt cho hệ thống hoặc phiên làm việc. Khi xảy ra lỗi không đủ không gian, câu lệnh sẽ bị tạm dừng và giao dịch tương ứng cũng bị tạm dừng. Điều này đảm bảo rằng tất cả các tài nguyên giao dịch được giữ trong suốt quá trình tạm dừng và khi câu lệnh được tiếp tục thực thi.

Khi lỗi không đủ không gian được khắc phục (ví dụ như thông qua sự can thiệp của người dùng hoặc giải phóng không gian bộ nhớ đệm bởi các truy vấn khác), câu lệnh sẽ tự động tiếp tục thực thi. Một khoảng thời gian tạm dừng đã được định nghĩa, và nếu câu lệnh bị tạm dừng trong thời gian này mà không khắc phục được lỗi, nó sẽ kết thúc và trả về lỗi cho người dùng.

Các lệnh có thể tiếp tục bị tạm dừng khi xảy ra các điều kiện như không đủ không gian, đạt tới giới hạn số extent tối đa, hoặc vượt quá hạn ngạch không gian. Sau khi lỗi được khắc phục, câu lệnh có thể tiếp tục thực thi và có thể bị tạm dừng và tiếp tục nhiều lần trong quá trình thực thi.

Lưu ý: Lỗi đạt tới giới hạn số extent tối đa chỉ xảy ra với các tablespace được quản lý bằng cách sử dụng phương pháp quản lý từ điển (dictionary-managed tablespaces).

Chế độ cấp phát không gian có thể tiếp tục - Using resumable Space allocation

Chế độ cấp phát không gian có thể tiếp tục (resumable space allocation) chỉ hoạt động khi các câu lệnh được thực thi trong phiên làm việc đã kích hoạt chế độ tiếp tục (resumable mode). Có hai cách để kích hoạt và vô hiệu hóa chế độ này:

• Sử dụng lệnh ALTER SESSION ENABLE RESUMABLE.
• Thiết lập tham số khởi tạo RESUMABLE_TIMEOUT thành một giá trị khác không thông qua câu lệnh ALTER SESSION hoặc ALTER SYSTEM.

Khi kích hoạt chế độ tiếp tục, bạn có thể chỉ định một khoảng thời gian tạm dừng, sau đó câu lệnh tạm dừng sẽ kết thúc với lỗi nếu không có sự can thiệp nào. Tham số khởi tạo RESUMABLE_TIMEOUT xác định số giây trước khi quá thời gian tạm dừng. Bạn cũng có thể chỉ định khoảng thời gian tạm dừng bằng câu lệnh sau:

SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;

Giá trị TIMEOUT sẽ có hiệu lực cho đến khi nó được thay đổi bởi một câu lệnh ALTER SESSION ENABLE RESUMABLE khác, thay đổi bằng cách khác hoặc phiên làm việc kết thúc.

Khi sử dụng ENABLE RESUMABLE TIMEOUT để kích hoạt chế độ tiếp tục, khoảng thời gian tạm dừng mặc định là 7.200 giây, tương đương 2 giờ.

Bạn cũng có thể đặt tên cho các câu lệnh có thể tiếp tục.

Ví dụ: ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'multitab insert';

Tên của câu lệnh được sử dụng để xác định câu lệnh có thể tiếp tục trong các view DBA_RESUMABLE và USER_RESUMABLE. 

Ví dụ: SELECT name, sql_text FROM user_resumable;

Để tự động cấu hình các thiết lập cho các câu lệnh có thể tiếp tục trong các phiên làm việc cụ thể, bạn có thể tạo và đăng ký một LOGON trigger cấp cơ sở dữ liệu (database-level) mà sẽ thay đổi phiên làm việc của người dùng.

Trigger này sẽ thực hiện lệnh để kích hoạt các câu lệnh có thể tiếp tục cho phiên làm việc, chỉ định khoảng thời gian tạm dừng và gán tên cho các câu lệnh có thể tiếp tục được thực thi bởi phiên làm việc.

Vì các câu lệnh bị tạm dừng có thể làm tắc một số tài nguyên hệ thống, người dùng phải được cấp quyền RESUMABLE trước khi được phép kích hoạt cấp phát không gian có thể tiếp tục và thực thi các câu lệnh có thể tiếp tục.

Tiếp tục thực hiện các câu lệnh bị tạm dừng - Resuming Suspeded statements

Ví dụ:

1. Một câu lệnh INSERT gặp lỗi thông báo bảng đã đầy.
2. Câu lệnh INSERT bị tạm dừng và không có lỗi được truyền cho khách hàng.
3. Tùy chọn, một trigger AFTER SUSPEND được thực thi.
4. Tùy chọn, ngoại lệ SQLERROR được kích hoạt để hủy bỏ câu lệnh.
5. Nếu câu lệnh không bị hủy bỏ và không gian trống được thêm thành công vào bảng, câu lệnh INSERT tiếp tục thực thi.

Phát hiện một câu lệnh bị tạm dừng:

Khi một câu lệnh có thể tiếp tục bị tạm dừng, lỗi không được thông báo cho khách hàng. Để có thể thực hiện các hành động khắc phục, Oracle Database cung cấp các phương pháp thay thế để thông báo cho người dùng về lỗi và cung cấp thông tin về tình huống.

Các hành động có thể thực hiện trong quá trình tạm dừng:

Khi một câu lệnh có thể tiếp tục gặp lỗi có thể được sửa, hệ thống tạo sự kiện hệ thống AFTER SUSPEND. Người dùng có thể đăng ký các trigger cho sự kiện này ở cả cấp cơ sở dữ liệu và cấp schema.

Nếu người dùng đăng ký một trigger để xử lý sự kiện hệ thống này, trigger sẽ được thực thi sau khi một câu lệnh SQL bị tạm dừng. Các câu lệnh SQL được thực thi trong trigger AFTER SUSPEND luôn không thể tiếp tục và luôn là độc lập.

Giao dịch bắt đầu trong trigger sử dụng rollback segment của SYSTEM. Các điều kiện này được áp đặt để vượt qua deadlock và giảm khả năng trigger gặp cùng một lỗi như câu lệnh.

Trong mã của trigger, bạn có thể sử dụng các view USER_RESUMABLE hoặc DBA_RESUMABLE hoặc hàm DBMS_RESUMABLE.SPACE_ERROR_INFO để lấy thông tin về các câu lệnh có thể tiếp tục.

Khi một câu lệnh có thể tiếp tục bị tạm dừng:

1. Phiên gọi câu lệnh được đưa vào trạng thái chờ. Một dòng được chèn vào V$SESSION_WAIT cho phiên với cột EVENT chứa "statement suspended, wait error to be cleared".
2. Một cảnh báo tạm dừng hoạt động được phát ra trên đối tượng cần thêm tài nguyên bổ sung để hoàn thành câu lệnh bị tạm dừng.

Khi tình trạng lỗi được giải quyết (ví dụ: nhờ sự can thiệp của DBA hoặc có thể là không gian sắp xếp được giải phóng bởi các truy vấn khác), câu lệnh bị tạm dừng tự động tiếp tục thực thi và cảnh báo "resumable session suspended" được xóa bỏ.

Một câu lệnh bị tạm dừng có thể bắt buộc kích hoạt ngoại lệ SERVERERROR bằng cách sử dụng thủ tục DBMS_RESUMABLE.ABORT(). Thủ tục này có thể được gọi bởi một DBA hoặc bởi người dùng đã phát hành câu lệnh. Nếu thời gian chờ tạm dừng liên quan đến câu lệnh có thể tiếp tục được đạt đến, câu lệnh sẽ tự động hủy bỏ và một lỗi được trả về cho người dùng.

Các hoạt động nào có thể tiếp tục được? - What Operations Are Resumable?

Các hoạt động sau có thể được thực hiện theo cách có thể tiếp tục:

1. Các truy vấn: Các câu lệnh SELECT chạy hết không gian tạm thời (cho khu vực sắp xếp) là ứng cử viên cho việc thực hiện có thể tiếp tục. Khi sử dụng OCI, các cuộc gọi OCIStmtExecute() và OCIStmtFetch() cũng là ứng cử viên.
2. DML: Các câu lệnh INSERT, UPDATE và DELETE đều là ứng cử viên. Giao diện sử dụng để thực thi chúng không quan trọng; có thể là OCI, SQLJ, PL/SQL hoặc giao diện khác. Ngoài ra, INSERT INTO... SELECT từ các bảng bên ngoài cũng có thể được tiếp tục.
3. DDL: Các câu lệnh sau đây là ứng cử viên cho việc thực hiện có thể tiếp tục:
   1. CREATE TABLE ... AS SELECT
   2. CREATE INDEX
   3. ALTER INDEX ... REBUILD
   4. ALTER TABLE ... MOVE PARTITION
   5. ALTER TABLE ... SPLIT PARTITION
   6. ALTER INDEX ... REBUILD PARTITION
   7. ALTER INDEX ... SPLIT PARTITION
   8. CREATE MATERIALIZED VIEW