Bài viết này nêu rõ sự khác biệt giữa SQL Profile và Baselines trong Oracle® và giải thích cách chúng hoạt động khi tuning truy vấn.
Optimizer, Profile và Baseline
Ở mức độ tổng quan, ba yếu tố này phối hợp như sau:
Query Optimizer sử dụng thông tin như system statistics, bind variables, và compilations để chọn kế hoạch thực thi tốt nhất cho truy vấn. Tuy nhiên, đôi khi đầu vào bị khuyết dẫn đến kế hoạch không tối ưu.
SQL Profiles chứa thông tin bổ trợ giúp giảm thiểu sai sót này, hỗ trợ Optimizer chọn kế hoạch tốt nhất.
SQL Plan Baselines cho một câu lệnh SQL bao gồm tập hợp các kế hoạch được chấp nhận. Sau khi parse câu lệnh, Optimizer chọn kế hoạch tốt nhất từ tập hợp kế hoạch được chấp nhận. Nếu Cost Optimizer tìm thấy kế hoạch tốt khác, nó thêm kế hoạch mới vào lịch sử kế hoạch. Tuy nhiên, Optimizer không sử dụng kế hoạch mới cho đến khi xác minh nó hoạt động tốt hơn kế hoạch hiện tại được chấp nhận.
Hãy hiểu đơn giản: SQL Profiles cung cấp thông tin cho Optimizer để giúp chọn kế hoạch tốt nhất nhưng không ép buộc Optimizer chọn kế hoạch cụ thể nào. SQL Plan Baselines giới hạn lựa chọn kế hoạch của Optimizer trong tập hợp kế hoạch được chấp nhận. Nếu bạn muốn xem xét các kế hoạch dựa trên cost, hãy đưa chúng vào tập hợp baseline được chấp nhận.
Sử dụng SQL Profiles nếu bạn muốn Optimizer dùng kế hoạch chi phí thấp nhất và cần dùng thống kê mới nhất. Sử dụng Baselines khi bạn muốn dùng một trong tập hợp kế hoạch cụ thể. Nếu SQL Plan Baselines không lấy được kế hoạch tốt nhất từ tập hợp được chấp nhận, hãy dùng SQL Profile thay thế.
SQL Plan Management (SPM)
SQL Plan Management có các thành phần sau:
- Plan Capture (Thu thập kế hoạch)
- Plan Selection (Lựa chọn kế hoạch)
- Plan Evolution (Tiến hóa kế hoạch)
SPM Plan Capture
Khi bạn thực thi bất kỳ câu lệnh nào, hệ thống hard parse nó và tạo cost plan dựa trên SQL Profile có sẵn. Sau khi chọn cost-based plan, hệ thống so sánh với các kế hoạch trong SQL Plan Baselines. Nếu cost-based plan khớp với một trong các kế hoạch được chấp nhận, bạn có thể dùng kế hoạch đó. Nếu không khớp, hệ thống thêm nó vào Plan Baselines như một kế hoạch chưa được chấp nhận.
SPM Plan Selection
Khi thực thi SQL statement với baseline plan, hệ thống chọn kế hoạch tốt nhất cho SQL đó. Optimizer dùng quy trình tương tự. SQL Profile có sẵn cũng ảnh hưởng đến chi phí ước tính cho từng kế hoạch và chọn kế hoạch tương ứng.
SPM Plan Evolution
Thành phần cuối của SPM là tiến hóa của các kế hoạch chưa được chấp nhận, kiểm thử các kế hoạch chưa chấp nhận so với các kế hoạch đã chấp nhận. Quá trình này đánh giá kế hoạch tốt nhất dựa trên thời gian thực thi truy vấn và tài nguyên CPU yêu cầu. Nó chấp nhận kế hoạch tốt nhất theo chi phí truy vấn. Nếu có SQL Profile, nó ảnh hưởng đến chi phí ước tính.
So sánh Profile và Baseline
Bảng sau (nguồn: https://www.cnblogs.com/princessd8251/articles/3637461.html) so sánh SQL Profiles và SQL Plan Baselines:
Kiến trúc
Hình ảnh sau minh họa kiến trúc SQL Plan Baseline:
Nguồn ảnh: https://ittutorial.org/sql-plan-management-using-sql-plan-baselines-in-oracle-oracle-database-performance-tuning-tutorial-14/
Tải SQL Plan Baselines
Hình ảnh sau minh họa hai cách tải SQL Plan Baselines:
Nguồn ảnh: https://ittutorial.org/sql-plan-management-using-sql-plan-baselines-in-oracle-oracle-database-performance-tuning-tutorial-14/
Với phương pháp đầu tiên, bạn có thể đặt tham số khởi tạo OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES thành TRUE để bật tự động thu thập kế hoạch. Tham số này mặc định là FALSE, hãy đặt thành TRUE như ví dụ sau:
Với phương pháp thứ hai, bạn có thể dùng package DBMS_SPM để quản lý thủ công SQL Plan Baselines. Tải kế hoạch từ SQL Tuning Set như ví dụ sau:
Tải SQL Plan Baseline thủ công
Sử dụng lệnh sau để tải plan baseline thủ công:
Kiểm tra sử dụng SQL Plan Baseline
Sau khi tải SQL Plan Baseline, bạn cần thực thi SQL để xem Optimizer có đang dùng SQL Plan Baseline hay không. Bạn có thể truy vấn SQL Plan Baselines bằng SQL_TEXT và tên kế hoạch như sau:
Hiển thị SQL Plan Baselines
Chạy truy vấn sau để hiển thị SQL Plan Baselines:
Xóa SQL Plan Baseline
Để xóa SQL Plan Baseline, chạy truy vấn sau để kiểm tra kế hoạch nào Optimizer đang dùng:
Sau khi có kế hoạch đang dùng, chạy lệnh sau để xóa baseline:
Oracle SQL Profile
SQL Tuning Advisor, được kích hoạt qua Oracle Enterprise Manager (OEM) hoặc bằng lệnh command-line, có thể tạo SQL Profile cho một câu lệnh SQL. Profile này chứa thông tin bổ sung về câu lệnh.
Ví dụ
Trong ví dụ này, bạn chạy SQL Tuning Advisor đối với sql_id rồi thực hiện các thao tác trên SQL Profile:
1. Chạy SQL Tuning Advisor
Chạy mã SQL Tuning Advisor sau cho sql_id 6DKRNBX1ZDWY38:
Chạy DBMS_SQLTUNE.REPORT_TUNING_TASK sau để xem khuyến nghị:
2. Chấp nhận SQL Profile
Chạy mã sau để chấp nhận sql_profile:
3. Kiểm tra tên SQL Profile
Sử dụng truy vấn sau để kiểm tra tên sql_profile:
4. Vô hiệu hóa SQL Profile
Chạy mã sau để vô hiệu hóa sql_profile:
Để bật lại, thay giá trị từ DISABLED thành ENABLED.
5. Xóa SQL Profile
Chạy mã sau để xóa sql_profile:
Kết luận
Khi bạn thực thi bất kỳ câu lệnh SQL nào, Optimizer tạo execution plan để parse truy vấn, lấy dữ liệu từ ổ cứng và đưa vào bộ nhớ. SQL Profile và Baselines hướng dẫn Optimizer chọn kế hoạch chi phí thấp nhất về mặt thời gian và CPU. Một kế hoạch SQL tốt chạy truy vấn hiệu quả và trả về kết quả nhanh hơn.
Tìm hiểu thêm về dịch vụ Database của chúng tôi.
Sử dụng tab Feedback để bình luận hoặc đặt câu hỏi. Bạn cũng có thể bắt đầu cuộc trò chuyện với chúng tôi.