Thursday, July 15, 2010

Query Plan เปี๊ยนไป๋!!?

Query Plan เปี๊ยนไป๋!!?
Oracle Version: 10g
เมื่อเราทำการ Gather Statistics กับตารางที่ข้อมูลไม่ได้มีการปลี่ยนแปลงสามารถทำให้ Query Plan ที่เกิดกับคิวรีบนตารางเหล่านั้นเปลี่ยนไปได้ เช่นหากวันหนึ่งเราทำการ Gather Statistics รันคิวรี (และบันทึก Query Plan) เอาไว้ วันต่อมาเรา Gather Statistics อีก (และไม่ได้แก้ไขข้อมูลเดิมแต่อย่างใด) เมื่อเราคิวรีข้อมูลในตารางเหล่านั้นเราอาจจะพบว่า Query Plan ที่ได้ เปลี่ยนไป ซึ่ง Query Plan ที่ได้ ดูเหมือนวาจะดีกว่าตัวก่อน สาเหตุเป็นเพราะอะไร?

ความเปลี่ยนแปลงข้างต้นทางหนึ่งอาจจะมาจากค่าดีฟอลต์ของพารามิเตอร์ในการ Gather Statistics ที่ชื่อ METHOD_OPT ใน DBMS_STATS โดยใน Oracle10g พารามิเตอร์ตัวนี้มีค่าดีฟอลต์เป็น SIZE AUTO หลังจากที่เรารันคิวรี Oracle จะจำรูปแบบการคิวรีรวมถึงคอลัมน์ต่าง ๆ ไว้ในตาราง Dictionary ชื่อ SYS.COL_USAGE$ ซึ่งครั้งต่อไปที่เรารัน DBMS_STATS เพื่อรวมรวมสถิติของตารางเดิมเหล่านี้ DBMS_STATS จะคิวรีตารางนี้ก่อนเพื่อดูว่ามีคอลัมน์ไหนบ้างที่จะควรจะเก็บข้อมูล Histogram ด้วยโดยดูจาก Workload ของคิวรีตัวก่อนหน้า

ลองดูกับตัวอย่างง่าย ๆ กันนะครับ เริ่มจากการสร้างตารางเล็ก ๆ ตารางหนึ่งซึ่งมีข้อมูลที่มีความ "เบ้" (Skew) สูง ๆ อยู่ในคอลัมน์ชื่อ ID และรวบรวมสถิติแบบปกติก่อน โดยเราจะทำให้ข้อมูลมีความเบ้สูงโดยที่หากคิวรีข้อมูลในคอลัมน์ ID ที่มีค่าอยู่ระหว่าง 0 ถึง 4 ค่าใดค่าหนึ่งจะได้จำนวนแถวประมาณ 20% ของข้อมูลทั้งหมด ในขณะที่ถ้าเราคิวรีเอาค่า 99 ฐานข้อมูลจะแสดงผลออกมาเพียงแถวเดียว
SQL> create table t
2 as
3 select mod(rownum,5) id, a.*
4 from all_objects a;
Table created.

SQL> update t
2 set id = 99
3 where rownum = 1;
1 row updated.

SQL> create index t_idx on t(id);
Index created.

SQL> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T' );
4 end;
5 /

SQL> select column_name, count(*)
2 from user_tab_histograms
3 where table_name = 'T'
4 and column_name = 'ID'
5 group by column_name;

COLUMN_NAME COUNT(*)
----------- ------------
ID 2

ตอนนี้คอลัมน์ ID ไม่มี Histogram ที่สมบูรณ์ โดยดีฟอลต์ -- มีแค่ 2 Bucket ดังที่แสดงในคิวรีตาราง USER_TAB_HISTOGRAMS เพื่อที่จะให้ Optimizer เข้าใจในลักษณะความเบ้ของข้อมูล ผมต้องการ Histogram ที่มีมากกว่า 2 Bucket การมีแค่ 2 Bucket ใน USER_TAB_HISTOGRAM จะบอก Optimizer ได้แค่ว่าค่าใดสูงสุดและต่ำสุดเท่านั้น จากการ Gather Statistics ตอนนี้ Optimizer มีข้อมูลดังต่อไปนี้
  1. ค่าสูงสุด (99)
  2. ค่าต่ำสุด (0)
  3. จำนวนของค่าที่ไม่ซ้ำกัน (กรณีนี้คือ 6 ได้แก่ 0,1,2,3,4,99)
  4. จำนวนแถวในตาราง (50,119)
ด้วยข้อมูลเหล่านี้ Optimizer จะเชื่อว่าค่าแต่ละค่า (ใน 6 ค่า) มีจำนวนแถวเท่า ๆ กันในตารางคือ 50119/6 = 8,353 ซึ่งถ้าคิวรีกำหนด WHERE ID = <ค่าใด ๆ> จะให้แถวเป็นจำนวน 8,353 แถว เพื่อพิสูจน์เราลองคิวรีด้วย ID=1 หรือ ID=99
{xtypo_code}SQL> set autotrace traceonly explain

SQL> select *
2 from t
3 where id = 1;

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU) | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8353 | 783K | 163 (2) | 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 8353 | 783K | 163 (2) | 00:00:02 |
-----------------------------------------------------------------------


SQL> select *
2 from t
3 where id = 99;

Execution Plan
-----------------------------------------------------------------------
Plan hash value: 1601196873
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU) | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8353 | 783K | 163 (2) | 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 8353 | 783K | 163 (2) | 00:00:02 |
-----------------------------------------------------------------------


Optimizer เชื่อว่ามีแถวที่ค่า ID = 1 อยู่ 8,353 แถว ซึ่งมากกว่าประมาณ 10% ของจำนวนแถวของข้อมูลทั้งตาราง มันจึงเลือกที่จะทำการอ่านข้อมูลทั้งตาราง (Table Access Full) แทนที่จะใช้อินเด็กซ์ เช่นเดียวกับเมื่อใช้ WHERE ID = 99 ซึ่งมีอยู่เพียงแถวเดียวในตาราง แต่ Optimizer คิดว่ามีอยู่ 8,353 แถว มันจึงใช้วิธีการเดียวกันคือ Table Access Full คราวนี้ลอง Gather Statistics อีกครั้ง โดยใช้คำสั่งเหมือนกับคำสั่งก่อนหน้า
SQL> set autotrace off
SQL>begin
2 dbms_stats.gather_table_stats
3 ( user, 'T' );
4 end;
5 /
SQL> select column_name, count(*)
2 from user_tab_histograms
3 where table_name = 'T'
4 and column_name = 'ID'
5 group by column_name;

COLUMN_NAME COUNT(*)
------------ -----------
ID 5


สังเกตว่าการทำ Gather Statistics ครั้งหลังนี้ เราได้มากกว่า 2 Bucket ใน USER_TAB_HISTOGRAMS, DBMS_STATS ที่ใช้ค่าพารามิเตอร์เป็น AUTO จะรวบรวมข้อมูลมากขึ้นกว่าเดิม ถ้าเราคิวรีตาราง SYS.COL_USAGE$ ตอนนี้ เราจะพบว่ามีแถวข้อมูลใหม่เพิ่มขึ้นมาแถวหนึ่งเป็นคอลัมน์และตารางที่เราทำการคิวรีก่อนหน้า ซึ่งหมายความว่ามีคิวรีในระบบที่ใช้เครื่องหมายเท่ากับกับคอลัมน์ดังกล่าว ซึ่งนั่นเป็นความลับที่ทำให้ DBMS_STATS เปลี่ยนวิธีการรวบรวมสถิติ และทำให้เกิดความเปลี่ยนแปลงขึ้นอย่างมากใน Query Plan ต่อไปนี้
SQL> select *2 from t3 where id = 1;

Execution Plan
-----------------------------------------------------------------------
Plan hash value: 1601196873
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU) | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10260| 961K | 164 (2) | 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 10260| 961K | 164 (2) | 00:00:02 |
-----------------------------------------------------------------------

SQL> select *
2 from t
3 where id = 99;

Execution Plan
-----------------------------------------------------------------------
Plan hash value: 470836197
-----------------------------------------------------------------------
| Id| Operation |Name |Rows|Bytes|Cost(%CPU)|Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | |1 |96 |2 (0) |00:00:01|
| 1 | TABLE ACCESS BY INDEX ROWID|T |1 |96 |2 (0) |00:00:01|
|* 2| INDEX RANGE SCAN |T_IDX|1 | |1 (0) |00:00:01|
-----------------------------------------------------------------------


สังเกตดูว่า Cardinality (Rows) ต่างกันอย่างไร ระหว่างเดิม 8,353 เปลี่ยนไปเป็น 10,260 (กรณี WHERE ID = 1) หรือ 1 (กรณี WHERE ID = 99) การเปลี่ยนแปลงไปของ Cardinality นี้มีผลต่อผลรวมของ Cost ทั้งหมดของตัว Query Plan ซึ่งทำให้ Optimizer สามารถเลือก Query Plan ได้ดีกว่า

และนี่คือข้อเท็จจริงที่ทำให้ Query Plan อาจจะเปลี่ยนเมื่อเวลาผ่านไป ซึ่งมีผลมาจากวิธีการรวบรวมสถิติของ Oracle ที่เปลี่ยนแปลงไป เมื่อเรากำหนดค่าของพารามิเตอร์ METHOD_OPT ใน DBMS_STATS ให้เป็น AUTO (AUTO ปกติเป็นค่าดีฟอลต์)
เรียบเรียงจาก:
http://www.oracle.com/technology/oramag/oracle/06-may/o36asktom.html