Saturday, November 7, 2009

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

ข้อเขียนวันนี้ต่อเนื่องและเป็นตอนจบจากตอนที่แล้วคือ การใช้ Parallel Hint (ตอนที่ 1) โดยในตอนนี้จะเป็นวิธีการตรวจสอบการทำงานของ Parallel Query ที่เรากำลังรันอยู่ โดยแปลจากบทความของ gpike เช่นกัน ขอให้สนุกกับบทความและหวังว่าจะได้ประโยชน์กันนะครับ

การตรวจสอบ Parallel Process
ในขณะที่การใช้ Parallel Process เป็นสิ่งที่มีประโยชน์เมื่อต้องจัดการกับข้อมูลขนาดใหญ่ มันก็อาจจะทำให้คุณงงงวยเมื่อไม่รู้ว่ากำลังเกิดอะไรขึ้นภายในระบบฐานข้อมูล ระหว่างการทำ Parallel Query ซึ่ง Oracle ได้เตรียม V$ Views ไว้หลายตัวที่จะช่วยให้เราสามารถตรวจสอบกระบวนการของ Parallel Query ได้ ซึ่งจะช่วยให้เราสามารถเขียนคำสั่งได้ดีขึ้น และสามารถที่จะ Kill Process ที่กินทรัพยากรของระบบจำนวนมากเกินไป

อะไรคือ V$ Views
คิวรีในตัวอย่างต่อจากนี้ไป จะต้องใช้ V_$ View ซึ่งผู้ที่เป็นเจ้าของคือ SYS (คืออยู่ใน Schema "SYS") ปกติแล้ว V_$ View เราจะมองเห็นเป็น (โดยผ่าน Public Synonym) ซึ่งได้เอา Underscore ออก ตัวอย่างเช่นวิว SYS.V_$PX_SESSION เวลาเราใช้วิวตัวนี้เราจะใช้ผ่าน Synonym ชื่อ V$PX_SESSION วิธีการที่ง่ายที่สุด (แต่ไม่ปลอดภัย) คือการ GRANT SELECT ANY TABLE ให้กับ User ทุกคน

การดู Parallel Query Process เบื้องต้น
โดยปกติเมื่อมีการทำ Parallel Query จะเกิด Session ที่คอยประสานการทำงานของ Parallel Servers โดยในขณะทำคิวรี จะมี Parallel Servers 2 ชุดเกิดขึ้นได้แก่ 1) Producers ซึ่งคอยจัดการเรื่อง Full Table Scan และ 2) Consumers ซึ่งเอาข้อมูลทีได้จาก Producers มาทำ Operation ต่างเช่น Hash, Join เป็นต้น Degree ของการทำ Parallel ที่กำหนดในคำสั่ง Hint เป็นตัวกำหนดว่าจะมี Parallel Server Process มากน้อยแค่ไหนในแต่ละ (Parallel) Server Set ไม่ใช่จำนวนทั้งหมดในแต่ละคิวรี ดังตัวอย่างคิวรีข้างล่างนี้

select /*+ PARALLEL(s 2) */ distinct time_of_day from sales s;

ในขณะที่ทำการคิวรี เราจะรันคำสั่งเพื่อดูการทำงานภายในของ Parallel Query ข้างบน

select qcsid,
sid,
nvl(server_group,0) server_group,
server_set,
degree,
req_degree
from SYS.V_$PX_SESSION
order by qcsid,
nvl(server_group,0),
server_set;
คิวรีข้างบนจะให้ผลดังต่อไปนี้ (ผลที่ได้จะปรากฎ ณ ขณะที่ Parallel คิวรีตัวบนยังคงทำงานอยู่)

QCSID SID SERVER_GROUP SERVER_SET DEGREE REQ_DEGREE
------ ------- ---------- ---------- ---------- --------
390 390 0
390 368 1 1 2 2
390 353 1 1 2 2
390 332 1 2 2 2
390 330 1 2 2 2

ผลที่ได้แสดงให้เห็นว่าจากการทำ Parallel Query โดยมี Degree =2 ทำให้เกิด Process ขึ้นมา 5 Process แถวบนสุดเป็นตัวประสานงานของ Process อื่น ๆ เรียกว่า Query Coordinator (QC) แถว 2 และ 3 แสดง เซ็ทของ Parallel Process (server_set = 1) ซึ่งมีหน้าที่ทำการ Full Table Scan ตาราง SALES ในขณะที่แถวที่ 4 และ 5 (server_set = 2) จะแสดงการทำงานของ Parallel Process ซึ่งทำงานส่วนที่เหลือ (Distinct) ของข้อมูลบนตาราง SALES

สมมติว่าเราเพิ่ม Degree เป็น 4 แต่ในระบบฐานข้อมูลอนุญาตให้ใช้แค่ Degree = 3 เราจะได้ผลจากการคิวรี V_$PX_SESSION ดังนี้
QCSID SID SERVER_GROUP SERVER_SET DEGREE REQ_DEGREE
------ ------- ---------- ---------- ---------- --------
390 390 0
390 351 1 1 3 4
390 342 1 1 3 4
390 330 1 1 3 4
390 332 1 2 3 4
390 353 1 2 3 4
390 339 1 2 3 4

แม้ว่าคำสั่งจะขอ Degree = 4 (REQ_DEGREE) จะเห็นว่ามีแค่ 3 (DEGREE) ซึ่งอธิบายว่าทำไมเราจึงมีแค่ 3 Process ในแต่ละ Server Set

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

select a.qcsid,
a.sid,
a.server_group,
a.server_set,
substr(b.name,1,20) operation,
a.value
from V$PX_SESSTAT a,
V$STATNAME b
where a.statistic# = b.statistic#
and upper(b.name) = 'PHYSICAL READS'
order by a.qcsid,
a.server_group,
a.server_set;
คิวรีข้างบนนี้จะให้ผลดังข้างล่างก็ต่อเมื่อเรากำลังรันคิวรีที่เป็น Parallel อยู่ โดยผลที่ได้นี้เกิดจากคิวรีที่มี Degree = 2 และมีการ Join แบบซับซ้อน และมีการ Sort ร่วมกับการทำ Full Table Scan

QCSID SID SERVER_GROUP SERVER_SET OPERATION VALUE
------ ------- ---------- ---------- ---------- --------
332 330 1 1 physical reads 4632
332 351 1 1 physical reads 4697
332 333 1 2 physical reads 4554
332 339 1 2 physical reads 4605
332 332 physical reads 168005

คิวรีนี้แสดงให้เห็นว่าตอนที่กำลังรันคิวรี Parallel Server Set ทั้งคู่ (Producer และ Consumer) จะทำงานโดยแตกออกเป็น 2 Process ตาม DEGREE ที่กำหนด SID 332 เป็น Session ที่ทำหน้าที่เป็นตัวเริ่มต้นและคอยประสานงานของ Session ย่อยอื่น ๆ (Query Coordination Session) ค่าของ Physical Reads (การอ่านค่าจากฮาร์ดดิสก์) จะเพิ่มขึ้นเรื่อย ๆ จนกว่า Session นี้จะ Disconnect ไป

การตรวจสอบการทำงานภายในที่ใช้เวลานาน (Long Operations) โดยใช้ V$SESSION_LONGOPS
V$SESSION_LONGOPS เป็นที่เก็บสถานะของการทำงานซึ่งจะระบบกะว่าใช้เวลาไม่ต่ำกว่า 6 วินาที ตัวอย่างของ Long Operation ได้แก่ Full Table Scan, Hash Join และ Sort Merge ที่อาจจะสร้าง Log การทำงานของตัวมันในระหว่างทำงานไว้ในตารางนี้ (และหายไปเมื่อทำงานเสร็จ) ข้อดีของตารางนี้คือมีคอลัมน์ที่แสดงเวลาที่งานจะเสร็จโดยประมาณเป็นวินาที สำหรับ Parallel Query ที่จะเก็บ Log ในตารางนี้ จะสร้างเรคคอร์ดตามจำนวนของ Parallel Server ที่กำหนดไว้

select sl.sid,
sl.serial#,
substr(sl.opname,1,30),
sl.sofar,
to_char(sl.start_time,'DD-MON-YYYY:HH24:MI:SS') start_time,
sl.elapsed_seconds elapsed,
sl.time_remaining remaining
from V$SESSION_LONGOPS sl,
V$SESSION s
where s.sid = sl.sid
and s.serial# = sl.serial#
and s.username = '&username'
order by sl.start_time desc,
sl.time_remaining asc;

ระหว่างการทำงานของ Parallel คิวรีทีใช้เวลานาน เราอาจจะได้ผลของคิวรีดังนี้

SID SERIAL# OPERATION START_TIME ELAPSED REMAINING
----- ------------- ------------------ -------------------- --------------- -------------------
332 18956 Table Scan 03-MAY-2007:14:26:11 42 18
333 18957 Table Scan 03-MAY-2007:14:26:13 40 20

โปรดสังเกตว่า V$SESSION_LONGOPS ไม่ได้เอาไว้ใช้ในการตรวจสอบ Parallel Query โดยตรง แต่สามารถใช้ได้กับการทำงานใด ๆ ที่ใช้เวลานานเช่นการทำ Full Table Scan

แปลจาก Monitoring Oracle Parallel Queries: PARALLEL Hint Part2 Published in Complex SQL, Hints, SQL, Tuning by gpike วันพฤหัสบดีที่ 3 พฤษภาคม 2007

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

1 comment:

Anonymous said...

สุดยอด !!! ได้อะไร สุดยอดเยอะ >.< ! เลย

Post a Comment