Tunning SQL bằng tính năng Explain Plan trong PL/SQL Developer

Hi các bạn,

Performance của câu truy vấn SQL đôi khi là vấn đề làm cho chúng ta đau đầu, câu SQL đang chạy ngon lành sau khi chỉnh sửa, thêm thắt cái gì đó bỗng nhiên dở chứng chạy như rùa, thậm chí như ốc sên . Thường thì hành động chúng ta khi gặp trường hợp này là ngồi mò , người mò giỏi sẽ biết dự đoán và phân nhỏ câu SQL ra để tìm thủ phạm, tuy nhiên sau khi tìm ra đoạn code thủ phạm gây chậm thì việc chỉnh sửa vẫn là tiếp tục mò mẫm . Đây là thói quen xấu cần thay đổi.

PL/SQL Developer có tính năng Explain Plan dùng để kiểm tra chi phí đoạn SQL, bạn có thể nhấn F5 hoặc click vào Tools/Explain Plan. Mình xin nêu ra 1 trường hợp ảnh hưởng đến Performance mà không ít trong chúng ta mắc phải khi thực hiện viết SQL, đó là việc phá vỡ index có sẵn của hệ thống.
Cụ thể như câu SQL bình thường dưới đây, cost của nó là 102:

Khi ta viết trunc(invoice_date), cost của nó tăng lên thành 881. Nguyên nhân là index của field invoice_date đã không được tận dụng và hệ thống phải query cả table để biến invoice_date thành trunc(invoice_date) trước khi so sánh tiếp.

Để chứng minh lý thuyết trên, hôm qua lão đã ngồi tunning lại đoạn SQL query dữ liệu trong 1 Form làm cho KH theo đúng các bước đã làm.
Đoạn SQL thủ phạm:

Code:

and not exists (select 1
                                from
                                    (select a.customer_trx_id
                                    from ra_customer_trx_all a, ra_customer_trx_all b
                                    where a.customer_trx_id = b.previous_customer_trx_id
                                    and b.complete_flag = 'Y'
                                    and not exists (select 1
                                                          from ap_invoice_lines_all d, ra_customer_trx_lines_all c
                                                          where d.global_attribute1 = c.customer_trx_line_id
                                                          and a.customer_trx_id = c.customer_trx_id)
                                    union
                                    select b.customer_trx_id
                                    from ra_customer_trx_all a, ra_customer_trx_all b
                                    where a.customer_trx_id = b.previous_customer_trx_id
                                    and b.complete_flag = 'Y'
                                    and not exists (select 1
                                                          from ap_invoice_lines_all d, ra_customer_trx_lines_all c
                                                          where d.global_attribute1 = c.customer_trx_line_id
                                                          and a.customer_trx_id = c.customer_trx_id)) loaitru
                                where loaitru.customer_trx_id = cta.customer_trx_id)

Được tunning lại thành:

Code:

and not exists (select a.customer_trx_id
                                    from ra_customer_trx_all a
                                    where a.customer_trx_id = cta.previous_customer_trx_id
                                    and a.complete_flag = 'Y'
                                    and not exists (select 1
                                                          from ap_invoice_lines_all d, ra_customer_trx_lines_all c
                                                          where d.global_attribute1 = to_char(c.customer_trx_line_id)
                                                          and a.customer_trx_id = c.customer_trx_id)
                                    union all
                                    select a.customer_trx_id
                                    from ra_customer_trx_all a
                                    where a.previous_customer_trx_id = cta.customer_trx_id
                                    and a.complete_flag = 'Y'
                                    and not exists (select 1
                                                          from ap_invoice_lines_all d, ra_customer_trx_lines_all c
                                                          where d.global_attribute1 = to_char(c.customer_trx_line_id)
                                                          and a.customer_trx_id = c.customer_trx_id))

Lão kết hợp thêm trường hợp phá hỏng index a Cường GMD đã phát hiện nữa, và kết quả là:
Trước khi Tunning, cost = 44401, kết quả chạy thực tế mất 15 phút cho dữ liệu 1 tháng

Sau khi Tunning, cost = 5941, kết quả chạy thực tế mất 2 phút cho dữ liệu 1 tháng