AUTO_SAMPLE_SIZE บน Oracle 11g ไฉไลกว่าเดิม!

เกริ่นนำกันก่อนละกันนะครับเกี่ยวกับการ Gather Optimizer Statistics ใน Oracle version เก่าๆ นั้น เราจำเป็นจะต้องคำนวน(เดา) ซึ่งเป็นเรื่องที่ค่อนข้างจุกจิกและยุ่งยากมากๆ เพราะ ถ้าเรากำหนดค่าไว้ที่ 100% บนออบเจ็กต์ขนาดใหญ่นั้นไม่ดีเอาซะเลย ถึงแม้ว่ามันจะการันตีว่าสถิติถูกรวบรวมมา 100% เต็มแต่ด้วยเวลาที่ใช้ไปนั้นเรียกได้ว่า “เต่าคลาน” เลยทีเดียว จนได้ถือกำเนิด AUTO_SAMPLE_SIZE ขึ้นเป็นครั้งแรกบน Oracle 9i ทำให้เราไม่จำเป็นต้องมาคอยกำหนด ESTIMATE_PERCENT อีกต่อไป แต่ข้อจำกัดของมันก็ยังมีอยู่ ถ้ามีการเบ้ (Skew) ของข้อมูลมากๆ สุดท้ายแล้วการกำหนด ESTIMATE_PERCENT ก็ยังคงมีประสิทธิภาพดีกว่าถ้ามีการเบ้ของข้อมูลมากๆ

จนมาถึง Oracle 11g ได้มีการนำอัลกอริทึ่มใหม่สำหรับการ Sampling ข้อมูลมาใช้ ซึ่งมีผลทั้ง 2 ทางคือ ความแม่นยำและความเร็วในการ Gather Statistics (ความแม่นยำใกล้เคียง 100% แต่ใช้เวลาราวๆ 10%) โดยที่อัลกอริทึ่มตัวใหม่นี้จะถูกนำมาใช้ก็ต่อเมื่อ ESTIMATE_PERCENT ถูกกำหนดเป็น AUTO_SAMPLE_SIZE ในทุกๆ DBMS_STATS.GATHER_*_STATS นั่นเอง

จากตัวอย่างของผลลัพธ์จากการทดสอบโดย TPC-H Benchmark ขนาด 300 GB ง่ายๆ ด้านล่างนี้แสดงให้เห็นว่าผลลัพธ์ของการ Gather ทั้ง 3 แบบ คือ 1% Sample, AUTO_SAMPLE_SIZE และ 100% Sample บนตารางข้อมูลชื่อ Lineitem (ขนาด 230 GB)ได้ผลลัพธ์ดังนี้ครับ

TPC-H300GB

ในการทดสอบได้ผลลัพธ์ว่าการใช้ AUTO_SAMPLE_SIZE เร็วกว่า 100% Sample ถึง 9 เท่าและช้ากว่า 1% Sample เพียง 2.4 เท่า ในขณะที่ประสิทธิภาพของ Statistics นั้นแทบจะใกล้เคียงกับ 100% Sample เลยทีเดียว (ไม่แตกต่างกันมากถึงขนาดที่ Execution plan เปลี่ยน)

Oracle แนะนำอย่างหนักแน่นเลยครับว่าบน Oracle 11g ควรปล่อยให้ ESTIMATE_PERCENT ใช้ค่า Default ของมันไป (AUTO_SAMPLE_SIZE) ถ้าคุณกำหนด ESTIMATE_PERCENT ด้วยตัวเอง ไม่ว่าจะกำหนดที่เท่าไร Oracle จะเลือกใช้อัลกอริทึ่มเดิมครับ ไม่ใช้ของใหม่

สรุป AUTO_SAMPLE_SIZE และค่า Default

บน Oracle 11g ใช้ไปเลยครับ ผมการันตีว่าดีมากๆ (อิงจากฐานข้อมูลบางตัวที่ดูแลอยู่ครับ) แต่สำหรับฐานข้อมูลที่ยังเป็น Oracle 9i และ 10g นั้น ผมยังใช้การกำหนด ESTIMATE_PERCENT เองอยู่ครับ สุดท้ายนี้เอาค่า Default สำหรับ Oracle แต่ละ version ครับเผื่อทุกท่านจะเลือกใช้และทดลองกันดูครับ

defailt_value

ขอบคุณครับ
ข้อมูลจาก An Oracle White Paper – Best Practices for Gathering Optimizer Statistics

Author: MrNobita Namsutto

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 /  เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ 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 /  เปลี่ยนแปลง )

Connecting to %s