Làm Thế Nào Chúng Ta Biết Được Trong Cơ Sở Dữ Liệu Đang Có Các Câu Lệnh Nào, Các Table Nào Đang Bị Quét Full, Gây Ảnh Hưởng Đến Hiệu Năng?

Cách tiếp cận vấn đề: Bản thân các Cơ sở dữ liệu đề sẽ lưu lại thông tin chiến lược thực thi của các câu lệnh. Chúng ta chỉ cần kiểm tra các thông tin này và lọc ra các câu lệnh đang có chiến lược thực thi là quét full table scan (hoặc thuật ngữ tương tự, tùy theo database), thông thường các table bị quét full lớn hơn 1GB đều nên cân nhắc rà soát lại.

Quy trình dưới đây áp dụng cho Cơ sở dữ liệu Oracle

1. Scripts chi tiết tìm ra các câu lệnh và objects bị quét full, với điều kiện objecst này có dung lượng lớn hơn 1 GB, trong khoảng thời gian SYSDATE – 13 đến SYSDATE – 1

  SELECT p.sql_id,
         p.object_owner,
         p.object_name,
         p.object_type,
         p.operation,
         p.options,
         o.bytes / 1024 / 1024 / 1024     AS "SIZE_GB",
         COUNT (*)                        AS "EXECUTIONS"
    FROM dba_hist_sql_plan p,
         dba_hist_sqlstat s,
         dba_hist_snapshot sn,
         dba_segments     o
   WHERE     p.object_owner <> 'SYS'
         AND p.object_owner = o.owner
         AND p.object_name = o.segment_name
         AND p.operation LIKE '%TABLE ACCESS%'
         AND p.options LIKE '%FULL%'
         AND p.sql_id = s.sql_id
         AND s.snap_id = sn.snap_id
         AND sn.begin_interval_time > SYSDATE - 13
         AND sn.begin_interval_time < SYSDATE - 1
         AND o.bytes > 1024 * 1024 * 1024
GROUP BY p.sql_id,
         p.object_owner,
         p.object_name,
         p.object_type,
         p.operation,
         p.options,
         o.bytes / 1024 / 1024 / 1024
ORDER BY "EXECUTIONS" DESC, "SIZE_GB" DESC

Ghi chú:

• Trong câu lệnh trên: chúng ta tìm kiếm các câu lệnh đang quét Full các Objects có dung lượng lớn hơn 1GB (điều kiện lọc là o.bytes > 1024 * 1024 * 1024) 
• Lọc các câu lệnh trong khoảng thời gian SYSDATE -1 đến SYSDATE – 13
• Các học viên có thể tự thay đổi lại điều kiện để phù hợp với hệ thống của mình.

Kết quả sẽ có dạng như sau:

Trong đó

• SQL_ID: mã của câu lệnh
• Object_owner: Cho biết Objects bị quét full thuộc quyền sở hữu của user nào
• Object_name: Tên objects, đây chính là tên bảng bị quét full
• Object_type: cho biết đây là loại objects gì, trong ví dụ trên là loại Table
• Options: Full – thể hiện objects đang bị Full Scan.
• Size_GB: Dung lượng của objects bị quét full. Dung lượng càng lớn thì ảnh hưởng hiệu năng càng nguy hiểm.
• Executions: Tổng số lần thực hiện câu lệnh

2. Khi có SQL_ID, chúng ta có thể tìm chi tiết câu lệnh SQL bằng cách sau

select sql_text from dba_hist_sqltext where sql_id=’6p31zn7rwv713′