Trước đây Oracle sử dụng Rule Based Optimizer (RBO) để quyết định cách thực thi 1 câu lệnh SQL, dựa trên các quy tắc, chẳng hạn như có index thì xài index.

 

Sau này Oracle đã đưa ra Cost Based Optimizer (CBO), tính toán việc tiêu tốn tài nguyên (CPU, memory, I/O…) với các cách thực thi khác nhau, và chọn ra cách thực thi có cost thấp nhất để thực hiện. CBO phù hợp hơn RBO do cố gắng sử dụng resource ít nhất, không quan trọng thứ tự trong mệnh đề FROM, WHERE, hay ta có thể sử dụng hint để can thiệp vào execution plan…

 

Để có thể tính toán cost chính xác, đưa ra các plan thực thi tốt nhất, CBO cần có thông tin về hệ thống, về tất cả các đối tượng như table hay index. Các thông tin đó được gọi là Optimizer Statistics.

 

Optimizer Statistics được lưu trong data dictionary, và có thể xem bằng các dictionary view như DBA_TAB_STATISTICS, DBA_TAB_COL_STATISTICS

 

Table statistics & Column statistics

 

Ta sẽ thử 1 vài ví dụ nhỏ để xem cách CBO tính toán các giá trị cost (rows, bytes, %CPU…) dựa trên các giá trị statistics.

 

Table statistics cơ bản bao gồm số row trong table, số data block của table, độ dài trung bình row trong table… 

 

Chẳng hạn khi ta query table HR.Regions

 

 

Ta sẽ thấy số bytes được tính toán bằng 2 giá trị num_rowsavg_row_len

 

 

Column statistics bao gồm các thông tin cơ bản như số giá trị phân biệt (distinct value), giá trị nhỏ nhất, giá trị lớn nhất trong column…

 

Khi ta query table HR.Departments với location_id = 1800, ta thấy số row dự kiến trả về là 4, mặc dù kết quả thực tế chỉ có 1 row

 

 

Đơn giản là vì CBO lấy số dòng trong table chia cho số giá trị phân biệt của cột location_id, do nó giả sử mỗi giá trị phân biệt sẽ có 1 số lượng đồng đều (28 row, mà có 7 giá trị location_id phân biệt, ta query 1 giá trị location_id => trung bình sẽ có 28/7 = 4 row trả về)

 

 

Tuy nhiên ở 1 máy khác, với cấu hình mạnh hơn, plan lại ra chính xác số row trả về ??? Cái này chắc phải đánh dấu tìm hiểu tiếp sleepy

 

 

Từ ví dụ trên ta thấy, trong trường hợp dữ liệu phân bố không đều, nếu cứ tính trung bình chia đều như vậy thì không hợp lý lắm. Oracle bổ sung thêm các giá trị statistics khác trên column.

 

Histogram

 

Histogram chỉ ra sự phân bố dữ liệu trong column, trong trường hợp dữ liệu không đồng đều, giúp cho Optimizer ra plan thực thi chính xác hơn.

 

Oracle sẽ theo dõi việc sử dụng column, cũng như số giá trị distinct, sự phân bố dữ liệu, để quyết định có tạo histogram trên column hay không.

 

Có 2 dạng histogram:

  • Frequency
  • Height-balanced

 

Frequency Histogram

 

Frequency histogram được tạo ra khi số distinct value trong column ít hơn 254. Thuật toán khá đơn giản. Oracle sẽ sắp xếp giá trị trong cột theo thứ tự tăng dần. Mỗi giá trị cho vào 1 bucket (cái xô á =)) ), sau đó những bucket chứa giá trị giống nhau sẽ được dồn lại tới bucket được đánh số lớn nhất. Khi đó, chênh lệch giữa 2 bucket liền kề sẽ là số row chứa giá trị lớn hơn.

 

VD: trên column promo_category_id của table SH.Promotions

Khi sắp xếp thứ tự và bỏ từng giá trị vào bucket

 

freqhis_1

Sau khi dồn lại

freqhis_2

 

Muốn tìm số row có promo_category_id = 10, Oracle lấy bucket 503 – bucket 483 = 20

 

 

Height-balanced Histogram

 

Height-balanced histogram được tạo ra khi số distinct value trong column nhiều hơn 254. Thuật toán cũng tương tự Frequency histogram. Tuy nhiên khác biệt ở chỗ, do số giá trị được histogram lưu chỉ tối đa là 254, nên Oracle chia table làm 254 phần, và lưu giá trị lớn nhất trong phần đó vào bucket, với giả định nguyên phần đó đều có giá trị đó, cho nên độ chính xác sẽ thấp hơn Frequency histogram.

 

VD: trên column cust_city_id của table SH.Customers

 

Table có 55.500 rows / max 254 bucket  = 219 rows 1 phần

 

Trước khi dồn, từ row 1 -> row 219 có nhiều giá trị, nhưng row 219 có giá trị lớn nhất là 51043 -> cho vào bucket 1. Tương tự row 220 -> 438 có nhiều giá trị, nhưng row 438 có giá trị lớn nhất là 51044 -> cho vào bucket 2.

 

freqhis_3

 

Oracle sẽ kiểm tra bucket 1, nếu chứa giá trị nhỏ nhất của cột cust_city_id thì thôi, còn không thì thêm 1 bucket chứa giá trị nhỏ nhất (51040). 

 

freqhis_4

 

Cuối cùng là dồn bucket lại (trong hình ta thấy bucket 24 đã được dồn vào bucket 25)

 

freqhis_5

 

Trường hợp 1: khi query liên quan đến giá trị được lưu trong histogram, Oracle sẽ lấy số bucket chứa giá trị chia cho tổng số bucket và nhân cho số row trong table.

VD: với cust_city_id = 51806, có tổng cộng 932 row trong thực tế.

Còn khi estimate ta sẽ có (4 / 254)*55500 = 874 row (có 4 bucket lưu giá trị 51806)

 

 

Trường hợp 2: khi query không liên quan đến giá trị được lưu trong histogram, Oracle sẽ lấy 1 số density do Oracle tính toán nhân với số row của table để ra con số estimate (không phải cột density trong table dba_tab_col_statistics). 

 

 Việc tạo histogram do Oracle chủ động thực hiện, tuy nhiên ta cũng có thể tạo histogram trên column nếu muốn.

 

Extended statistics

 

Column groups

 

Trong table có thể có những column có quan hệ thông tin với nhau, chẳng hạn như giữa column City với column Country_ID. Do đó Oracle hỗ trợ tạo statistics trên nhiều column bằng procedure DBMS_STATS.CREATE_EXTENDED_STATS

 

Khi câu lệnh SQL liên quan đến những column có mối quan hệ kiểu như vậy, Optimizer có thể sử dụng extended statistics để đưa ra phương án thực thi tốt hơn, thay vì chỉ dựa trên statistics trên từng column đơn lẻ.

 

Expression statistics

 

Ngoài statistics trên nhiều column, Oracle còn hỗ trợ tạo statistics trên expression trên column. Chẳng hạn khi ta hay sử dụng query với mệnh đề where có upper(customer_name), ta có thể tạo statistics trên expression này, cũng bằng procedure DBMS_STATS.CREATE_EXTENDED_STATS

 

 

Index statistics

 

Index statistics bao gồm các thông tin như distinct keys, độ sâu của index (blevel), số leaf blocks, clustering factor… Optimizer sẽ sử dụng index statistics cùng với các thông tin statistics khác để xác định cost khi sử dụng index. 

 


 

 Optimizer statistics về cơ bản có thể được Oracle thu thập tự động. Tuy nhiên có nhiều lúc ta cần chủ động thực hiện để đảm bảo các thông tin này luôn mới nhất, giúp Optimizer đưa ra execution plan chính xác.