Tuesday, October 20, 2009

การใช้ Parallel Hint (ตอนที่ 1)

Parallel Hint เป็น Hint ตัวหนึ่งที่มีประโยชน์ในการจัดการกับข้อมูลขนาดใหญ่ โดยหลักการคือการแยกงานออกทำพร้อม ๆ กัน แล้วจึงนำผลที่ได้มารวมกันอีกทีหนึ่ง อย่างไรก็ตามการใช้ Parallel Hint นี้จะต้องใช้ด้วยความระมัดระวังเพราะมันอาจจะมีผลกระทบคือไปเบียดบังเอาทรัพยากรของระบบอย่างมากได้ โดยส่วนตัวผมคิดว่า Parallel Hint เหมาะกับงานที่เป็น Batch Process ซึ่งทำนอกเวลางานมีผู้ใช้งานจำนวนน้อยและเราสามารถควบคุมสถานการณ์ได้ ข้อเขียนในวันนี้ผมได้นำเอาบทความเรื่อง Parallel Execution and the /*+ PARALLEL */ hint มาแปลและเรียบเรียงใหม่ โดยใช้ตัวอย่างที่ผู้อ่านสามารถทดลองดูได้จริง ขอให้สนุกกับข้อเขียนนี้ และหวังว่าจะมีประโยชน์ในการนำไปใช้งานนะครับ


Parallel Execution and the /*+ PARALLEL */ hint
คำสั่งชี้นำ(hint) /*+ PARALLEL(ชื่อตาราง ดีกรี) */ คงจะเป็นคำสั่งที่ผมชอบที่สุดอันหนึ่ง เนื่องจากผมเอามันออกมาใช้เกือบจะทุกกรณีไป อย่างไรก็ตาม PARALLEL hint แม้ว่าจะทรงพลัง แต่ก็อาจจะมีผลเสียต่อประสิทธิภาพของระบบได้หากใช้โดยไม่ระมัดระวัง ปกติ hint อื่น ๆ จะให้ผลอย่างมากก็คือทำให้ประสิทธิภาพของคิวรีดีขึ้นหรืออย่างเลวก็ช้าลง แต่ PARALLEL hint อาจจะทำให้ทั้งระบบฐานข้อมูลเกิดปัญหาเลยก็ได้ถ้าใช้โดยไม่ระวัง

ระหว่างการทำ Parallel Execution การปฏิบัติการภายในของระบบฐานข้อมูลจะถูกตัดแบ่งออกเป็นส่วน ๆ เพื่อแจกใหักับระบบซึ่งรองรับ Multi-thread (การทำงานพร้อม ๆ กันหลาย ๆ งานในขณะเดียวกันของคอมพิวเตอร์) การทำ Parallel Execution สามารถใช้ได้ทั้งกับการคิวรีหรือคำสั่งจัดการข้อมูล (DML) เช่น INSERT, UPDATE หรือคำสั่งที่เกี่ยวกับโครงสร้าง (DDL) เช่น CREATE INDEX ฯลฯ เบื้องลึกเบื้องหลังต่าง ๆ สามารถหาอ่านได้จากเอกสารของ Oracle ในที่นี้เราจะพูดถึงแต่ว่า hint ตัวนี้สามารถทำอะไรให้เราได้บ้าง

สมมติว่าฐานข้อมูลของเรา DBA ได้ตั้งค่าใหัสามารถทำ Parallel Query ได้ เราสามารถที่จะเรียกใช้คุณสมบัติ Parallel ได้โดยการใช้คีย์เวิรด์ "DEGREE" ในคำสั่ง CREATE TABLE หรือ ALTER TABLE เพื่อที่จะทำให้คิวรีที่ทำ Full Table Scan (การอ่านข้อมูลทั้งตารางโดยไม่ใช้อินเด็กซ์) กับตารางนี้ใช้คุณสมบัติ Parallel โดยอัตโนมัติ คีย์เวิรด์ DEGREE จะบอกถึงจำนวนของ Process ที่จะให้ระบบฐานข้อมูลแตกงานออกเพื่อทำพร้อม ๆ กัน (ขึ้นอยู่กับจำนวน CPU ที่มีด้วย) แต่โดยมากแล้วไม่ค่อยมีใครใช้คุณสมบัตินี้กับตารางโดยตรงเนื่องจากอาจจะส่งผลกระทบกับการทำงานโดยรวมกับระบบฐานข้อมูล เช่นโดยปกติถ้าตารางนั้นมีอินเด็กซ์ ซึ่งคิวรีที่ใช้สามารถที่จะใช้อินเด็กซ์ได้ซึ่งกินทรัพยากรน้อยการการใช้ Parallel Query แต่ตัว Optimizer (ตัวที่ทำการคิดในระบบฐานข้อมูลเพื่อให้คิวรีได้อย่างมีประสิทธิภาพที่สุด) อาจจะไปเลือกใช้ Full Table Scan แทน ถ้าตารางนั้นได้กำหนดให้สามารถทำ Parallel ไว้

ลองดูตัวอย่างของคิวรีที่ช้า และการใช้ Parallel เพื่อทำให้มันเร็วขึ้น
เริ่มจากการสร้างตารางจากตาราง all_objects โดยเพิ่มจำนวนแถวให้มากกว่า 4 ล้านแถว และสร้างตาราง Look Up ชื่อ obj_type โดยการ select distinct OBJECT_TYPE จากตาราง ALL_OBJECTS อีกที

SQL> create table all_obj as select * from all_objects;

Table created.

SQL> insert into all_obj select * from all_obj;

70908 rows created.

SQL> insert into all_obj select * from all_obj;

141816 rows created.

SQL> insert into all_obj select * from all_obj;

283632 rows created.

SQL> insert into all_obj select * from all_obj;

567264 rows created.

SQL> insert into all_obj select * from all_obj;

1134528 rows created.

SQL> insert into all_obj select * from all_obj;

2269056 rows created.

SQL> insert into all_obj select * from all_obj;

4538112 rows created.

SQL> commit;

Commit complete.

SQL> create table obj_type as select distinct object_name from all_objects;

Table created.

SQL> set autotrace traceonly explain
SQL> select ot.object_type,count(*)
2 from all_obj ao,
3 obj_type ot
4 where ao.object_type = ot.object_type
5 group by ot.object_type
6 order by ot.object_type;

ผลที่ได้จะดูคล้าย ๆ ข้างล่าง


นี่เป็นผลที่เราคาดไว้แล้ว Optimizer จะทำการสแกนทุก ๆ แถวในตาราง ALL_OBJ และ Join กับตาราง OBJ_TYPE ซึ่งก็ไม่มีอะไรผิดปกติ คิวรีจะใช้เพียง Thread เดียวในการทำงาน แต่ถ้าเครื่องของเรามี CPU มากกว่าหนึ่ง และระบบสามารถทำงานด้วย Thread จำนวนมากกว่าหนึ่งได้ เราก็จะสามารถใช้ Parallel Hint ดังตัวอย่างต่อไปนี้

SQL> select /*+ parallel(ao 2) */ ot.object_type,count(*)
2 from all_obj ao,
3 obj_type ot
4 where ao.object_type = ot.object_type
5 group by ot.object_type
6 order by ot.object_type;


เราจะเห็นว่ามีบรรทัดเพิ่มขึ้นมาอีกหลายบรรทัดใน Explain Plan ซึ่งพอจะสรุปได้ว่ามีการแยก Query ออกเป็นส่วนย่อย ๆ แล้วทำงานพร้อม ๆ กัน เสร็จแล้วจึงเอาผลมารวมกันอีกที จากการใส่ Hint นี้เข้าไปเราพบว่าคิวรีของเราใช้เวลาทั้งหมด 00:04:05 จากเดิมที่ไม่ได้ใช้ Hint คิวรีเดียวกันนี้ใช้เวลา 00:07:21 ซึ่งเร็วกว่าเดิมถึง 40%!

อย่างไรก็ตามของดีไม่ได้มาเปล่า ๆ ด้วยการใช้ Parallel Hint นี้เราได้สั่งให้มีการใช้พลังของ Hardware มากกว่าปกติ จากตัวอย่างเราใช้ Degree = 2 ซึ่งเราอาจจะใช้มากกว่านี้เป็น 4, 6, 8 หรือ 16 ก็ได้ขึ้นอยู่กับ Hardware ที่เรามี แต่จากประสบการณ์ที่เคยทำมาบางทีการกำหนดให้ Degree สูง ๆ ก็ไม่ใช่ว่าจะให้ผลที่เร็วที่สุดเสมอไป ขึ้นกับปัจจัยหลาย ๆ ประการบน Hardware ของคุณด้วย ตัวอย่างเช่น CPU อาจจะทำงานได้ดีกับ Parallel Hint แต่อาจจะพบคอขวดที่ I/O ก็ได้เป็นต้น ลองทดสอบกับ Degree ต่าง ๆ ที่เหมาะสมกับระบบของคุณ นอกจากนี้การกำหนด Degree สูง ๆ จะทำให้เกิด Process จำนวนมากในการทำงานคิวรี ซึ่งจะไปเบียดบังเอาพลังของ Hardware จากคนอื่นๆ ที่ใช้เครื่องแม่ข่ายเดียวกันนั้นอยู่ด้วย จึงควรใช้ Parallel Query ด้วยความระมัดระวัง และเท่าที่จำเป็นเมื่อการทำ Full Table Scan เป็นสิ่งที่หลีกเลี่ยงไม่ได้ บางตำราให้ลองเอา 2 * จำนวน CPU ที่มีในการกำหนดจำนวน Degree ไปลองกันดูเอง แล้วพบกันตอนต่อไปนะครับ

เรียบเรียงจาก
Parallel Execution and the /*+ PARALLEL */ hint ตีพิมพ์ใน Complex SQL, SQL Tuning by gpike วันอังคารที่ 27 มีนาคม 2007

บทความที่เกี่ยวเนื่องกัน
1. การใช้ Parallel Hint (ตอนที่ 2 จบ)