Hướng dẫn Tối ưu Chỉ mục trong Oracle Database
Trong bài viết này, chúng ta sẽ tìm hiểu những cách tối ưu hóa chỉ mục trong Oracle Database, đặc biệt là khi nâng cấp phiên bản, nhằm cải thiện hiệu suất và đảm bảo các truy vấn vẫn hoạt động hiệu quả.
1. Kiểm tra Đường dẫn Thực thi Sau Khi Nâng cấp
Khi nâng cấp phiên bản Oracle, việc theo dõi các truy vấn của ứng dụng là rất quan trọng. Hãy xác định xem các đường dẫn thực thi (execution paths) của truy vấn có còn sử dụng các chỉ mục như trước khi nâng cấp hay không. Điều này bao gồm việc kiểm tra xem chiến lược thực thi có thay đổi và liệu nó đã tốt hơn hoặc tệ hơn.
2. Truy vấn Thông tin Về Chỉ mục
Chúng ta có thể truy vấn các view DBA_INDEXES và DBA_IND_COLUMNS để lấy danh sách các chỉ mục trên một bảng cụ thể. Nếu chỉ muốn thông tin cho schema hiện tại, hãy sử dụng các view USER_INDEXES và USER_IND_COLUMNS.
3. Sử dụng Chỉ mục Ẩn
Chỉ mục ẩn (invisible indexes) cho phép chúng ta tạm thời “ẩn” một chỉ mục, từ đó kiểm tra cách các truy vấn hoạt động mà không có nó. Việc kích hoạt lại chỉ mục ẩn rất dễ dàng sau khi kiểm tra.
4. Chỉ mục Kết hợp với Chỉ mục Ẩn
Chỉ mục ẩn cũng cho phép tạo nhiều chỉ mục trên cùng một cột, giúp dễ dàng sử dụng chỉ mục khác nhau cho các quy trình khác nhau trong ngày, từ đó quản lý tốt hơn.
5. Tạo và Phân tích Chỉ mục Cùng Một Phép
Oracle cho phép chúng ta tạo chỉ mục và phân tích chúng trong một bước bằng cách sử dụng mệnh đề COMPUTE STATISTICS. Từ phiên bản 10g, Oracle tự động tính toán số liệu thống kê cho các thao tác CREATE/REBUILD chỉ mục khi chỉ mục không rỗng.
6. Giá trị Mặc định và NULL
Sử dụng giá trị mặc định cho một cột trong bảng sẽ không cho phép giá trị NULL và có thể giúp cải thiện hiệu suất xử lý dữ liệu.
7. Sử Dụng Hàm Để Tăng Cường Chỉ mục
Khi so sánh với một cột, nếu sử dụng các hàm như TO_DATE hoặc TO_CHAR, sẽ giúp các chỉ mục được sử dụng tốt hơn so với việc áp dụng hàm trực tiếp trên cột.
8. Kiểu Dữ liệu Không Tương đồng
Việc so sánh các kiểu dữ liệu không tương đồng có thể khiến Oracle vô hiệu hóa chỉ mục. Dù có sử dụng lệnh EXPLAIN PLAN, việc xác định nguyên nhân quét toàn bộ bảng vẫn khó khăn.
9. Tính Chọn Lọc của Chỉ mục
Tính chọn lọc cao của một chỉ mục giúp cost-based optimizer xác định các đường dẫn thực thi nhanh chóng. Tạo các chỉ mục kết hợp để nâng cao tính chọn lọc và hiệu suất.
10. Phân Tích Chiều Cao Nhị Phân của Chỉ mục
Để kiểm tra chiều cao nhị phân của chỉ mục, hãy sử dụng cột BLEVEL trong view USER_INDEXES. Việc phân tích đều đặn sẽ cung cấp thông tin cần thiết để điều chỉnh các chỉ mục.
11. Rebuild Chỉ mục Khi Cần
Nếu số lượng hàng bị xóa trong một chỉ mục đạt từ 20-25%, hãy thực hiện rebuild chỉ mục để giảm chiều cao nhị phân và không gian trống.
12. Sử Dụng Histogram
Khi dữ liệu trong bảng không đồng đều, sử dụng histogram cung cấp cho cost-based optimizer một cái nhìn tổng quan về phân bố dữ liệu và có thể cải thiện hiệu suất.
13. Tính Năng Skip-Scan
Đối với các bảng lớn với chỉ mục hỗn hợp, tính năng skip-scan cung cấp khả năng truy cập nhanh ngay cả khi cột đầu tiên không được sử dụng trong điều kiện giới hạn.
14. Lưu Trữ Giá trị Chỉ mục
Giá trị của các cột được đánh chỉ mục được lưu trữ trong chỉ mục. Điều này giúp loại bỏ việc truy cập bảng không cần thiết và giảm bớt I/O.
15. Chỉ mục Bitmap
Sử dụng chỉ mục bitmap cho các cột có số lượng giá trị thấp, như cột giới tính với hai giá trị "nam" hoặc "nữ".
16. Truy vấn Chỉ mục Bitmap
Để lấy thông tin về các chỉ mục bitmap, hãy truy vấn view USER_INDEXES để đảm bảo bạn quản lý các chỉ mục một cách hiệu quả.
17. Nguy Cơ Khóa Với Chỉ mục Bitmap
Tránh sử dụng chỉ mục bitmap trong môi trường OLTP lớn do nguy cơ khóa xảy ra khi có nhiều thao tác INSERT. Hiểu rõ các hạn chế liên quan đến chỉ mục bitmap trước khi sử dụng.
18. Bảng Tổ Chức Theo Chỉ Mục
Cân nhắc sử dụng các bảng tổ chức theo chỉ mục cho những bảng thường xuyên truy cập bằng cách so khớp chính xác hoặc range scan.
19. Chỉ mục Khóa Đảo
Đối với hệ thống có giới hạn về số lượng đĩa và cần thực hiện nhiều tải tuần tự lớn, chỉ mục khóa đảo có thể là giải pháp khả thi.
20. Tối Ưu Chỉ mục Dựa Trên Hàm
Để tối ưu hóa chỉ mục dựa trên hàm, hãy thiết lập tham số khởi tạo QUERY_REWRITE_ENABLED thành TRUE.
21. Chỉ mục Local Prefixed
Chỉ mục local prefixed giúp Oracle nhanh chóng loại bỏ các phân vùng không cần thiết, từ đó cải thiện hiệu suất.
22. Cập Nhật Chỉ Mục Toàn Cục
Khi sửa đổi bảng phân vùng, hãy sử dụng câu lệnh UPDATE GLOBAL INDEXES để đảm bảo chỉ mục toàn cục được cập nhật đúng cách.
23. Cải Thiện Hiệu Suất Nối Bằng Chỉ mục Bitmap
Sử dụng chỉ mục kết hợp bitmap để nâng cao hiệu suất khi thực hiện các phép nối trong data warehouse.
24. Xây Dựng Lại Chỉ Mục Nhanh Chóng
Sử dụng tùy chọn REBUILD trong lệnh ALTER INDEX để nhanh chóng xây dựng lại chỉ mục với hệ thống hiện tại.
25. Xây Dựng Lại Chỉ Mục Trực Tuyến
Dùng tùy chọn REBUILD ONLINE để cho phép các thao tác DML diễn ra trong khi chỉ mục đang được xây dựng lại, ngoại trừ các chỉ mục bitmap.
26. Chuyển Đổi Online Giữa Bảng Phân Vùng
Ngoài việc chuyển đổi online từ bảng không phân vùng sang bảng phân vùng, chúng ta cũng có thể chuyển đổi các chỉ mục đi kèm một cách hiệu quả.
source: viblo
