Optimizer hints

เมื่อวาน น้องคนหนึ่งทักมาว่า เลิกเขียน 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 ที่มีประโยชน์บางตัวที่ผมเคยใช้อยู่ นะครับ สำหรับวันนี้ สวัสดีครับ