การใช้ 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)

การตรวจสอบการทำงานของ Session

Updated: 5/4/2009

ล็อคอินด้วย User ที่มีสิทธิ์ DBA

1. แสดงการล๊อค ณ ขณะนี้ และการร้องขอการล๊อค
SQL> select * from v$lock;

2. แสดง Process ที่ Active อยู่ ณ ขณะนี้
SQL> select * from v$process;

3.แสดงข้อมูลของ Sessions ที่มีอยู่ ณ ปัจจุบัน เราสามารถเชื่อมโยง SID ไปยัง Sessions อื่น ๆ ได้ นอกจากนี้ยังแสดงข้อมูลของการล๊อคเรคคอร์ดด้วย
SQL> select * from v$session;

4.แสดงข้อมูลของการรอ (เพื่อทำงานอย่างใดอย่างหนึ่ง) ของ Session หนึ่ง ๆ
SQL> select * from v$session_event;

5. แสดงทรัพยากร หรือการทำงานใด ๆ ที่ Session ที่ Active กำลังรอคอย WAIT_TIME = 0 หมายถึง งานปัจจุบันที่ Session ทำอยู่
SQL> select * from v$session_wait;
โครงสร้างของ V$SESSION_WAIT ช่วยให้ง่ายในการเช็คว่า ณ ขณะนั้นมี Session ใดรออยู่บ้าง รวมทั้งสาเหตุด้วย ข้อมูลที่ได้ทำให้เราสามารถตรวจสอบได้ว่า การรอแบบนี้เกิดขึ้นบ่อยหรือเปล่า หรือมีความสัมพันธ์กับเหตุการณ์อื่น ๆ หรือเมื่อมีการเรียกไปยัง Module ใด ๆ หรือเปล่า

6. แสดงข้อมูลทางสถิติของ Session ของ User จะต้องไป Join กับ V$STATNAME และ V$SESSION
SQL> select * from v$sesstat;
ตัวอย่าง
SQL> select a.*,b.name,c.username,c.machine from v$sesstat a, v$statname b, v$session c
where a.sid=c.sid and a.statistic#=b.statistic#
and username='TANAKORNT'
order by a.sid ,a.statistic#;

การใช้ TKPROF เพื่อแสดงสถิติการรันของคำสั่ง SQL

Last Updated: 9/4/2009

การรันคำสั่ง SQL ถ้าคุณต้องการรู้ว่าคำสั่งของคุณมีประสิทธิภาพดีมากน้อยแค่ไหน หรือว่าใช้เวลาในการรันมากน้อยเพียงใด จากสาเหตุอะไร คุณสามารถใช้โปรแกรม TKPROF ซึ่งเป็นโปรแกรมที่ถูกติดตั้งมาพร้อมกับตอนที่คุณลง Oracle โดยเริ่มจากคุณสั่งให้ Oracle ทำการบันทึกข้อมูลทางสถิติ (Trace)ของคำสั่ง SQL ที่คุณต้องการเสียก่อน แล้วจึงใช้ TKPROF ในการจัดรูปแบบข้อมูลเหล่านี้ให้อ่านได้เข้าใจง่าย โดย TKPROF จะบอกข้อมูลที่เกี่ยวกับประสิทธิภาพ เช่น แผนการทำงานของคำสั่ง SQL, เวลาของ CPU ที่ใช้ในการประมวลผลเป็นต้น

การใช้ TKPROF ประกอบด้วยขั้นตอนต่อไปนี้
1. ลบ Cache ใน Shared Pool และ DB Buffer
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;

2. ตั้งค่าพารามิเตอร์ TIMED_STATISTICS = TRUE
ดูค่าของพารามิเตอร์
SQL> show parameter timed_statistics;
เปลี่ยนค่าพารามิเตอร์เป็น TRUE
SQL> alter system set timed_statistics = true;

3. สร้าง Plan Table โดยรันสคริปต์ utlxplan, Plan Table เอาไว้เก็บค่าข้อมูลสถิติที่เกิดขึ้นตอนรันคำสั่ง SQL (ตำแหน่งของสคริปต์ utlxplan ขึ้นอยู่กับไดเรคทอรีที่ลง Oracle และระบบปฏิบัติการ)
SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\utlxplan

4. กำหนดให้สามารถ Trace คำสั่ง SQL ได้ การ Trace จะทำทุกคำสั่งที่รันใน Session นี้ จนกว่าจะ set sql_trace กลับเป็น False
SQL> alter session set sql_trace=true;

5. รันคำสั่ง SQL ที่ต้องการดูค่าสถิติ ให้ใส่ remark ไว้ในคำสั่งด้วย เพื่อที่ว่าเวลาเรารันคำสั่งเดียวกันหลาย ๆ ครั้ง เราจะได้สามารถแยกคำสั่งแต่ละคำสั่งได้ง่าย
SQL> SELECT /*sql1*/ * from scott.emp where ename = ‘KING’;

6. ออกจาก SQL เพื่อเข้า OS Prompt และลบไฟล์ tkprof ตัวเก่าที่เรารันเอาไว้ออก เมื่อเรารันคำสั่งเสร็จข้อมูลต่าง ๆ ที่ได้จาก sql trace จะไปเก็บไว้ในไฟล์ trace(.trc) ตัวล่าสุด ซึ่งถ้าเป็น Oracle10g จะอยู่ในไดเรคทอรี่ udump เช่นใน C:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3552.trc แต่ถ้าเป็นเวอร์ชั่น 11g จะอยู่คนละที่กันเช่นที่ C:\app\tanakorn\diag\rdbms\orcl\orcl\trace> เป็นต้น

7. เนื่องจาก Trace File ที่เราได้ใช้ เป็นข้อมูลทางสถิติที่ยังไม่ได้จัดรูปแบบทำให้อ่านยาก จึงต้องใช้โปรแกรม TKPROF นี้เพื่อจัดรูปแบบข้อมูลในไฟล์ให้อ่านได้เข้าใจง่าย
C:\> tkprof C:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3552.trc d:\tkprof.prf explain=system/sys table=system.plan_table sys=no sort=(prscpu,execpu,fchcpu)
TKPROF: Release 11.1.0.6.0 - Production on Thu Apr 9 23:48:50 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

โปรแกรมจะจัดรูปแบบไฟล์ orcl_ora_3552.trc แล้วเก็บไว้ใน tkprof.prf โดยการจัดรูปแบบจะใช้ตาราง Plan Table ใน Schema system

8. เมื่อรันเสร็จ ข้อมูลทางสถิติจะถูกเก็บไว้ในไฟล์ TKPROF.prf

ส่วนเรื่องการอ่านไฟล์ TKPROF.prf สามารถหาอ่านได้จากเอกสารบนอินเตอร์เนทซึ่งมีอยู่จำนวนไม่น้อย