Để thu thập và quản lý optimizer statistics, Oracle đưa ra package DBMS_STATS với rất nhiều function procedure cho chúng ta sử dụng. Trong đó ta sẽ quan tâm nhất đến các procedure GATHER_*_STATS, với khả năng thu thập statistics (gather stats) ở mức database, schema, table hay index.

 

Trong phần này chúng ta sẽ tìm hiểu về procedure GATHER_TABLE_STATS.

 

Procedure này có 15 tham số. Ở mức cơ bản nhất, ta chỉ cần truyền vào 2 tham số đầu tiên là schema nametable name, các tham số còn lại có thể bỏ qua để sử dụng giá trị mặc định.

 

 

Tham số thứ 3 là partition name, dùng khi chúng ta muốn gather stats trên 1 partition xác định của table.

 

Dưới đây là 1 số tham số tiếp theo chúng ta có thể quan tâm

 

Estimate_percent

 

Là % số row trong table sẽ được gather stats. Tốt nhất: 100%. Trước kia hay dùng 1 số % nhỏ để đỡ tốn thời gian, như 5% hay 10%.

Oracle 11g sử dụng giá trị DBMS_STATS.AUTO_SAMPLE_SIZE (mặc định) để tự tìm ra 1 lượng % mẫu để gather stats sao cho kết quả gần nhất so với gather trên 100% mẫu, nhưng chỉ với chi phí của việc gather trên 10% mẫu.

Tham số này ta nên để mặc định.

 

Method_opt

 

Giá trị: FOR ALL [INDEXED | HIDDEN] COLUMNS SIZE [auto | 1-254 | skewonly | repeat]

Mặc định: FOR ALL COLUMNS SIZE AUTO

 

Tham số này xác định sẽ gather stats trên những column nào và có tạo histogram hay không.

  • FOR ALL COLUMNS: gather stats trên tất cả các column, kể cả virtual column
  • FOR ALL INDEXED COLUMNS: chỉ gather stats trên những column có index, không khuyến khích
  • FOR ALL HIDDEN COLUMNS: chỉ gather stats trên những virtual column
  • SIZE AUTO: Oracle tự quyết định việc tạo histogram trên column nếu thấy cần thiết
  • SIZE 1-254: SIZE 1 = không tạo histogram. Còn lại sẽ tạo trên tất cả các column, giá trị > 1 quy định số bucket tối đa sẽ tạo trên mỗi column, từ đó ảnh hưởng đến việc histogram sẽ là frequency hay height-balanced. Nếu muốn tạo histogram nên chọn 254
  • SIZE skewonly: chỉ tạo histogram trên những column có dữ liệu không đều
  • SIZE repeat: tạo lại trên những column đã có histogram với số bucket tương tự

 

Trong trường hợp muốn tạo histogram trên column xác định, hay tạo extended statistics, ta có thể dùng nhiều giá trị FOR … SIZE (chỉ đối với gather_table_stats, với gather_database_stats hay gather_schema_stats ta chỉ có thể chỉ định for all [] columns size [])

 

VD: 

Không tạo histogram trên các column khác mà chỉ tạo trên Employee_id và Manager_id: 

FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 EMPLOYEE_ID MANAGER_ID

 

Muốn tạo column group statistics: 

FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 (EMPLOYEE_ID, MANAGER_ID)

 

Muốn tạo expression statistics: 

FOR ALL COLUMNS SIZE 1 FOR COLUMNS (UPPER(FIRST_NAME))

 

Degree

 

Xác định có thực thi parallel hay không. Ta có thể set rõ số degree nếu muốn.

  • NULL: mặc định, dùng degree của table (thường là 1 – không chạy parallel)
  • DBMS_STATS.DEFAULT_DEGREE để Oracle tự xác định, có thể từ 1 đến (PARALLEL_THREADS_PER_CPU X CPU_COUNT) 

Granularity

 

Xác định mức độ gather stats nếu table được partition.

  • AUTO: Oracle tự xác định (mặc định)
  • ALL: gather trên cả table, partition và subpartition
  • GLOBAL: gather ở mức table
  • GLOBAL AND PARTITION: gather ở mức table và partition
  • PARTITION: gather ở mức partition
  • SUBPARTITION: gather ở mức subpartition

Cascade

  • DBMS_STATS.AUTO_CASCADE: Oracle gather stats trên index liên quan nếu thông tin đã cũ (mặc định)
  • TRUE: gather index stats
  • FALSE: không gather index stats

No_invalidate

  • DBMS_STATS.AUTO_INVALIDATE: Oracle sẽ xem xét và  invalidate các cursor truy cập đến table dần dần để thay đổi plan với statistics mới, tránh gây ảnh hưởng performance. Các cursor chưa bị invalidate vẫn sử dụng plan cũ
  • TRUE: Không invalidate các cursor liên quan
  • FALSE: invalidate ngay lập tức các cursor liên quan. Không khuyến khích

Nếu muốn đơn giản, ta có thể chỉ truyền 2 tham số cần thiết như ở ví dụ đầu tiên. Còn trong trường hợp muốn kiểm soát các tham số rõ ràng, ta có thể chỉ định ra như ví dụ sau