Posted by: siamnobita | 03/09/2010

SQL Tuning Again

เข้าไปอ่าน blog ของเพื่อนบ้านมา เกี่ยวกับเรื่อง Oracle Performance Tuning อ่านแล้วก็ขัดอกขัดใจอย่างมากมาย โดยเฉพาะในส่วนที่อธิบายเกี่ยวกับ sql tuning ( พออ่านไปถึงหนังสืออ้างอิงก็ร้องอ๋อเลย อีตาดอน เบอร์สั้นอีกแล้ว)

สรุปจากเนื้อหาที่ว่ามาใน blog นั้น เขามีขั้นตอนในการ tune sql อยู่ 4 ขั้นตอนย่อย

  • Remove unnecessary large-table full-table scans
  • Cache small-table full-table scans
  • Verify optimal index usage
  • Materialize your aggregations and summaries for static tables

สามข้อแรก แตกมาเป็นสามข้อ แต่ความจริงหนีไม่พ้นเรื่องเดียวกันคือการเลือก full table scan หรือ index scan ส่วนข้อสุดท้ายเป็นการ save queries output เอาไว้ ประมาณว่าถ้า query มันช้า ก็ save มันเก็บไว้ซะเลยคราวหลังจะได้ไม่ต้องรันอีก ง่ายดีจริง ๆ (อาจเป็นการมองแบบอคติมากไปหน่อย แต่ผมหมั่นไส้คุณดอนมานานแล้ว แหะ แหะ)

อันที่จริงแล้ว การ tune sql แบบที่ tune execution plan กันเลยนั้น มีข้อปลีกย่อยอีกเยอะแยะ เขียนกันเป็นตำราได้ทั้งเล่มเลยด้วยซ้ำ การสรุปมาแค่ 4 ข้อนี้นั้น มันน้อย over เกินไป นอกจาก access path แล้วคุณต้องดู join order, join method, query transformation เป็นอย่างน้อย นี่ขัดใจข้อแรก

ขัดใจข้อต่อมา การเปรียบเทียบระหว่าง table scan กับ index scan โดยใช้แค่ขนาดของ table เป็นตัวตัดสินใจ เอ่อ ไม่อยากเชื่อจริง ๆ ว่าพี่แกทำมาหากินผ่านมาได้อย่างไร หรือแกกั๊กความรู้ที่แท้จริงเก็บไว้ใช้เอง ที่เขียนสอนพวกเรา แค่เสี้ยวน้อย ๆ แค่พอสร้างศรัทธาให้เราเชื่อว่าพี่แกต้องแน่จริง ถึงกล้าเขียนเป็นหนังสือออกมาขาย

ผมคงยังไม่มีเวลาพอเถียงแกในเรื่องอื่น ๆ ดังนั้นวันนี้จะจับประเด็นร้อน เรื่อง index นี่ก่อน เพราะเคยสัมภาษณ์คนเข้าทำงานมา น้อยคนที่จะตอบเรื่อง index นี่ได้ถูกใจผม

1. หลักการเลือกว่าควรใช้ index หรือ full scan นั้น อยู่ที่ selectivity ครับ ไม่ใช่ขนาดของข้อมูล โดย selectivity น้อย ๆ ควรใช้ index มาก ๆ ควรใช้ full scan และระหว่างน้อย ๆ กับมาก ๆ นั้น ก็จะมี grey area ที่เราจำเป็นต้องนำปัจจัยอื่นมาใช้ในการตัดสินใจเพิ่มเติม นอกเหนือจากตัว selectivity เอง ช่วง grey area นี้ก็อาจประมาณ 5 – 20 % แต่ก็ยืดหยุ่นได้อีก อาจต้องลองรันให้รู้ดำรู้แดงถึงจะตัดสินใจได้ก็มี

2. การ cache ข้อมูลไว้ใน memory ไม่ค่อยเกิดประโยชน์กับ full table scan ไม่ว่า table นั้นจะเล็กหรือใหญ่ เนื่องจาก block ที่อ่านโดย full scan นั้นจะถูกวางไว้ด้าน LRU least recently used ทันที พร้อมจะถูกถีบกลับลง Hard disk ในเวลาไม่นาน ยกเว้นคุณกำหนดคุณสมบัติของ table นั้นให้ทำการ cache เวลา full scan ด้วย ซึ่งไม่ใช่ค่า default ซึ่งเรื่องของ cache ก็เป็นอีกปัจจัยหนึ่งในการพิจารณาช่วง grey area กล่าวคือดูว่าข้อมูลที่เราสนใจนั้นมัน hot ขนาดไหน ถ้าใคร ๆ ก็สนใจข้อมูลชุดนี้มันก็มีแนวโน้มจะอยู่ใน cach เราก็จะ prefer index scan แม้ว่า selectivity จะค่อนข้างสูงก็ตาม

3.ปัจจัยพิเศษสุดท้ายที่ผมนึกออก คือเรื่องของ clustering factor ครับ อธิบายความหมายได้ว่าคือการจัดเก็บข้อมูลในตาราง เรียงลำดับตามการเรียงตัวของค่าใน indexed คอลัมน์ มากน้อยขนาดไหน หรือแนวโน้มว่าสำหรับค่าใด ๆ ใน index ที่อยู่ติด ๆ กัน ข้อมูลจริงในตารางมันอยู่ติดกันใน block เดียวกันด้วยหรือเปล่า ถ้ามันอยู่ติดกันมันก็ทำให้เราอ่าน table block น้อยลงต่อค่าใน index 1 ค่า ซึ่งทำให้เรา prefer index scan เช่นเดียวกัน แต่ถ้ามันกระจายตัวมาก เราอ่านข้อมูลแค่ 10,000 rows จาก table 1,000,000 rows ซึ่งเก็บอยู่ใน block 10,000 blocks เราอาจต้องเข้าอ่านข้อมูลทั้ง 10,000 blocks (100%) เลยก็ได้ ถ้าแต่ละ row ที่ต้องการมันดันอยู่คนละ block หมดเลยกรณีแบบนี้ full scan ไปเลยก็เร็วกว่าเห็น ๆ

ก็ขอยกตัวอย่างเพียงคร่าว ๆ เผื่อใครที่สนใจจะได้ไปศึกษาข้อมูลเพิ่มเติมได้ (แต่อย่าไปศึกษาจากตำราพี่ดอนก็แล้วกัน อิอิ) เพราะความจริงแล้วหลักการเลือกมันก็ไม่ได้มีเพียง 3 ข้อนี้เหมือนกัน 3 ข้อนี้แค่เป็นประเด็นที่เจอบ่อย ๆ ยังมีกรณีที่ table มัน almost empty หรือ column ที่ต้องการอยู่บน index ทั้งหมด ซึ่งทำให้การใช้ index full scan หรือ index fast full scan ดีกว่า table full scan และ/หรือกรณีอื่น ๆ อีกมากมาย จาระไนไม่หมด

ถ้าจะให้แนะนำหนังสือ ผมแนะนำหนังสือ sql tuning ของ dan tow ดีกว่าครับ เนื้อหาน่าจะถูกต้องกว่าแม้ว่าบางส่วนไม่ได้ update ตาม features ใหม่ของ oracle ก็ตาม

สำหรับวันนี้ สวัสดีครับ


Responses

  1. สาระดีเยี่ยมครับพี่ต้อม
    คอยติดตามอ่านอยู่ครับ


ใส่ความเห็น

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

หมวดหมู่

%d bloggers like this: