Sunday, May 4, 2014

SQL คำสั่งเดียว...อยู่!

โพสต์ครั้งแรก: 3 มิถุนายน 2011
เขียนเรื่องของ LWM มาหลายตอนใกล้จะถึงตอนจบ พอดีผมไปเจอบทความใน Oracle Magazine เห็นว่าน่าสนใจเลยแปลมาให้อ่านกันเล่นแก้เบื่อ โดยในบทความนี้ Tom Kytes กล่าวว่า ถ้าเราสามารถทำงานที่เราต้องการโดยสามารถใช้คำสั่ง SQL ธรรมดาคำสั่งเดียวนั้น ย่อมจะดีกว่าการเขียนเป็น Procedural (เขียน SQL หลาย ๆ คำสั่งอยู่ใน BEGIN.. END) ยาว ๆ ซึ่งเนื่องมาจากเบื้องหลังคำสั่ง SQL ของเรามี Optimizer เป็นตัวช่วยจัดการเรื่องประสิทธิภาพของการทำงานอยู่แล้ว ดังนั้นแม้ว่าเราจะพยายามเขียน Stored Procedure ด้วย Logic ใด ๆ ผลลัพธ์ที่ได้มีแนวโน้มว่าจะมีประสิทธิภาพด้อยการการทำงานให้จบภายในคำสั่งเดียว ลองมาดูบทความที่ลงใน Oracle Magazine ฉบับเดือนพค./มิย. 2011 ซึ่งนอกจาก Tom Kytes จะพูดถึงแนวคิดข้างต้นแล้ว เขายังกล่าวถึงประโยชน์ของการใช้วิธีการจัดการกับข้อมูลขนาดใหญ่หากต้องเขียนคำสั่งเป็นแบบ Procedural อีกด้วย

Bulking Up- Thomas Kytes, Oracle Magazine May. June 2011
ผมกล่าวอยู่เสมอ ๆ ว่า ถ้าคุณสามารถใช้คำสั่ง SQL แบบง่าย ๆ เพียงคำสั่งเดียวก็ใช้เถอะครับ อย่าทำอะไรให้มันยุ่งยากโดยเขียนคำสั่ง Procedural (PL/SQL) ผมได้แสดงตัวอย่างคำถามจากผู้อ่านข้างล่างซึ่งเป็นคำสั่ง FOR LOOP UPDATE ที่ใช้เวลานาน และใช้ทรัพยากรของระบบอย่างมาก ในตัวอย่างนี้ผมนำ Code ต้นฉบับมาย่อเพื่อให้ดูง่ายขึ้น โดยจะเอาคำสั่งที่อยู่ใน FOR LOOP ออกไป [หมายเหตุ: ผมได้แก้ไข Code ชุดนี้เพื่อความกระชับ กรุณาดู Code ฉบับเต็มจาก bit.ly/gDONzd]
FOR increc IN
(SELECT *
FROM big_table
ORDER BY many columns)
LOOP
... much procedural code goes here...
UPDATE big_table
SET ...
WHERE primary_key = increc.primary_key;
COMMIT;
END LOOP;
คำถามคือ ตาราง bit_table มีข้อมูลอยู่ 27 ล้านแถว คุณช่วยให้คำแนะนำว่าจะทำไงดีให้ Code ข้างต้นทำงานได้เร็ว?
ผมสามารถบอกได้ว่าคุณอาจจะเริ่มจากการ: ดึงข้อมูลมาจัดการทีละเยอะ ๆ (Bulking) โดยการใช้ BULK COLLECT ซึ่ง Code ที่ได้มีลักษณะดัง Listing 2
Code Listing 2: Step 1 to improving slow-by-slow processing—bulking up
declare
cursor c is
select rowid rid, t.*
from big_table t
order by many columns;
l_limit number := 1000;
begin
loop
-- l_arrayN is a plsql index by table corresponding
-- to your columnN - declare of the type of columnN
fetch c bulk collect
into L_ROWID_ARRAY,
l_array1, l_array2,
l_array3 ...
LIMIT l_limit;
for i in 1 .. l_rowid_array.count
loop
-- the current body of your loop goes here, but
-- instead of an UPDATE, you would assign the
-- values to more arrays (the set columns of your
-- current update), eg:
vrep_array(i) := vrep;
vpaid_array(i) := vpaid;
... and so on ...
end loop;
forall i in 1 .. l_rowid_array.count
update big_table
set reported = v_rep_array(i),
paid = v_paid_array,
...
where rowid = l_rowid_array(i);
exit when c%notfound;
end loop;

Code นี้ดึงข้อมูล (Fetch) มาทีละ 1,000 แถว จัดการบางอย่างกับข้อมูลที่ดึงมา และอัพเดททั้ง 1,000 แถวกลับเข้าไปในตาราง (โดยใช้ ROWID - ไม่ได้ผ่าน Index)
นั่นเป็นขั้นตอนแรก ซึ่งมีบางสิ่งบางอย่างที่น่ากล่าวถึงคือ
- การ COMMIT ทุก ๆแถว เมื่อแถวใด ๆ ถูกอัพเดท ซึ่งจะทำให้ Code ของคุณไม่สามารถที่จะทำการเริ่มต้นใหม่ได้หากมีข้อผิดพลาดเกิดขึ้น เพราะข้อมูลถูกบันทึก (Commit) ไปแล้ว ถ้าระหว่างที่คุณรันแล้วพบ ORA-1555 หรือ Error อื่น ๆ ข้อมูลของคุณก็จะอยู่ในสภาวะที่ไม่เสถียร (Inconsistent State) ไอ้อย่างนี้เรียกว่าเป็น Logical Corrupt ยังไม่รวมถึงความจริงที่ว่าการ Commit ทีละคำสั่งนั้นมัน "ช้า"
- การอัพเดทใช้ ROWID แทนที่จะใช้ Primary Key ใน Code ต้นฉบับนั้น Oracle จะต้องทำ Index Unique Scan กับ Primary Key ถึง 27,000,000 ครั้ง นั่นหมายถึงการอย่างน้อย 3-5 ครั้งที่วิ่งไปหาดิสก์ในแต่ละครั้งของการทำ Index Unique Scan ดังนั้นการใช้ ROWID อาจจะประหยัดการเข้าถึงดิสก์ได้ถึง 100,000,000 ครั้ง!
- Code นี้ดึงข้อมูลและอัพเดท 1,000 แถวในแต่ละครั้ง ซึ่งจะลดการเข้าถึง Server ได้ถึง 999 ครั้ง (แม้ว่าจะเป็น PL/SQL ก็ยัง "Round-Trips" ไปยัง Server แม้ว่าจะไม่ได้วิ่งผ่าน Network แต่ก็ไปเพิ่มการใช้ CPU และทรัพยากรอื่นของระบบ
เพื่อที่จะแสดงให้เห็นว่าผลที่ได้เปลี่ยนแปลงไปอย่างไร ผมจะนำเสนอวิธีการเล็ก ๆ ที่ผมใช้ใน "Efficient PL/SQL" ซึ่งนอกจากเรื่องอื่น ๆ แล้วผมได้พูดถึงเหตุผลที่เราควรจะ Bulk Up หรือไม่ควร Bulk Up
โดยสรุปแล้ว ถ้าคุณอยู่ใน PL/SQL กำลังดึงข้อมูลจากฐานข้อมูล แต่ไม่ได้ต้องการอัพเดทกลับเข้าไป ก็ "อย่า" ใช้ Bulk-Collect ลองดูตัวอย่างชัด ๆ ต่อไปนี้
for x in (select * from t where ...)
loop
dbms_output.put_line(... t.x ...);
end loop;
ในตัวอย่างนี้ เราดึงข้อมูลจากฐานข้อมูลโดยผ่านคำสั่ง SELECT แต่ไม่ได้อัพเดทกลับไปยังฐานข้อมูล จริง ๆ แล้วคำสั่ง SELECT มีลักษณะเป็น BULK อยู่แล้ว แม้ว่าจะไม่มีอะไรบ่งบอกก็ตาม คือตั้งแต่ Oracle Database 10g การใช้ "for x in (select ...)" จะเกิดการใช้ Array-Fetch ทีละ 100 แถวเองเพื่อให้ประสิทธิภาพดีขึ้น ดัง Code ข้างล่างนี้
for x in (select * from t where ...)
loop
process (x);
update t set ... where ...;
end loop;
ซึ่งทำ Array Fetch ในคำสั่ง SELECT แต่สำหรับคำสั่ง UPDATE ที่ตามมาไม่ได้ใช้ Array Fetch ผมจำเป็นจะต้องแก้ไข Code เล็กน้อยเพื่อที่จะทำให้เกิด Array Fetch กับการอัพเดทข้อมูล ลองดูอีกตัวอย่างใน Listing 3
Code Listing 3: The slow_by_slow stored procedure
create or replace procedure slow_by_slow
as
begin
for x in (select rowid rid, object_name
from t t_slow_by_slow)
loop
x.object_name := substr(x.object_name,2)
||substr(x.object_name,1,1);
update t
set object_name = x.object_name
where rowid = x.rid;
end loop;
end;

ใน Listing 3 มันอ่านทุก ๆ แถวจากตาราง T ทำอะไรบางอย่างกับข้อมูล แล้วจึงอัพเดทข้อมูลกลับเข้าไปยังตารางโดยใช้ ROWID แต่มันทำแบบ "row by row" หรืออาจจะเรียกเพื่อให้เห็นภาพว่า "slow by slow" วิธีการเขียนที่ดีขึ้นได้แสดงไว้ใน Listing 4
Code Listing 4: The preferred bulk stored procedure
create or replace procedure bulk
as
type ridArray is table of rowid;
type onameArray is table
of object_name%type; cursor c is select rowid rid, object_name
from t t_bulk;
l_rids ridArray;
l_onames onameArray;
N number := 100;
begin
open c;
loop
fetch c bulk collect
into l_rids, l_onames limit N;
for i in 1 .. l_rids.count
loop
l_onames(i) := substr(l_onames(i),2)
||substr(l_onames(i),1,1);
end loop;
forall i in 1 .. l_rids.count
update t
set object_name = l_onames(i)
where rowid = l_rids(i);
exit when c%notfound;
end loop;
close c;
end;

ใน Listing 4 ผมตั้งใจทำ Array Fetch ด้วยจำนวนแถวในการ Fetch แต่ละครั้ง ซึ่งค่านี้สามารถแก้ไขได้ ผมใช้ตัวแปร N ซึ่งมีค่าดีฟอลต์เท่ากับ 100 ซึ่งจะทำให้เกิดการดึงข้อมูลมาทีละ 100 แถว ไม่ว่าจะเป็นการดึงหรือการอัพเดทข้อมูล โดยใช้ FORALL กับการ UPDATE ผลที่ได้ทำให้รู้สึกพิศวง โดยผมเปิด SQL_TRACE แล้วรันคำสั่งแบบ Slow by Slow กับการใช้ Bulk เปรียบเทียบกัน ดูรายงาน TKPROF ใน Listing 5
Code Listing 5: TKPROF for slow_by_slow and bulk procedures
Slow-by-slow processing in the slow_by_slow procedure
UPDATE T SET OBJECT_NAME = :B2 WHERE ROWID = :B1
call count cpu elapsed disk query current rows
———————— ————————— —————————— ———————— ————————— ———————— ————————— ————————
Parse 1 0.00 0.00 0 0 0 0
Execute 71824 21.25 22.25 0 71836 73950 71824
Fetch 0 0.00 0.00 0 0 0 0
———————— ————————— —————————— ———————— ————————— ———————— ————————— ————————
total 71825 21.25 22.25 0 71836 73950 71824
Bulk processing in the bulk procedure
UPDATE T SET OBJECT_NAME = :B1 WHERE ROWID = :B2
call count cpu elapsed disk query current rows
———————— ————————— —————————— ———————— ————————— ———————— ————————— ————————
Parse 1 0.00 0.00 0 0 0 0
Execute 719 12.83 13.77 0 71853 74185 71825
Fetch 0 0.00 0.00 0 0 0 0
———————— ————————— —————————— ———————— ————————— ———————— ————————— ————————
total 720 12.83 13.77 0 71853 74185 71825

สังเกตว่า CPU ลดลงจาก 21.25 เหลือ 12.83 -- ซึ่งมีสาเหตุจากการใช้ Array แทนการเข้าวิ่งเข้าไปในฐานข้อมูล 71,824 ครั้ง คำสั่งอัพเดทส่งข้อมูลกลับเพียง 719 ครั้ง นั่นเป็นการทำให้การสวิทช์ไปมาระหว่าง PL/SQL กับ SQL ทำได้อย่างมีประสิทธิภาพขึ้น
ขอแถมเป็นน้ำจิ้มหน่อย ผมสนับสนุนให้ใช้เวลาสักหน่อยในการ Reverse-Engineer สคริปต์ที่เราเขียน โดยลองเขียนความต้องการหรือผลลัพธ์ที่ต้องการ แล้วลองพัฒนาคำสั่ง SQL โดด ๆ ดู ซึ่งถ้าจากตัวอย่างที่เพิ่งกล่าวมา ผมสามารถเขียน Code ให้เป็นคำสั่ง SQL เรียบ ๆ ธรรมดา ๆ ได้ดังคำสั่งข้างล่าง
UPDATE t SET object_name =
SUBSTR(object_name,2) || SUBSTR(object_name,1,1)
ลองดูรายงาน TKPROF อีกทีใน Listing 6 นั่นน่าจะเป็นสิ่งที่คุณควรจะต้องการสำหรับข้อมูลขนาด 27 ล้านแถว ซึ่งเป็นอะไรบางอย่างที่รันได้เร็วกว่า 10-20 เท่าหรือกว่านั้น
Code Listing 6: TKPROF for the single SQL statement
UPDATE T SET OBJECT_NAME = SUBSTR(OBJECT_NAME,2) || SUBSTR(OBJECT_NAME,1,1)
call count cpu elapsed disk query current rows
———————— ————————— —————————— ———————— ————————— ———————— ————————— ————————
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.30 1.44 0 2166 75736 71825
Fetch 0 0.00 0.00 0 0 0 0
———————— ————————— —————————— ———————— ————————— ———————— ————————— ————————
total 2 1.30 1.44 0 2166 75736 71825


 แปลจาก On Deferring and Bulking Upโดย Tom KyteOracle Magazine May/June 2011http://www.oracle.com/technetwork/issue-archive/2011/11-may/o31asktom-354139.html

1 comment:

Mr.San said...

ชอบตัวอย่างสุดท้ายครับ
อะไรที่ทำได้โดย statement เดียว มันย่อมดีที่สุดเลย ...

Post a Comment