ต่อจากตัวอย่างที่แล้ว ยังสนุกไม่เลิก รู้สึกว่า logic ของตัวอย่างที่แล้วมันเข้าใจยากเกินไปหน่อย เลยพยายามหา logic อื่น ผลลัพธ์คือ query นี้ครับ

select d1, d2
  , trunc(((d2-d1)+1)/7)*2
  + case
      when to_char(d1,’d') = ‘1′ and to_char(d2,’d') = ‘7′ then 0
      when to_char(d1,’d') = ‘1′ then 1
      when to_char(d2,’d') = ‘7′ then 1
      when to_char(d2,’d') < to_char(d1,’d') then 2
      else 0
    end cnt
from weekend_count

สำหรับ query นี้มาจากการสังเกตตาราง

		1	2	3	4	5	6	7
		อา	จ	อ	พ	พฤ	ศ	ส
1	อา	1	1	1	1	1	1	0
2	จ	2	0	0	0	0	0	1
3	อ	2	2	0	0	0	0	1
4	พ	2	2	2	0	0	0	1
5	พฤ	2	2	2	2	0	0	1
6	ศ	2	2	2	2	2	0	1
7	ส	2	2	2	2	2	2	1

แนวนอนเป็นวันที่เริ่มต้นว่าตรงกับวันอะไร ส่วนแนวตั้งเป็นวันที่สุดท้าย
ส่วนค่าแต่ละค่ามาจากการนับ เช่น จากวันอาทิตย์ ถึง วันศุกร์ มีวันหยุด 1 วัน
จากวันอังคาร ถึงวันจันทร์ จะมีวันหยุด 2 วัน

ซึ่งผมสรุปได้ เป็นกรณีดังนี้
1.วันแรกเป็นวันอาทิตย์ และวันสุดท้ายเป็นวันเสาร์ ครบสัปดาห์พอดี ไม่ต้องบวกเพิ่ม
2.วันแรกเป็นวันอาทิตย์ หรือวันสุดท้ายเป็นวันเสาร์ มีวันหยุด 1 วัน
3.วันสุดท้ายเป็นวันในสัปดาห์ที่อยู่ก่อนวันแรก แสดงว่าผ่านเสาร์อาทิตย์มาหนึ่งรอบ บวกเพิ่มอีก 2
4.นอกจากกรณีข้างต้น แสดงว่าไม่มีวันหยุดเลย

ซึ่งสามารถเขียนเป็น sql โดยใช้ case expression ได้ดังตัวอย่าง

ถ้าเปรียบเทียบ query นี้ กับ query ก่อน ผมไม่แน่ใจนักว่าอันไหนจะเร็วกว่า แต่คิดว่าอันหลังน่าจะเร็วกว่า เพราะมีการคำนวณน้อยกว่า แต่คิดว่าก็คงต่างกันไม่มากนัก หากจะเลือกใช้ น่าจะเลือกจาก logic มากกว่า โดยดูว่าอันไหนเข้าใจง่ายกว่ากัน

SQL is fun!!!!

ช่วงนี้ไม่ค่อยมีเวลามาเขียนหัวข้อใหม่ ๆ เท่าไหร่ ต้องขออภัยคนอ่านเป็นอย่างยิ่งครับ

หัวข้อวันนี้บังเอิญไปเจอคำถามในเว็บ narisa ที่น่าสนใจข้อหนึ่ง ต้องการนับวันหยุดที่อยู่ภายในช่วงวันที่ที่กำหนด โดยเจ้าของกระทู้สนใจนับเฉพาะเสาร์-อาทิตย์เท่านั้น

ในกรณีทั่ว ๆ ไปแล้ว หากโจทย์ต้องการนับวันหยุด ผมจะสร้างตารางเก็บวันหยุดทั้งหมดขึ้นมา แล้วก็ใช้ count ธรรมดา ก็เพียงพอ แถมรองรับกรณีวันหยุดตามประเพณีต่าง ๆ ได้ด้วย แต่อาจต้องการการ maintenance ตารางในแต่ละปีบ้าง

อย่างไรก็ดี วันนี้ผมนึกสนุกอยากลองดูว่า ถ้าจะเขียน sql ใช้ built-in function ล้วน ๆ เพื่อตอบโจทย์การนับวันหยุดเสาร์ อาทิตย์นี้จะทำได้ไหม ผลปรากฏเป็น sql นี้ครับ

create table weekend_count
( d1  date
, d2  date
) ;
select d1, d2
  , greatest((next_day(d1,7)-d1)-5,0)
  + ((next_day(d2,7)-7)-next_day(d1,7))/7*2
  + least ((d2+1) – (next_day(d2,7)-7),2)
from weekend_count
/

หลักการคือ หาวันเสาร์แรก และ เสาร์สุดท้ายในช่วงมาโดยใช้ฟังก์ชั่น next_day(d1,7) และ next_day(d2,7)-7

นับ weekend ช่วงที่หนึ่งคือ ตั้งแต่วันแรกจนถึงวันเสาร์แรก จะมีเสาร์อาทิตย์เท่ากับจำนวนวันที่เกิน 5 วัน
นับ weekend ช่วงที่สองคือ ตั้งแต่วันเสาร์แรกจนถึงวันเสาร์สุดท้าย จะมีเสาร์อาทิตย์เท่ากับจำนวนวันหารเจ็ดคูณสอง
นับ weekend ช่วงที่สามคือ ตั้งแต่วันเสาร์สุดท้ายจนถึงวันสุดท้าย จะมีเสาร์อาทิตย์เท่ากับจำนวนวัน แต่ไม่เกินสอง

จริง ๆ logic นี้ อาจมีจุดอ่อนในกรณีที่ ช่วงวันที่ยาวไม่พอที่จะแบ่งเป็นสามช่วงได้ แต่เท่าที่ทดสอบดูหลาย ๆ เคส พบว่าจำนวนวันจากช่วงที่สองจะติดลบ และชดเชยกันจนได้ผลลัพธ์ที่ถูกต้องอยู่ดี ถ้าใครลองทดสอบดูแล้วเจอเคส ที่ใช้ไม่ได้ รบกวนแจ้งด้วยก็จะเป็นพระคุณยิ่งครับ

ก่อนจบหัวข้อ ขอกลับมาที่เฉลยที่อยู่ในเว็บนั้นอีกนิด จริง ๆ แล้วเจ้าของกระทู้ต้องการคำนวณช่วงวันที่ที่ user กรอกเข้ามาผ่าน forms ดังนั้นหากคำนวณด้วย pl/sql ล้วน ๆ น่าจะเหมาะสมกว่า การใช้ sql ที่ผมแสดงให้ดู แต่อาจสามารถปรับเอา logic ไปประยุกต์ใช้ได้ ซึ่งจะตัดเรื่องการวนลูปออกไป เหลือแต่การคำนวณล้วน ๆ ซึ่งเร็วกว่า

การใช้ sql แบบนี้นั้น จะเหมาะในกรณีที่เราเก็บช่วงวันที่อยู่ในตาราง และมีจำนวนหลาย ๆ ช่วง ต้องการ select และคำนวณไปพร้อม ๆ กันเลย ซึ่งการใช้ built-in function ก็จะเร็วกว่า การเขียน user-defined function ตามที่เคยแสดงให้ดูในครั้งก่อนแล้ว

สุขสันต์ปีใหม่นะครับ

ขอพักแนว technical ว่าด้วยเรื่อง performance tuning มาคุย(บ่น) เรื่องงานเอกสารเพื่อประกอบกระบวนการ Software Development Life Cycle ที่มักเรียกกันสั้น ๆ ว่า Program Spec. นี้สักหน่อย

เรื่องของเรื่องมีอยู่ว่า ที่บริษัททำการทบทวนเอกสารที่จำเป็นต่อการพัฒนาระบบงานครั้งใหญ่ มีการประชุมซึ่งผมดันต้องมาเป็นหนึ่งในสมาชิกที่เข้าประชุมด้วย ผมเอง ขอสารภาพว่า เป็นคนที่ขี้เกียจทำงานเอกสารอย่างมากมาย จึงเสนอให้ยกเลิกเจ้า Program Spec. ตัวนี้เสียที เพราะไม่เคยสร้างประโยชน์ที่แท้จริงอะไรเลย (ยกเว้นอาจเพิ่ม man-days เพื่อนำไปเรียกเก็บเงินจากลูกค้าได้) ผลก็คือผมเป็นเสียงเดียวที่เห็นด้วยกับข้อเสนอนี้ (ฮา) ดังนั้น โดยหลักประชาธิปไตยที่ไม่ฟังเหตุผลของเสียงข้างน้อย ผมก็ต้องก้มหน้าก้มตาทำเอกสารไร้ค่าชิ้นนี้ต่อไป

เหตุผลของเสียงข้างมาก คือ เอกสารชิ้นนี้มีความจำเป็น เพื่อใช้ในการติดต่อสื่อสารกันระหว่าง System Analyst (SA) หรือคนออกแบบโปรแกรม กับ Programmer (PG) หรือคนเขียนโปรแกรม เมื่อมีปัญหาในตัวโปรแกรมที่เขียนขึ้น ทั้งสองฝ่ายจะได้ไม่ต้องเถียงกัน ยึดตัว program spec นี้เป็นหลัก และอีกเหตุผลสำคัญก็คือ บริษัทไหน ๆ เขาก็เขียนกันทั้งนั้น โดยเฉพาะบริษัทฝรั่งใหญ่ ๆ โต ๆ ทั้งหลาย เรียกว่า ต้องมีไม่งั้นไม่ professional ว่างั้นเหอะ

เหตุผลของเสียงข้างน้อย (ผมเอง) คือ เรื่อง pro ไม่ pro ขอพักไว้ก่อน แต่เท่าที่ทำงานมา ผมไม่เคยเห็น program spec ที่ไหนที่สามารถบรรลุวัตถุประสงค์ที่วางไว้ คือ ยุติข้อขัดแย้งระหว่าง SA กับ PG ได้ (ซึ่งเสียงใหญ่ในที่ประชุมบอกว่า program spec ที่ดี ๆ ใช้งานได้จริงก็มี แต่ผมไม่เคยเห็นเอง ก็อาจจะจริงครับ แต่ผมก็ทำงานในแผนกพี่นั่นแหละ อย่าลืมสิ ฮา)

จากประสบการณ์ของผม โปรเจ็คต์ที่จบได้สวย ๆ ไม่ว่าจะมี program spec หรือไม่ ก็ไม่มีข้อขัดแย้งระหว่าง SA กับ PG ขณะเดียวกัน โปรเจ็คต์ที่จบได้ห่วยหรือไม่มีทางจบแน่นอน ก็มักจะเกิดข้อขัดแย้งระหว่าง SA กับ PG ขึ้นแล้วสุดท้ายก็มาลงที่ Program Spec ว่า SA ไม่ยอมทำ หรือทำไม่ดี เขียนไม่ละเอียด (โอ้ พระเจ้า จะเอาละเอียดขนาดนั้น ตูเขียนโปรแกรมให้เองเลยดีกว่ามั้ง)

ประเด็นของผมก็คือ ข้อขัดแย้งระหว่าง SA กับ PG มีความสัมพันธ์โดยตรงกับความสำเร็จล้มเหลวของโปรเจ็คต์ เห็นได้ชัดเจน (แต่ประมาณ ไก่เกิดก่อนไข่ หรือไข่เกิดก่อนไก่ บอกไม่ได้ว่าอะไรเกิดก่อน) แต่ program spec ไม่ได้เกี่ยวอะไรด้วย เธอเป็นเพียงแพะที่น่าสงสารเท่านั้น

หาก SA กับ PG สามารถสื่อสารกันด้วยความเข้าใจ และมีพื้นฐานอยู่บนความเชื่อใจและวางใจซึ่งกันและกัน ข้อขัดแย้งย่อมไม่เกิดขึ้น ผมมี keyword อยู่สองคำ คือความเข้าใจ และความเชื่อใจ หากคนสองคนคุยกันภาษาเดียวกัน (สื่อสารสองทาง) แต่ยังไม่เข้าใจกัน แล้วคิดว่า คนหนึ่งไม่พูดไม่จา เขียนหนังสือส่งให้อีกคนอ่าน (สื่อสารทางเดียว) คนอ่านเค้าจะอ่านเข้าใจหรือครับ program spec ไม่ช่วยเรื่องความเข้าใจแน่นอน จริงอยู่ที่ว่าบางครั้งคำพูดอาจไม่เพียงพอสำหรับการสื่อสาร ต้องอาศัยภาษามือ ภาพประกอบช่วย แต่ไม่ได้หมายความว่านั่นต้องเป็นprogram spec เขียนขึ้นกระดานก็ได้ ชี้ ๆ เอาบน prototype ก็ได้

สำหรับความเชื่อใจยิ่งแล้วใหญ่ เพราะ program spec ลึก ๆ แล้วมีที่มาจากความไม่เชื่อใจกัน กลัวว่าพอมีปัญหา อีกฝ่ายจะโยนความผิดมาให้ตัวเอง หากเป็นการจ้างงานระหว่างสองบริษัท ผมเห็นด้วยเอกสารเป็นลายลักษณ์อักษร เป็นสิ่งจำเป็น แต่หากสิ่งนี้มีความจำเป็นระหว่างคนสองคนในที่ทำงานเดียวกัน ผมคิดว่ามีปัญหาความเป็นทีมเวอร์คในที่ทำงานนั้นแล้วครับ

มีอีกเรื่องที่ผมอยากเอามาวิเคราะห์ คือความละเอียดของ Program Spec หลาย ๆ ครั้งเมื่อปัญหาเกิดขึ้น ความละเอียดของ Program Spec ถูก request ถึงขั้นต้องเอา code บางส่วนมาใส่ไว้ให้เลย PG จะได้ copy ไปแปะ โปรแกรมจะได้ทำงานได้ตามที่ SA ต้องการ ให้เขียนสดแค่บางส่วนซึ่งทดสอบไม่ได้ แล้วหวังจะให้ code นั้นไม่มี bug ผมว่าคนที่พูดแบบนี้ ไม่เคยเขียนโปรแกรมครับ code ที่ปลอด bug ต้องผ่านการทดสอบบนข้อมูลเสมือนจริงจนครบทุกกรณีที่เป็นไปได้ สุดยอด SA ขนาดไหน ก็ไม่ไหวหรอกครับ สุดท้าย PG ก็ต้องนำ code นั้นไปปรับปรุงแก้ไขให้ถูกต้องอีกครั้งอยู่ดี แล้วอะไรจะเกิดขึ้นครับ มีทั้ง code ที่ใช้งานจริง กับ code ที่อยู่ใน spec แบบนี้ เขาเรียก denormalize นะครับ เกิด update anomalies แน่นอน แถมไม่มีประโยชน์ในแง่ query performance ด้วย ทำไปทำไมครับ

เพิ่งนึกได้ เค้าบอกผมอีกด้วยว่า Program Spec มีประโยชน์อีกในแง่เวลาต้องกลับมาแก้ไขโปรแกรม โดยคนอื่นที่ไม่ได้เป็นคนเขียน จะได้เข้าใจการทำงานของโปรแกรม เอ่อ แน่ใจหรือครับ เอ่อ แล้วพอแก้ไขโปรแกรมเสร็จ พี่แก้ไขใน spec ด้วยหรือเปล่าครับ แล้วคนที่มาแก้ไขเป็นคนที่สาม เขาจะรู้หรือครับว่าพี่แก้ไขอะไรไป นี่ก็ denormalize เหมือนกันครับ คำอธิบายการทำงานของโปรแกรม ควรจะอยู่ในตัวโปรแกรมเอง ไม่ว่าจะโดยการ comment หรือ refactoring ก็ว่ากันไป แก้เสร็จปุ๊บ ก็ใส่ comment ไปเลย ว่าใครแก้ แก้เมื่อไหร่ แก้ทำไม โปรแกรมที่ไม่มี comment ต่อให้มี program spec ก็ไม่มีประโยชน์หรอกครับ ยังไงก็ต้องนั่งไล่ logic ของคนเขียนคนแรกอยู่ดี ว่าเขาคิดอะไรอยู่ตอนเขียน

สุดท้ายก่อนจบ blog นี้ ผมเสนอให้ใช้เป็น job assignment แทน program spec ครับ เอาไว้กรณี PG หรือ SA ขี้ลืม จะได้ติดตามงานกันได้ รายละเอียดงานก็ใส่เท่าที่จำเป็น ทำเสร็จก็ปิด job ถ้าต้องมีการแก้ไขโปรแกรมตัวเดิมอีกครั้ง ก็ออก job ใหม่ ไม่ต้องไป refer ตัวเก่า ปิดแล้วก็ปิดเลย ไม่ต้องตามไป update ให้เสียเวลา แต่ผมคงต้องรอจนกว่าจะมีบริษัทเป็นของตัวเองมั้ง กว่าจะได้ใช้ concept นี้ ในการทำงานจริง เฮ้อออออออ ยาว ๆ สักที

ครั้งที่แล้ว ผมพูดถึงการ tune function และยกตัวอย่างให้เห็น ซึ่งหากกล่าวโดยสรุปคือ แนะนำให้พยายามใช้ built-in functions ใน sql statement แทน stored functions ทุกครั้ง หากเป็นไปได้

อย่างไรก็ดี การใช้ stored functions ก็มีข้อดีอยู่ ซึ่งผมลืมที่จะพูดถึงไป นั่นคือ หาก business requirement เปลี่ยน แทนที่เราจะต้องตามแก้ในทุก sql statement เหมือนการใช้ built-in เราก็เพียงแก้ source code ของ function แค่นั้นก็จะมีผลไปทุก statement ที่เรียกใช้

หากเรามีการใช้ built-in function ในการคำนวณที่เหมือน ๆ กันในหลาย ๆ statement และการคำนวณนั้นอาจมีการเปลี่ยนแปลง ในกรณีนี้ผมอยากแนะนำให้เราเขียน view ขึ้นมา และเก็บ definition ของ column ซึ่งได้จากการคำนวณนั้นไว้ใน view ครับซึ่งหากมีการเปลี่ยนแปลง เราก็แก้ไขที่เดียวเช่นเดียวกัน

อีกประเด็นหนึ่ง คือเราคงไม่สามารถใช้ built-in function แทน user defined function นี้ได้เสมอไป เช่น requirement นั้นยุ่งยาก หรือพิเศษพิสดาร ไม่เหมือนคนทั่วไป ถ้าเป็นเช่นนั้น สิ่งที่เราจะทำได้ดีที่สุดในการ tune คือการพยายามลดจำนวนครั้งของการ call ลงให้มากที่สุดแทนครับ ตัวอย่างเช่น sql statement ในครั้งที่แล้ว

select get_attime2(starttime), count(*) cnt
from tt1
group by get_attime2(starttime)
/

ถ้าเราเรียกใช้ฟังก์ชั่นที่ group by clause ฟังก์ชั่นนี้ก็จะถูกเรียกใช้หนึ่งครั้งต่อหนึ่งแถวของข้อมูล วิธีหนึ่งที่จะช่วยลดได้ก็คือ เขียนแบบนี้ครับ

select time_str, sum(cnt) total
from (
  select get_attime2(starttime) time_str, count(*) cnt
  from tt1
  group by starttime
)
group by time_str
/

โดยการเขียนแบบนี้ get_attime2 จะถูกเรียกเท่าจำนวนค่าที่ไม่ซ้ำทั้งหมดของ starttime หรือประมาณ 86,400 (24×60x60) เท่านั้น แทนที่จะถูกเรียก 500,000 ครั้งเท่าจำนวนแถวทั้งหมดของข้อมูล เวลาที่ใช้ตามที่ผมทดสอบดู ลงลงจาก 3.45 เหลือเพียง 0.9 วินาทีเท่านั้น

ในเรื่องของการลดจำนวนครั้งที่ call นี้ สิ่งสำคัญที่เราต้องทำความเข้าใจคือ ฟังก์ชั่นถูกเรียกใช้เมื่อไรบ้าง เมื่อเขียน statement ในลักษณะต่าง ๆ กัน ตัวอย่างเช่น

create or replace package pkg_test
is
  g_cnt    number ;
  function f_cnt
  ( p_num  number
  ) return number ;
end ;
/
create or replace package body pkg_test
is
  function f_cnt
  ( p_num   number
  ) return number is
  begin
    g_cnt := g_cnt + 1 ;
    return p_num ;
  end ;
end ;
/

 
function pkg_test.f_cnt จะ return ค่า input ที่ให้ไป แต่จะนับจำนวนครั้งเมื่อถูกเรียกใช้แต่ละครั้ง และเก็บไว้ในตัวแปร pkg_test.g_cnt เราลองมาทดสอบกันดู ด้วย statement ดังนี้

drop table tt1 purge
/
create table tt1
as
select rownum id, mod(rownum, 100)+1 grp
from all_objects
where rownum <= 50000
/
exec pkg_test.g_cnt := 0
select pkg_test.f_cnt(id)
from tt1
where grp = 1
/
exec dbms_output.put_line ( pkg_test.g_cnt )

เขียนแบบนี้ function ถูกเรียก 500 ครั้ง หรือเท่ากับจำนวนแถวที่ return ออกมา

exec pkg_test.g_cnt := 0
select id
from tt1
where grp = pkg_test.f_cnt(1)
/
exec dbms_output.put_line ( pkg_test.g_cnt )

เขียนแบบนี้ function ถูกเรียก 50,000 ครั้ง หรือเท่ากับจำนวนแถวที่อยู่ในตารางทั้งหมด หมายถึง oracle จะเรียกใช้ฟังก์ชั่นทุกครั้ง เพื่อทดสอบว่า row ใดเป็นไปตาม condition ที่เราระบุ อย่างไรก็ดีหากเราสร้าง index ให้กับ column grp การเรียกใช้ function จะเปลี่ยนไป โดยหาค่าผลลัพธ์ของฟังก์ชั่นออกมาก่อน แล้วนำค่านั้นไปใช้ในการ access index การเรียกใช้จะเหลือเพียง 2 ครั้งเท่านั้น (แต่ทำไมไม่เป็น 1 ครั้ง เท่านั้นผมก็ไม่ทราบเหมือนกัน เอาเป็นว่า 1 ครั้ง กับ 2 ครั้ง ผลต่างมันไม่มากนักก็ถือว่าโอเคครับ)

create index tt1_idx
on tt1(grp)
/
exec pkg_test.g_cnt := 0
select id
from tt1
where grp = pkg_test.f_cnt(1)
/
exec dbms_output.put_line ( pkg_test.g_cnt )

เอาไว้คราวหน้า เราจะมาทดสอบแบบนี้กับ deterministic function ดูครับ

ตามที่สัญญาไว้ในครั้งที่แล้ว ว่าจะยกตัวอย่างที่เคย tuning จริงมา post วันนี้ขอเริ่มด้วยกรณีการเรียกใช้ stored function ที่เขียนขึ้นเอง โดยไม่จำเป็น

ตัวอย่างนี้ เพิ่งได้มาสด ๆ ร้อน ๆ จากน้องในที่ทำงานเดียวกัน แต่อยู่คนละ project ซึ่ง project ของเขาเป็นโรงงานแห่งหนึ่ง ที่จริงเขาก็ยังไม่ได้ขอให้ tune query ให้ แค่ขอคำปรึกษาเรื่องการใช้ analytic function เท่านั้น แต่พอผมเห็น statement ของเขาแล้วมันอดไม่ได้ ก็เลยแนะนำเรื่องการ tune แถมให้ด้วยเลย

ฟังก์ชั่นของเขาจะรับค่าเวลาที่สินค้าถูกผลิตขึ้น และคำนวณว่าเวลานั้นตกอยู่ในช่วงใด ซึ่งแบ่งเป็นชั่วโมง ๆ หน้าตาเป็นแบบนี้ครับ

CREATE OR REPLACE FUNCTION GET_ATTIME1
( start_time IN NUMBER
) RETURN varchar2 IS
  rs_time VARCHAR2(6) ;
BEGIN
  if start_time = 80000 then
    rs_time:= '08:00';
  end if;
  if start_time >= 80001 and start_time <= 90000 then
    rs_time:= '09:00';
  end if;
  ...
  if (start_time >= 230001 and start_time <= 240000)
    or start_time = 0 then
    rs_time:= 't0:00';
  end if ;
  if start_time >= 1 and start_time <= 10000 then
    rs_time:= 't01:00';
  end if ;
  ...
  if start_time >= 70001 and start_time <  80000 then
    rs_time:= 't08:00';
  end if ;
  RETURN rs_time;
end ;
/

จากนั้นก็นำไปเรียกใช้ในลักษณะดังนี้

select get_attime1(starttime), count(*) cnt
from tt1
group by get_attime1(starttime) ;

จะเห็นว่าหนึ่งแถวใน tt1 ก็จะมีการเรียกใช้ function หนึ่งครั้ง ดู ๆ ไป ก็ไม่ได้ซับซ้อนอะไรนัก แต่จะมีผลต่อ performance หรือไม่ ขึ้นกับจำนวนแถวทั้งหมดใน tt1 ผมได้ลองทดสอบโดยสร้างข้อมูลขึ้นมาแบบสุ่ม จำนวน 500,000 แถว เพื่อจะทดลองผลของการ tuning (ดู code ทั้งหมด ได้ ที่นี่ ครับ )

ขั้นแรก พิจารณาในตัว code ที่น้องเขาเขียน จะเห็นว่า ใช้ if ย่อย ๆ หลาย if แทนที่จะใช้ elsif จุดนี้จะช่วยตัดทอนการทำงานที่ไม่จำเป็นออกได้ เนื่องจากถ้าใช้ if เราต้องทำการเปรียบเทียบทุกกรณีที่ระบุ แต่ถ้าเอา elsif เข้ามาช่วย เมื่อเจอกรณีที่ให้ค่าจริงปุ๊บ ก็หยุดปั๊บ ไม่ทำงานส่วนที่เหลือทันที (ดูที่ code get_attime2 ) ผลลัพธ์ที่ทดลองบนเครื่องของผม เวลาลดลงจาก 3.75 วินาที เหลือประมาณ 3.3 วินาที

อย่างไรก็ดี วิธีการที่ผมใช้ในการ tune จริง ๆ คือการเปลี่ยนมาใช้ buit-in function ล้วน ๆ ครับ จาก code ข้างบน ผมเขียนโดยใช้ sql function ได้ดังนี้

select (case
          when ( starttime < 80000 or starttime >= 230001)
            then 't'
          else null
        end)
       ||to_char(mod(ceil( starttime/10000 ),24),'fm09')||':00'
  , count(*) cnt
from tt1
group by (case
          when ( starttime < 80000 or starttime >= 230001)
            then 't'
          else null
        end)
       ||to_char(mod(ceil( starttime/10000 ),24),'fm09')||':00'  ;

ดูยุ่งยากกว่าเยอะนะครับ ใครเห็น code นี้คงมึนไปเหมือนกันว่าคนเขียนอยากทำอะไร แต่ผลลัพธ์ของ query นี้ได้เหมือนเดิมเป๊ะ ในเวลาเพียงประมาณ 0.75 วินาทีครับ

เวลาที่ลดลงไปเป็นผลมาจากการที่เราลดการ switching ระหว่าง sql engine กับ pl/sql engine ครับ ถ้าเทียบเวลาที่ใช้ในการคำนวณด้วยฟังก์ชั่นที่ผมเขียนกับการใช้ if แล้ว การคำนวณผมช้ากว่าด้วยซ้ำ ดูได้จาก function get_attime3 ซึ่งผมเขียนโดยเลียนแบบ built-in function ที่ผมใช้ ผลที่ได้ใช้เวลาถึง 5.5 วินาทีด้วยซ้ำ ดังนั้นหากฟังก์ชั่นนี้ถูกเรียกใช้ใน pl/sql เป็นหลักแทนที่จะใช้ใน sql statement การใช้ get_attime2 ก็จะให้ผลลัพธ์ที่ดีกว่า

หนังสือหลาย ๆ เล่มพูดถึงการ tuning function ด้วยการกำหนดให้ function นั้น เป็น deterministic function ลองดูว่าผลลัพธ์เป็นอย่างไร ผมใช้ code เดียวกับ get_attime2 มาสร้างเป็น deterministic function get_attime4 ผลลัพธ์ที่ได้ผมมองไม่เห็นความแตกต่างเลยครับ เรื่อง deterministic function นี้ผมว่าผมเอามาเขียนเป็นอีกหนึ่งหัวข้อได้เลย เพราะฉะนั้นอดใจรอหน่อยแล้วกันนะครับ

ถ้าคุณคิดว่า tune แค่นี้ก็โอเคแล้ว ยังครับ ยังมีอีกหนึ่ง trick เอาผลลัพธ์ของฟังก์ชั่นมาเก็บไว้ในตารางก่อน แล้ว join เอา เนื่องจาก input ของฟังก์ชั่นนี้มันมีจำนวนจำกัด แค่เวลาใน 24 ชั่วโมงเท่านั้น ตารางที่ใช้ในการเก็บผลลัพธ์ของเราก็ไม่ได้ใหญ่โตอะไร (ตาราง tt2) ผลลัพธ์จากการ join ที่หลายคนกลัวกันนักหนา เหลือเพียง 0.5 วินาทีครับ อย่างไรก็ดี trick นี้ เปลี่ยนจาก cpu load มาเป็น I/O load ดังนั้นก็อยู่ที่ server ของคุณว่า bottleneck อยู่ที่ใด ต้องทำการเปรียบเทียบเองครับ แต่ก็ถือเป็นอีกหนึ่งทางเลือกได้

น่าสนใจไหมครับ ถ้าเห็นว่าน่าสนใจ comment ติชมกันหน่อยนะครับ แล้วคราวหน้าผมจะนำตัวอย่างอื่น ๆ มาให้ดูอีกครับ

ขอบคุณครับ

จากสถิติของบล๊อก ผมพบว่าหลายคนหลงเข้ามาที่นี่จากการค้นหาคำว่า sql tuning แสดงว่ามีคนสนใจในด้านนี้อยู่พอสมควรทีเดียว น่าเสียดาย ที่เขาต้องกลับไปมือเปล่า เพราะผมยังไม่ได้เขียนอะไรเป็นชิ้นเป็นอันเกี่ยวกับการ tuning เลย (ขออภัยเป็นอย่างยิ่ง แหะ ๆ)

เอาเป็นว่า วันนี้ผมจะเล่าสู่กันฟังเกี่ยวกับเทคนิคการ tuning ที่ผมเคยใช้มานะครับ แต่ขอออกตัวก่อนว่า ผมไม่เคยทำงานในตำแหน่ง DBA ซึ่งเป็นตำแหน่งที่น่าจะมีโอกาสได้ทำงาน tuning ค่อนข้างมาก เพียงแค่ผมชอบทางด้านนี้ เพื่อน ๆ น้อง ๆ รวมถึงคนรู้จักทั้งหลาย ก็เลยมาใช้บริการกันบ่อย ๆ (ฟรีด้วย ฮึ่ม) ซึ่งก็ยังเป็นเพียงประสบการณ์น้อยนิดเท่านั้น ยอมรับว่ายังเป็นกบในกะลาอยู่มาก ๆ

อันดับแรก เมื่อผมได้ sql ที่ต้องการ tune มา เกือบครึ่งหนึ่งแค่ดู sql ก็รู้ปัญหาเลย เนื่องจากข้อผิดพลาดอยู่ที่วิธีการเขียน เช่น

• การเรียกใช้ stored function ที่เขียนขึ้นเอง โดยไม่จำเป็น
• การ access ตารางเดียวกันหลาย ๆ ครั้ง ซึ่งผมพบว่าใช้ analytic functions แทนได้
• การใช้ not in โดยไม่คำนึงกรณีค่า null ที่อาจ return ได้จาก subquery
• การใช้ function ครอบ column ที่มี index รวมถึงการแปลงชนิดของข้อมูลแบบไม่ตั้งใจด้วย
• การใช้ outer join โดยไม่จำเป็น
• การใช้ view อย่างฟุ่มเฟือยเกินเหตุ
• อื่น ๆ ที่ผมยังนึกไม่ออกตอนนี้

ส่วนที่เหลือนอกจากนั้น แสดงว่า sql ดูเป็นปกติดี จุดแรกที่ผมจะตรวจสอบคือ มีการเก็บ stats เอาไว้หรือยัง และยังทันสมัยอยู่หรือไม่ ถ้าไม่ก็ดำเนินการเสียให้เรียบร้อย แล้วรันดูอีกครั้ง ดูความเปลี่ยนแปลง ถ้า stats โอเคอยู่แล้ว ก็ถึงเวลาไล่ execution plan

จาก execution plan ผมจะมองหา step ที่น่าจะเป็นปัญหา เช่น full scan table, nested loop join, sort operation โดยศึกษาประกอบกับข้อมูลจริง เมื่อเจอตัวปัญหาแล้วก็ต้องพยายามเปลี่ยน plan ด้วยวิธีการต่าง ๆ นานา เช่น re-write query รวมถึงการใช้ hints

ด้วยวิธีการต่าง ๆ นี้ ผมแก้ปัญหาที่เคยเจอมาได้เกือบทั้งหมด ผมไม่ค่อยอยากพึ่ง tools ประเภท tuning advisor สักเท่าไหร่ เพราะ tools พวกนี้ชอบลัดขั้นตอนไป step สุดท้ายเลย และผมคิดว่าผลลัพธ์ที่ได้มักจะยังไม่ดีถึงที่สุด

วันนี้ เกริ่นสั้น ๆ แต่เพียงเท่านี้ ครั้งหน้าจะยกตัวอย่างปัญหาที่เกิดจากวิธีเขียนมาให้เห็นกันชัด ๆ ทีละปัญหา ขอบคุณที่ติดตามล่วงหน้าครับ

ด้วยจิตคารวะ

เมื่อวาน น้องคนหนึ่งทักมาว่า เลิกเขียน blog แล้วหรือ? เอ่อ.. ยังครับ แค่ยุ่ง ๆ และนึกเรื่องที่จะเขียนไม่ออกเท่านั้น ว่าแล้วก็เลยถามกลับไปว่า อยากอ่านเรื่องเกี่ยวกับอะไร? น้องเค้าก็เลยตอบว่าอยากให้เล่าประสบการณ์การ tune ที่ทำแล้วผลลัพธ์เปลี่ยนจากหน้ามือเป็นหลังมือ หรือ features ใหม่ ๆ ประมาณ advanced SQL ทำนองนั้น ฟังแล้วก็นึกไม่ออกอยู่ดีว่าจะเขียนอะไร แหะ ๆ

แต่หลังจากคุยกับน้องเค้าแล้ว ก็นึกได้ว่าครั้งหนึ่งเคย tune sql ให้เค้า ซึ่งเค้าพยายามใช้ hint บังคับ oracle ให้ใช้ index แต่ oracle ไม่ยอมทำตาม hint ที่ให้ไป ทั้ง ๆ ที่เค้าก็ตรวจสอบแล้วว่า syntax ถูกต้อง อย่ากระนั้นเลย วันนี้เล่าเรื่อง hints หรือชื่อเต็มว่า optimizer hints ซะหน่อย น่าจะเข้าท่าดี

hints เป็นด่านแรกที่บรรดาชาว tuning มือใหม่มักให้ความสนใจ อยากศึกษา อยากเข้าใจ และอยากใช้เป็น อาจเป็นเพราะเคยพบ code เก่า ๆ ที่ถูก tune มาแล้ว จะมีการใส่ hints เอาไว้ เพื่อชี้นำ optimizer ให้เลือก execution plan ตามที่เราต้องการ บางครั้งก็ได้ผลดี แต่บางครั้งก็ไม่เป็นอย่างนั้น

ผมเคยอ่านบทความเรื่อง Does the optimizer need a clue? ของ NOCOUG (Northern California Oracle Users Group) พบคำอธิบายเรื่องของ hints ของคุณ Gaja Krishna Vaidyanatha เขาเปรียบเทียบไว้ดีมาก อ่านปุ๊บ น่าจะนึกภาพออกทันทีเลย (โดยเฉพาะท่านสุภาพบุรุษที่มีแฟนแล้ว)

เขาว่า hints นี้ก็เหมือนคำพูดลอย ๆ ของคุณสุภาพสตรี เวลาที่อยากให้เราทำอะไรให้ ไม่ใช่ประโยคคำสั่ง แต่ก็ไม่ใช่ประโยคบอกเล่า ถ้าไม่ทำอาจมีเคือง ดังนั้นพวกเราสุภาพบุรุษที่มีสติปัญญาปกติทุกคน ล้วนทราบดีว่า เป็นการดีที่สุดที่เราจะเคารพในคำพูดนั้น และปฏิบัติตามความต้องการของเธอแต่โดยดี (ผมอ่านแล้วฮามาก โดนเต็ม ๆ อิอิ)

อย่างไรก็ดี ในบางสถานการณ์ ตัวอย่างเช่น เวลาที่เรากำลังนั่งอยู่หน้าจอทีวี ดูรายการแข่งขันฟุตบอลทีมโปรดที่กำลังเข้าด้ายเข้าเข็ม ในวินาทีดับจิตที่นักฟุตบอลกำลังวิ่งเข้าไปยิงประตูนั้น แฟนของเราพูดขึ้นมาว่า “พี่ต้อม ลูกหิว” ซึ่งในสถานการณ์ปกติแล้ว เป็น hint ที่ถูกต้อง เหมาะสม และผมควรจะลุกขึ้นไปชงนมทันทีที่ได้ยิน แต่ในวินาทีสำคัญขนาดนั้น ก็เป็นไปได้ว่า ผมไม่ได้ยินคำพูดของเธอ หรือได้ยินแต่สมองตีความว่าคำพูดนั้นไม่ถูกต้อง (อย่างน้อย มันก็ไม่ถูกต้องตามกาละเทศะ จริงไหมครับ คุณผู้ชาย)

นั่นแหละครับ optimizer ก็เหมือนกับเราท่านทั้งหลาย ทำสิ่งที่ตัวมันเองคิดว่าถูกต้อง แต่ส่วนใหญ่ 99% การปฏิบัติตาม hints เป็นสิ่งที่ถูกต้อง และปลอดภัยที่สุด (ผิดพลาดอย่างไร คนสั่งนั่นแหละต้องรับผิดชอบ) ปัญหาอยู่ที่ 1% ที่เหลือ ถ้าเป็นเรื่องสำคัญ ภรรยาผมต้องเดินมากำกับอีกรอบ ว่าผมรับคำสั่งเธอไปปฏิบัติจริงหรือไม่ ซึ่งเหมือนกับเวลาเราใส่ hints ลงไปแล้ว ก็ควรสำรวจด้วยคำสั่ง explain plan อีกครั้งว่าผลลัพธ์เป็นไปตามที่เราต้องการหรือยัง

มีกรณีใดบ้าง ที่ optimizer จะไม่เชื่อฟัง hints ที่เราให้ หรือไม่ให้ผลลัพธ์ตามที่เราต้องการ

• ข้อแรก syntax ผิด อ่านแล้วไม่รู้เรื่อง แน่นอนว่า optimizer ทำตามไม่ได้ เพราะไม่รู้ว่าวัตถุประสงค์คืออะไร
• ข้อสอง hint ที่ให้ขัดแย้งกันเอง เช่นสั่งทั้ง use_nl (a b) กับ use_hash (a b ) ใน statement เดียว กรณีแบบนี้ optimizer จะไม่เลือกทำ hint ใด hint หนึ่ง แต่เลือกไม่สนใจทั้งคู่ แล้วหาทางใหม่เอาเอง
• ข้อสาม hint นั้นเป็นไปไม่ได้ในทางปฏิบัติ เช่นบังคับใช้ index บนเงื่อนไขที่ไม่ได้ดึงค่าจาก column ตรง ๆ where col_x + 1 = 5 แบบนี้ optimizer ก็มองว่า hint นั้นไร้สาระ และไม่ทำตามเช่นเดียวกัน กรณีของน้องคนที่เล่าให้ฟังตอนต้น ก็เข้าข่ายนี้
• ข้อสุดท้าย hint ที่เราให้ไม่ละเอียด และรัดกุมพอ ทำให้ optimizer มีทางเลือกอื่นที่ไม่เป็นไปตามที่เราตั้งใจ ตัวอย่างเช่น query ที่มีการ join table t1, t2 ถ้าผมกำหนด hint ว่า use_hash (t2) เพียงเท่านี้ เป็นไปได้ว่า plan ที่ออกมาอาจไม่ใช้ hash join ก็ได้ ถ้า optimizer พบว่ามีทางอื่นที่ทำได้โดยไม่ขัดคำสั่งเรา และให้ cost ที่ต่ำกว่า เช่นใช้ t2 เป็นตัวตั้ง แล้วนำ t1 เข้ามา join แบบ nested-loop เป็นต้น ( hint use_hash(t2) หมายความว่า ถ้าจะนำ t2 เข้ามา join ให้ใช้วิธี hash join เท่านั้น แต่ถ้าใช้ t2 เป็นตัวตั้ง ก็ไม่เกี่ยวข้องกัน)

นอกเหนือจากนี้แล้ว optimizer จะเป็นเด็กค่อนข้างว่าง่ายทีเดียว ไม่ดื้อ ไม่เถียง ทำตามอย่างเดียวเลย

อย่างไรก็ดี มีคำเตือนว่า อย่าใช้ hints เป็นทางเลือกแรกในการแก้ปัญหา performance ต้องมองหาสาเหตุที่แท้จริงก่อนว่าทำไม optimizer ไม่สามารถเลือก plan ที่ดีที่สุดให้กับเรา เพราะนี่เป็นหน้าที่โดยตรงของมัน ลองดูว่า statistics ที่เราเก็บมีปัญหาหรือเปล่า หรือปัญหาอยู่ที่ statement ของเราเอง แล้วแก้ปัญหาที่ต้นเหตุ

หากเราใช้ hint เราต้องเตรียมตัวเตรียมใจกับการปรับแก้โปรแกรมเมื่อสถานการณ์ต่าง ๆ เปลี่ยนไป ไม่ว่าจะเป็นลักษณะของข้อมูล version ของฐานข้อมูล แม้กระทั่งการลง patch ตัวใหม่ ก็อาจก่อให้เกิดปัญหาได้ ทาง oracle เองไม่เคยการันตีผลลัพธ์จากการใช้ hints เพราะฉะนั้นทุกอย่างเปลี่ยนแปลงได้เสมอ

เอาไว้ถ้ามีโอกาส ผมจะเล่าให้ฟังถึง hint ที่มีประโยชน์บางตัวที่ผมเคยใช้อยู่ นะครับ สำหรับวันนี้ สวัสดีครับ

ห่างหายไปนาน ไม่ได้มาเขียนอะไรเพิ่มเติม แถมยังมีการลบหัวข้อที่เคย public ไปแล้วอีกต่างหาก สาเหตุที่เอาหัวข้อนั้นออกไป เนื่องจากผมได้ไปอ่านข้อเขียนของคุณ Richard Foote ว่าแม้แต่กรณีที่ตารางมีขนาดเล็กมาก ๆ (ข้อมูลแค่ 1 data block) การใช้ index ก็ยังอาจจะได้เปรียบการ full scan table อยู่ดี แถมแสดงตัวอย่างให้ดูเสร็จสรรพ ทำลายความเชื่อมั่นของผมลงในทันที แต่ก็เพิ่มความมั่นใจในคำกล่าวของThomas Kyte ที่ว่า “คุณสามารถเรียนรู้เรื่องใหม่ ๆ จาก oracle ได้ทุกวัน” ผมจึงขอดึงหัวข้อนั้นกลับมาทบทวนแก้ไขก่อน public ใหม่ เพื่อไม่ให้ผู้อ่านรับรู้ข้อมูลผิด ๆ ตามผม ใครที่อ่านไปแล้วก็ขออภัยไว้ ณ ที่นี้ครับ

สำหรับวันนี้ขอกลับมาเรื่องพื้นฐานก่อนน่าจะดีกว่า ในช่วงอาทิตย์ที่ผ่านมา ผมได้มีโอกาสสอนรุ่นน้องในแผนก ในหลักสูตร Oracle SQL Fundamention I สังเกตเห็นพฤติกรรมบางอย่าง ซึ่งน่าจะเป็นจุดอ่อนของโปรแกรมเมอร์หลาย ๆ คน เลยอยากแสดงความคิดเห็นในเรื่องนี้สักเล็กน้อย 

พฤติกรรมดังกล่าวคือ การไม่อ่าน error message หรืออ่าน error message ไม่เป็น นอกจากนักเรียนบางคนในวันนั้น ผมยังพบบ่อย ๆ ว่าน้องที่เข้ามาตั้งกระทู้ถามปัญหาตามเว็บบอร์ดมักละเลยที่จะให้รายละเอียดเกี่ยวกับ error message ที่เขาพบ ต้องให้พวกเราย้อนถามกลับไปเสมอ

ผมคิดว่าไม่ว่าจะเป็นโปรแกรมเมอร์ฝีมือดีขนาดไหน มีประสบการณ์ช่ำชองกี่สิบปีก็ตาม ทุกคนต้องถูกคอมพิวเตอร์ประณาม (ด้วย error message) มาแล้วทั้งนั้น ทุกวันนี้ผมก็ยังโดนอยู่เป็นประจำ แต่ข้อแตกต่างระหว่างมือใหม่กับมือเก่านั้นจะเห็นได้ชัดตรงที่มือเก่าจะรู้ว่า error message นั้นจะช่วยเราแก้ปัญหาได้อย่างไร บางครั้งผมยังอยากวัดฝีมือคนที่เข้ามาสมัครงานใหม่ด้วย error message แต่เพียงอย่างเดียวเลย

การอ่าน error message เปรียบเสมือนการรู้จักเรียนรู้จากข้อผิดพลาด มีคำกล่าวว่า คนที่ไม่รู้จักเรียนรู้จากข้อผิดพลาดนั้น ไม่ว่าทำงานมาแล้วกี่สิบปี ประสบการณ์ก็มีค่าเท่ากับศูนย์

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

เรื่องนี้ทำให้ผมย้อนนึกไปถึงครั้งหนึ่งที่เคยช่วยรุ่นน้อง debug โปรแกรม เขาติดอยู่นานกับ error ง่าย ๆ ORA-06502 numeric or value error เนื่องจาก error นี้เกิดได้หลายสาเหตุ และเขาอ่านคู่มือมาไม่ดี เลยมองหาแต่เรื่องการ conversion จาก string ไปเป็น number ทั้ง ๆ ที่จริง แล้วจุดที่ผิดนั้นเกิดจากการประกาศขนาดตัวแปรไว้ไม่เพียงพอ หาแทบตายก็เลยไม่เจอซักที แบบนี้ถือว่าพอใช้ได้ เพราะพยายามอ่านแล้ว แต่ message อาจไม่ชัดเจน เข้าใจผิดเลยแก้ปัญหาไม่ได้ คาดว่าตอนนี้คงจำ error รหัสนี้ได้ขึ้นใจเลย  

ส่วนอีกเคสหนึ่ง เป็น error message ที่จัดว่ามีชื่อเสียงหรือชื่อเสียมากพอดู 

ORA-01555 snapshot too old: rollback segment number … too small

error นี้พา DBA เก่ง ๆ หลงทางกันมาแล้วหลายคน เพราะไม่ได้บอกสาเหตุที่แท้จริงของปัญหา ก็เลยพากันขยาย rollback segment แต่ขยายเท่าไหร่ปัญหาก็ไม่จบ บางคนยิ่งแก้ยิ่งยุ่ง เพราะไม่เหลือพื้นที่แล้ว เลยพยายาม commit ถี่ ๆ จะได้ใช้ rollback segment น้อยลง ปัญหาคือความจริงแล้ว rollback segment ที่ว่า too small นั้นเกิดจากการพยายามอ่านข้อมูลจาก segment นั้นแต่ไม่พบข้อมูลที่ต้องการ เนื่องจากถูกคนอื่นเขียนทับไปแล้ว oracle ก็เลยสรุปเอาเองว่ามันเล็กเกินไป เลยต้องมาเขียนทับกัน ซึ่งไม่ถูกต้องซะทีเดียว เนื่องจากการที่คนอื่นมาเขียนทับได้นั้น เกิดจากเราอนุญาตให้เขามาเขียนทับเองต่างหาก ซึ่งก็คือการ commit นั่นเอง พอยิ่ง commit ถี่ปัญหาก็เลยไม่จบ

ยิ่งเขียนยิ่งออกนอกประเด็น ผมขอสรุปง่าย ๆ ว่า การอ่าน error message ให้เป็นมีความสำคัญมาก เป็นจุดวัดฝีมือของโปรแกรมเมอร์กันเลยทีเดียวนอกเหนือไปจากความชำนาญในการเขียนโปรแกรม ดังนั้นหากคุณเป็นคนหนึ่งที่อยู่ในช่วงเริ่มต้น ขอให้ใส่ใจให้มากเป็นพิเศษ ทางลัดหนึ่งที่ผมพอจะแนะนำได้ก็คือการทำความเข้าใจ และเรียนรู้จากข้อผิดพลาดนี่แหละ จะทำให้เราเป็นคนเก่งได้เร็วขึ้น

ด้วยจิตคารวะ

มีคนใกล้ตัวบางคน (ที่ผมบังคับให้เข้ามาอ่าน) บอกว่า ประโยคนี้ในหัวข้อก่อน อ่านแล้วไม่เข้าใจ

นอกจากนั้นข้อแตกต่างเล็ก ๆ น้อย ๆ ระหว่าง query 2 query จะเกิดขึ้นกับความเป็นไปได้ของข้อมูลบางชุดเท่านั้น เราต้องเข้าใจถึงลักษณะของข้อมูลที่เรามีอยู่ด้วย

นั่นสิ ผมอ่านที่ตัวเองเขียนอีกรอบ ก็เห็นด้วยกับเขาเหมือนกัน เพราะฉะนั้นคราวนี้ผมจะลองขยายความเพิ่มเติม พร้อมกับยกตัวอย่างที่หลายคนน่าจะเคยเจอกันมาบ้าง ให้ดูด้วย

จริง ๆ ครั้งที่แล้ว ผมตั้งใจจะบอกว่า ในวิธีการเขียนของ SQL ที่หลากหลายนั้น บาง syntax ให้ผลลัพธ์ที่คล้ายคลึงกันมาก จนบางครั้ง คนบางคนเข้าใจผิดคิดว่าผลลัพธ์ที่ได้เหมือนกันเปี๊ยบเลยก็มี ทั้งนี้เป็นเพราะความแตกต่างที่จะเกิดขึ้นนั้น จะเกิดในกรณีที่ข้อมูลมีลักษณะเฉพาะบางประการเท่านั้น ผลที่ตามมาคือ oracle เลือกใช้วิธีการที่แตกต่างกันอย่างสิ้นเชิงในการหาผลลัพธ์ออกมาให้กับเรา และมีผลกับความเร็วโดยตรง

อย่างไรก็ดี เราในฐานะที่เป็นเจ้าของข้อมูล เราอาจจะรู้และรับรองได้ว่าข้อมูลในลักษณะนั้น ๆ ไม่มีทางเกิดขึ้นได้ในข้อมูลจริง (พูดง่าย ๆ บางครั้ง oracle ก็คิดมากและฉลาดเกินไป) เราจึงต้องรับผิดชอบโดยการเลือกวิธีการเขียนที่เหมาะสมกับข้อมูลของเราจริง ๆ หรือบอกให้ oracle รับรู้ข้อเท็จจริงเดียวกับเราโดยผ่าน statistics หรือ integrity constraints (โดยเฉพาะเรื่อง constraints นี้ ผมคงใช้เขียนได้อีกหลายวันทีเดียว)

ผมขอยกตัวอย่างง่าย ๆ ให้พิจารณาสักตัวอย่างสองตัวอย่าง ตัวอย่างแรกเป็นการใช้ not in, not exists และ minus

select t1_id
from t1
where t1_id not in ( select t2_id from t2 ) ;

select t1_id
from t1
where not exists
( select null from t2 where t2_id = t1.t1_id ) ;

select t1_id
from t1
minus
select t2_id
from t2 ;

สาม statement นี้ ดูเผิน ๆ จะเหมือนว่าได้คำตอบเดียวกัน แต่จริง ๆ แล้วไม่ใช่ ผมสามารถแสดงให้ดูด้วยชุดข้อมูลดังนี้

insert into t1 (t1_id) values (1) ;
insert into t1 (t1_id) values (1) ;
insert into t1 (t1_id) values (2) ;
insert into t2 (t2_id) values (2) ;
insert into t2 (t2_id) values (null) ; 

ข้อมูลบรรทัดที่สอง จะทำให้ minus ให้ผลลัพธ์ที่แตกต่าง เพราะ t1_id มีค่าซ้ำ ขณะที่ผลลัพธ์จาก set operators ทุกตัวยกเว้น union all จะถูกกำจัดค่าซ้ำก่อนเสมอ (select distinct … ประมาณนั้น) จึงได้ผลลัพธ์เพียง 1 แถว ส่วน not in กับ not exists ซ้ำก็ซ้ำ ไม่สนใจ แสดงทั้งหมด ส่วนข้อมูลบรรทัดที่ห้า จะทำให้ not in ให้ผลลัพธ์ที่แตกต่าง เพราะถ้าใช้ not in แล้ว ค่าที่ return จาก subquery ต้องไม่มีค่า null มิฉะนั้น ผลลัพธ์จะเป็น 0 แถวเสมอ ดังนั้นผลลัพธ์จากสาม query เมื่อกระทำกับข้อมูลชุดนี้แล้ว จะได้ไม่เท่ากันเลย

อย่างไรก็ดี ถ้าทั้งคอลัมน์ t1_id และ t2_id เป็น primary key ของตาราง t1 และ t2 ตามลำดับ จะเห็นได้ว่าข้อมูลชุดที่เรายกมาไม่มีทางเป็นจริงไปได้ เพราะ primary key จะต้องไม่ซ้ำและมีค่าเสมอ เป็น null ไม่ได้

นี่คือเหตุผลว่าทำไมเราจึงต้องทำความรู้จักกับข้อมูลของเราด้วย ในการ tuning query แบบนี้ผมไม่สามารถที่จะให้คำแนะนำที่เฉพาะเจาะจงได้ว่า query ทั้งสามนี้ ใช้แบบไหนดีกว่ากัน เพราะว่ามันให้คำตอบที่แตกต่างกันจึงไม่สามารถเปรียบเทียบกันได้ จะเลือกเขียนด้วยวิธีไหนขึ้นอยู่กับว่าข้อมูลของเรามีลักษณะอย่างไรต่างหาก

ตัวอย่างที่สอง หลายคนอาจจะเคยใช้อยู่บ่อย ๆ และสงสัยว่าแบบไหนดีกว่ากัน

select *
from t1
where :p_id is null or id = :p_id  ;

select *
from t1
where id = nvl(:p_id, id) ;

สอง query มักถูกใช้เมื่อเราต้องการสร้างรายงาน ที่ผู้เรียกดูรายงานสามารถที่จะระบุ หรือไม่ระบุค่าพารามิเตอร์ก็ได้ ถ้าไม่ระบุก็ให้แสดงข้อมูลทั้งหมด คุ้น ๆ บ้างไหมครับ รู้ไหมครับว่า สอง query นี้แตกต่างกันตรงไหน

ค่า null อีกแล้วครับท่าน กติกาของ SQL ข้อหนึ่ง null ไม่เคยเท่ากับอะไรเลย แม้แต่ตัวมันเอง เงื่อนไข (null = null) จะให้ค่าเป็น null ไม่ใช่ true

ดังนั้นหากเราไม่ระบุค่าพารามิเตอร์ และในข้อมูลของเรา มีแถวที่ id มีค่าเป็น null รวมอยู่ด้วย query แรกจะแสดงผลทุกแถวจริง ๆ รวมทั้งแถวที่ id เป็น null ขณะที่ query ที่สองจะแสดงผลแค่แถวที่ id มีค่าเท่านั้น แถวที่ id เป็น null จะไม่แสดงออกมา

เป็นอย่างไรบ้างครับ ตัวอย่างที่ยกมาพอจะทำให้เห็นภาพไหมครับ ว่าเราต้องรู้อะไรบ้างก่อนจะทำการ tune query นอกจากนี้แล้วยังมีอีกหลายกรณี แต่วันนี้ผมยังนึกไม่ออก เอาไว้วันไหนนึกได้ แล้วจะกลับมาเล่าสู่กันฟังอีกรอบนะครับ

ด้วยความเคารพอย่างสูง

จริง ๆ แล้ว ผมเพิ่งมีโอกาสทำงาน tuning จริงจัง แค่ประมาณสามปีเท่านั้นเอง ครั้งแรกที่ทำก็ไม่ได้มั่นใจอะไรมากมาย แต่ก็สร้างผลงานได้น่าประทับใจไม่น้อย จากประสบการณ์ครั้งนั้นไม่นาน ก็มีหลายคนเรียกผมเป็น tuning specialyst (โปรแกรมที่ผมแก้มันรันเร็วขึ้น 80-95%) อันที่จริงมันก็ไม่ใช่ฝีมือของผมซะทีเดียว ต้องขอบคุณโปรแกรมเมอร์คนแรกที่เขียนให้มันรันได้ช้าขนาดนั้นต่างหาก (โปรแกรมนั้นฝรั่งเขียนด้วยนะ อีกครั้งที่ขอยืนยันว่า คนไทยไม่แพ้ชาติใดในโลก )

คำถามที่ตามมาบ่อยครั้ง หลังการ tuning คือ “ทำได้ยังไงเนี่ยะ” เพราะดู ๆ แล้วเหมือนกับเราใช้เวทมนตร์เสกเอา กลายเป็นผู้วิเศษกันไปเลยทีเดียว ไม่ใช่แน่นอน tuning ไม่ใช่ไสยศาสตร์มนต์ดำ เป็นวิทยาศาสตร์แท้ ๆ แม้ว่าอาจเจือความเป็นศิลปินเข้าไปบ้างนิด ๆ หน่อย ๆ

พวกเรามักจะถามหาสูตรสำเร็จของการ tuning ซึ่งในความเป็นจริงนั้น ไม่มีสูตรสำเร็จที่ตายตัว ยืนยันโดยคำกล่าวของ Tom Kyte เจ้าพ่อแห่งวงการนี้ ว่าถ้าสรุปเป็นขั้นตอนตายตัวได้ ต่อให้มีเป็นแสนเป็นล้านขั้นตอน คงกลายเป็น software สำเร็จรูปราคาแพงไปเรียบร้อยแล้ว

อย่างไรก็ดี ใน link ข้างบนนี้ Tom ได้สรุปพื้นฐานที่สำคัญสำหรับผู้ที่อยากประสบความสำเร็จในงาน tuning เอาไว้ และผมคิดว่าเขาเขียนได้ดีทีเดียว น่าจะนำมาแบ่งปันให้คนไทยที่ไม่นิยมอ่านเว็บต่างประเทศ (คงเป็นคนชาตินิยมสูง น่านับถือทีเดียว แฮ่ ๆ) ได้อ่านกัน

1. It is a matter of knowing all of the available access paths and when to use them and how to make them available. เราต้องรู้จักและเข้าใจ access paths หรือวิธีการที่ฐานข้อมูลใช้ในการเข้าถึงข้อมูลที่เราต้องการ oracle มี access path หลากหลายเราต้องเข้าใจทั้งหมดและรู้ว่าแบบไหนเหมาะสมกับสถานการณ์แบบไหน และเราจะทำอย่างไร เมื่อ oracle เกิดปัญหาในการเลือก access path ที่เหมาะสมให้กับเรา
2. It is a matter of knowing the question. ก่อนลงมือ tune query ใดเราต้องทำความเข้าใจวัตถุประสงค์ของคนเขียน query นั้นเสียก่อน ข้อนี้สำคัญต่อให้ query ที่เราแก้ไข ทำงานเร็วขึ้นเป็นร้อยเท่าแต่ได้คำตอบแตกต่างออกไปจากที่ต้องการ มันก็ไม่มีประโยชน์อะไร นอกจากนั้นข้อแตกต่างเล็ก ๆ น้อย ๆ ระหว่าง query 2 query จะเกิดขึ้นกับความเป็นไปได้ของข้อมูลบางชุดเท่านั้น เราต้องเข้าใจถึงลักษณะของข้อมูลที่เรามีอยู่ด้วย
3. It is a matter of knowing all of the available bits of functionality. เราต้องเรียนรู้ความสามารถใหม่ ๆ ของฐานข้อมูลอยู่ตลอดเวลา ตัวอย่างเช่น analytic functions ซึ่งอยู่ในฐานข้อมูล oracle ตั้งแต่เวอร์ชั่น 8.1.7 จนบัดนี้ oracle ออก 11g มาแล้ว ผมว่ามีผู้ใช้งาน oracle ไม่ถึง 10% ที่รู้จักและนำมาใช้ประโยชน์ (อันนี้ ผมว่าสาเหตุหลักคือหลักสูตร SQL ที่ oracle จัดไม่ค่อย update เท่าที่ควรคนที่ผ่านการอบรม หรือต่อให้สอบ cert ได้แล้ว ส่วนใหญ่ใช้งานความสามารถของ oracle ได้ยังไม่ถึง 10% เลย)
4. Mastering all aspects of the SQL language. เราต้องสร้างความชำนาญในการใช้ภาษา SQL ยกตัวอย่างคุณ Tom เจ้าของบทความ โจทย์ที่คนทั่วไปต้องเขียน code ยาวเป็นหน้า ๆ แกมองปราดเดียว ตอบคำถามได้ด้วยคำสั่ง SQL แค่ประโยคเดียว แถมนึกได้หลาย solutions แยกตามแต่ละเวอร์ชั่นของ oracle อีกต่างหาก
5. Being prepared to throw out every existing “rule of thumb” you have. เตรียมตัวเตรียมใจที่จะโยนความรู้เก่า ๆ ที่เคยได้ยินมาทิ้งให้หมด อย่างที่บอกการ tuning ไม่มีกฏเกณฑ์ตายตัว เราต้องอาศัยความรู้ที่เอ่ยถึงในข้อก่อน ๆ บวกด้วยจินตนาการความคิด พิสูจน์ทฤษฎีต่าง ๆ ด้วยการทดสอบจริง กฏทุกกฏมักมีข้อยกเว้นเสมอ
6. Thinking about what the machine might have to do in order to process your query. คิดเหมือนที่ฐานข้อมูลคิด ไม่ได้หมายถึงเราต้องทำตัวเป็นเครื่องคอมพิวเตอร์ แต่เราต้องสนใจด้วยว่า ทุก query ที่เราสั่งไป เกิด process อะไรเบื้องหลัง ก่อนที่เราจะได้ผลลัพธ์ออกมา ผมเคยเรียนหลักสูตร RDBMS กับอาจารย์ท่านหนึ่ง แกบอกว่าการใช้ภาษา SQL เราสนใจแค่เราต้องการอะไรก็พอ ส่วนจะได้มาอย่างไรนั้นเป็นเรื่องของ RDBMS ก็คงจริงสำหรับโปรแกรมเมอร์ธรรมดา ๆ ทั่วไปครับ แต่ไม่พอสำหรับจะเป็น tuning specialyst แน่

สุดท้ายเป็นความคิดเห็นเพิ่มเติมจากผมเอง ผมฝากหลักอิทธิบาทสี่ ไว้ด้วย ฉันทะ ใจรักชอบในงาน วิริยะ ขยันหมั่นเพียร ไม่ย่อท้อ จิตตะ ผูกใจจดจ่อ เอาใจใส่ และวิมังสา มุ่งไตร่ตรองหาเหตุผล ทบทวนตัวเองสม่ำเสมอ ถ้าทำได้ทั้งหมดตามนี้ รับรองครับ ใคร ๆ ก็ tuning sql ได้แน่นอน

ด้วยจิตคารวะ