Execution plan เหตุใดเจ้าจึงเปลี่ยนไป?

สวัสดีครับ ช่วงนี้นานๆ เข้ามาเขียนซักทีนึงนะครับ แล้ววันนี้ผมก็จะมาบอกเล่าเรื่องราวเกี่ยวกับสิ่งที่เป็นหัวใจสำคัญสำหรับการ Tuning นั้นคือ Execution plan ของแต่ละ SQL statement ว่าเพราะสาเหตุใดบ้างที่ทำให้การคิวรี่ข้อมูลรู้สึกช้าลงหรือแตกต่างออกไปทั้งที่ SQL statement ก็ยังเหมือนเดิม ลองมาดูกันครับ

Execution plan เปลี่ยนไปได้อย่างไร?

เริ่มต้นด้วยผมจะทำการสร้างตารางเพื่อการทดสอบขึ้นมาโดยสร้างข้อมูลย้อนหลังขึ้นมา 5 ปี โดยในแต่ละวันมีข้อมูลจำนวน 1000 บรรทัด พร้อมทั้งทำการสร้าง index บน exp_date + Gather table statistics เสร็จสรรพ

SQL> create table TEST_EXP (id number, exp_date date, name varchar2(10));

Table created.

SQL> declare
  2  v_count  number;
  3  begin
  4  v_count:=0;
  5  for i in 1..1830 loop
  6     for j in 1..1000 loop
  7     v_count:= v_count+1;
  8     insert into TEST_EXP values (v_count, sysdate-i, 'EXP');
  9     end loop;
 10  end loop;
 11  commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> create index IDX_TEST_EXP on TEST_EXP(exp_date);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'TEST_EXP', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

ต่อมาผมจะทำการคิวรี่ข้อมูลจากตารางโดยกำหนดให้เลือกข้อมูลย้อนหลังจากตารางมา 1 ปี

SQL> select * from TEST_EXP where exp_date > sysdate - 365;

364000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3999963646

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   364K|  6045K|  1194   (9)| 00:00:15 |
|*  1 |  TABLE ACCESS FULL| TEST_EXP |   364K|  6045K|  1194   (9)| 00:00:15 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EXP_DATE">SYSDATE@!-365)

Statistics
----------------------------------------------------------
        171  recursive calls
          0  db block gets
      28879  consistent gets
       5621  physical reads
          0  redo size
    8070784  bytes sent via SQL*Net to client
     267414  bytes received via SQL*Net from client
      24268  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     364000  rows processed

จากผลด้านบนจะเห็นว่า CBO เลือกที่จะใช้ Full Table Scan (FTS) สำหรับการเลือกข้อมูลที่ค่อนข้างใหญ่ CBO และอาจจะสมเหตุสมผลมากกว่า และอีกสิ่งที่ควรสังเกตคือ เมื่อใดก็ตามที่ผลลัพธ์ของคิวรี่มีจำนวนบรรทัดเท่ากับจำนวนบรรทัดใน Execution plan เราสามารถมั่นใจได้เลยว่า นี่แหละคือ Execution plan ที่เจ๋งที่สุดที่ CBO เลือกมาให้

สมมุตินะสมมุติว่าตาราง TEST_EXP เป็นส่วนหนึ่งของ Application ที่ผมกำลังดูแล นั่นแน่นอนว่าลูกค้ามีความสุข DBA อย่างผมก็มีความสุขด้วยและสุดท้ายเราก็อยากจะเก็บสิ่งดีๆ นี้ไว้นานๆ ไม่อยากให้อะไรๆ มันเปลี่ยนไป ดังนั้นเราจึงสรุปกันได้ว่า “เฮ้ย เราเลิก Gather statistics บนตารางนี้ไปละกัน”

แต่อะไรๆ มันก็ค่อยๆ เปลี่ยนไปทุกวัน ในขณะที่ผู้ใช้ค่อยๆ อัดข้อมูลลงมาบนตาราง TEST_EXP ทุกวี่ทุกวัน (ตัวอย่างข้างล่าง สมมุติการอัดข้อมูลลงบนตาราง TEST_EXP ทุกวันเป็นเวลา 1 ปีเต็มล่วงหน้า)

SQL> declare
  2  v_count  number;
  3  begin
  4  v_count:=1830000;
  5  for i in 1..365 loop
  6     for j in 1..1000 loop
  7     v_count:= v_count+1;
  8     insert into TEST_EXP values (v_count, sysdate+i, 'EXP');
  9     end loop;
 10  end loop;
 11  commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

แต่จากที่เราเคยคุยกันไว้ว่า ยังไงก็ไม่ Gather statistics เด็ดขาด ดังนั้น Application ของเราก็จะทำงานเหมือนเดิมๆ ที่เคยทำมา นั้นคือเลือกข้อมูลย้อนหลัง 1 ปี (ในคิวรี่นี่เราจำเป็นต้องใช้ sysdate+365 จากนั้นค่อย select ย้อนหลัง เนื่องจากเราสมมุติว่าผ่านมา 1 ปีเต็มแล้ว

SQL> select * from TEST_EXP where exp_date > (sysdate+365) - 365;

365000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3293283452

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   909 | 15453 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_EXP     |   909 | 15453 |     9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_EXP |   909 |       |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EXP_DATE">SYSDATE@!+365-365)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      50619  consistent gets
       2028  physical reads
          0  redo size
    8468404  bytes sent via SQL*Net to client
     268151  bytes received via SQL*Net from client
      24335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     365000  rows processed

ฉิบหายแล้วววว Execution plan เปลี่ยนนนนนนนน!!!!

จากผลลัพธ์ด้านบนจะเห็นว่า CBO เลือกที่จะใช้ Index ในขณะที่แบบเก่านั่นคือการใช้ FTS ซึ่งสามารถสังเกตได้ว่า CBO คำนวนจำนวนบรรทัดใน Execution plan ผิดพลาด จากผลลัพธ์ที่มีจำนวนถึง 365,000 บรรทัดเหลือแค่ 909 บรรทัด ซึ่ง CBO นั่นรับรู้แค่เพียงว่าน่าจะมีข้อมูลเพียงแค่ 1 วันเท่านั้นจากที่เราทำการ Gather statistics ครั้งสุดท้าย

ทำไม Execution plan ถึงเปลี่ยนไปในขณะที่ Statistics ต่างๆ ก็ไม่ได้เปลี่ยน?

เหตุผลหลักนั่นคือ CBO คิดว่าวันล่าสุดบน TEST_EXP นั่นคือเมื่อปีก่อนจากครั้งสุดท้ายที่เราทำการ Gather statistics ไป และคิวรี่ได้ทำการเลือกข้อมูลที่อยู่นอกเหนือจากที่ CBO สามารถนำมาคำนวนได้ แล้ว CBO รู้จำนวนข้อมูลที่แท้จริงได้ยังไง คำตอบคือถ้า CBO ไม่รู้ข้อมูล CBO จะเดาเอา! การปล่อยให้ CBO เดานั้นไม่ใช่สิ่งที่ดีเลย โดยเฉพาะเมื่อ CBO ไม่สามารถรับรู้ statistics สำหรับตารางๆ นั้นเลย

เราจะแก้ไขปัญหานี้ยังไง?

ดีกว่าที่เราจะปล่อยให้ CBO เล่นเกมเดาใจต่อไป เราก็ต้องบอกมันไปเลยว่าจริงๆ แล้วนายควรจะต้องเลือกข้อมูลมากแค่ไหนด้วยวิธีง่ายๆ แค่ทำการ Gather statistics ให้ CBO รู้ว่าเรามีข้อมูลเพิ่มเข้ามาอีก 1 ปีแล้วนะ

SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'TEST_EXP', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

และถ้าเราทำการรันคิวรี่อีกครั้งนึง…

SQL> select * from TEST_EXP where exp_date > (sysdate+365) - 365;

365000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3999963646

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   364K|  6058K|  1473  (11)| 00:00:18 |
|*  1 |  TABLE ACCESS FULL| TEST_EXP |   364K|  6058K|  1473  (11)| 00:00:18 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EXP_DATE">SYSDATE@!+365-365)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      50619  consistent gets
          0  physical reads
          0  redo size
    8468404  bytes sent via SQL*Net to client
     268151  bytes received via SQL*Net from client
      24335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     365000  rows processed

สุดยอดดด! มันกลับมาแล้ว Execution plan ที่รัก สังเกตเห็นไหมครับว่าจำนวนบรรทัดที่ได้กับจำนวนบรรทัดที่ CBO คำนวนออกมากลับมาใกล้เคียงกันอีกครั้ง! สุดท้ายทุกคนก็กลับมามีความสุขกันอีกครั้ง…

สรุป

ใช่แล้วครับ Execution plan สามารถเปลี่ยนเปลงได้ถึงแม้ว่าเราจะไม่ได้เปลี่ยนแปลงอะไรบนฐานข้อมูลเลยรวมถึงการที่เราไม่ Gather statistics ด้วย

ขอบคุณครับ
TheWeirdDBA

ขอบคุณข้อมูลสุดเจ๋งจาก
Richard Foote’s Oracle Blog

Advertisements

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s