Sunday, June 10, 2012

Isolation Level

ระบบจัดการฐานข้อมูล (Database Management System หรือ DBMS)สมัยใหม่ในปัจจุบันมีคุณสมบัติหลัก ๆ ที่คล้ายคลึงกันมาก โดยเฉพาะระบบฐานข้อมูลที่สร้างขึ้นในเชิงพาณิชย์เพื่อให้สามารถแข่งขันกันได้ ระบบจัดการฐานข้อมูลยี่ห้อต่าง ๆ จึงพยายามพัฒนาคุณสมบัติของตนเองให้เทียบเท่าคู่แข่งเสมอ แต่ในความคล้ายนี้ก็มีบางอย่างที่ยังต่างกัน เรื่องหนึ่งที่ทำให้ระบบจัดการฐานข้อมูล Oracle ต่างจาก Microsoft SQL Server มาก ๆ ก็คือเรื่องของ Transaction Isolation Level

Isolation Level หรือถ้าลองแปลเป็นไทยอาจจะแปลว่า "ระัดับของความเป็นส่วนตัว" ซึ่งหมายถึงความเป็นส่วนตัวของ Transaction นั่นแหละครับ ทำนองว่า Transaction ใด ๆ ควรจะมีความเป็นส่วนตัวของข้อมูลในระดับหนึ่งในการทำงาน ไม่ใช่เรื่องของ Security หรือ Privacy นะครับ แต่เป็นการที่ว่า Transaction จะทำงานร่วมกันอย่างไรโดยยังมีความถูกต้องของข้อมูล ไม่ใช่ว่าคนนึงทำอย่าง อีกคนทำอีกอย่างแล้วข้อมูลตีกันไปตีกันมาจนเล๊ะตุ้มเป๊ะแบบนั้น

Isolation Level เกิดขึ้นมาเพื่อแก้สถานการณ์ซึ่งเป็นปัญหาหลัก ๆ สามประการตามที่ระบุในมาตรฐาน ANSI/ISO ในการทำงานร่วมกันของ Transaction คือ

1) Dirty Read ชื่อก็บอกอยู่แล้วว่าเป็นการอ่านที่สกปรก การอ่านที่สกปรกคือ เมื่อ User คนหนึ่งกำลังอัพเดทข้อมูลอยู่ใน Transaction ใด ๆ แต่ยังไม่ Commit ปรากฎว่าเมื่อ User อีกคนหนึ่งคิวรีข้อมูลชุดเดียวกันกลับได้เห็นข้อมูลที่ User คนแรกอัพเดทแล้ว (แม้จะยังไม่ได้ Commit) ที่บอกว่าสกปรกคือ User คนแรกอาจจะ Rollback ก็ได้ หรือข้อมูลที่เห็นตอนนั้นอาจจะอยู่ในระหว่างการทำงาน ไม่ใช่ค่าสุดท้ายที่ถูกต้อง

2) Non-Repeatable Read สมมติว่าใน Transaction ของคุณคิวรีข้อมูลหนึ่งอยู่ แต่พอเวลาผ่านไปสักพัก คิวรีข้อมูลเดิมอีกที แต่ปรากฎว่าข้อมูลได้เปลี่ยนไปแล้ว เรื่องแบบนี้ไม่น่าแปลก ถ้าเราอ่านข้อมูลจากตารางในกรณีที่ไม่ได้อยู่ใน Transaction เพราะเมื่อเวลาผ่านไปข้อมูลก็มีสิทธิ์ถูกเปลี่ยนได้อยู่แล้ว แต่ใน Transaction หนึ่ง ๆ ข้อมูลที่นำมาใช้งานควรจะถือว่าเอามาจากจุด ณ ตอนเริ่ม Transaction เป็นหลัก

3) Phantoms Read เหมือนจะหมายถึงข้อมูลผี หรือข้อมูลปีศาจอะไรทำนองนั้น คือระหว่างทำงานใน Transaction อยู่ เราคิวรีได้ข้อมูลชุดหนึ่ง ณ เวลา T1 ต่อมาเมื่อเราคิวรีอีก ณ เวลา T2 ข้อมูลชุดที่เราทำงานด้วยอยู่ เกิดมีเรคคอร์ดเพิ่มขึ้นหรือหายไป ต่างจาก Non-Repeatable Read คือ Case นั้นข้อมูลของเราเปลี่ยนไป แต่เคสนี้จำนวนของเรคคอร์ดเปลี่ยนไป

แล้วเรื่องนี้เกี่่ยวอะไรกับ Isolation Level เรามาดูกันต่อไปครั้งหน้านะครับ

อ่านเพิ่มเติม:
1) Isolation (Database Systems), Wikipedia,
http://en.wikipedia.org/wiki/Isolation_(database_systems)
2) Expert Oracle Database Architecture 2nd Edition, Thomas Kyte
3) SQL Server2008 R2, ธาริน สิทธิธรรมชารี


Sunday, May 27, 2012

Rebuild Index: งามอย่างอ้วน

พิมพ์ อีเมล
I
ndex ไม่ควรจะถูก Rebuild ยกเว้นแต่ในบางกรณีเท่านั้น การทำ Rebuild Index ส่วนใหญ่จะเป็นการสูญเสียเวลาเปล่า ๆ บางคนใช้วิธีการดูว่ามีการลบค่าต่าง ๆ ออกจาก Index มากแค่ไหน และทำการ Rebuild Index ซึ่งอาจจะเป็นการเข้าใจผิด คำถามคือถ้า 50% ของค่าใน Index ถูกลบไป ซึ่งทำให้เกิด "รู" ใน Index เราควรจะ Rebuild หรือเปล่า คำตอบคืออาจจะหรืออาจจะไม่ ขึ้นอยู่กับว่า Index นั้นถูกใช้อย่างไร และข้อมูลแบบใดที่ถูก Index ลองดูตัวอย่างข้างล่างนี้:
create table emp ( empno int, ename varchar2(25) );
create index emp_idx1 on emp(empno);
create index emp_idx2 on emp(ename);

คราวนี้ถ้าคอลัมน์ empno ถูกเติมด้วย Sequence (1,2,3,...) มันจะเพิ่มขึ้นเรื่อย ๆ และเราจะไม่เคยใช้ค่าที่ซ้ำกับค่าที่มีอยู่เดิมใน Index เลย เนื่องจาก Sequence จะไม่สร้างเลขที่เคยสร้างไปแล้ว ถ้าสมมติว่า HR ไล่คนออกบางคน emp_idx1 จะเป็น "รู" ซึ่งเป็น "รู"ที่จะไม่เคยถูกใช้อีกเลย และเมื่อเวลาผ่านไปเราพบว่า Index ตัวนี้มี "รู" เพิ่มขึ้นเมื่อมีการลบข้อมูลที่ถูก Index ไว้ กรณีแบบนี้อาจจะเหมาะกับการทำ Rebuild เนื่องจากพื้นที่ที่เป็น “รู” จะได้สามารถนำกลับมาใช้ใหม่ได้ และทำให้ Index มีความ “กระชับ” ขึ้น
แต่ถ้าเป็นกรณีของ Index บน ename สมมติว่าเรามีพนักงานชื่อ:
Bill
Bob
Mary

ถ้าเราไล่ Bob ออก (ลบ Bob ออกจากตาราง) เราจะได้ค่าใน Index เป็น
Bill
X-Bob (ค่าที่ถูกลบ)
Mary

เราควรที่จะ Rebuild Index แบบนี้หรือเปล่า? อาจจะไม่ เหตุผลก็คือเราอาจจะจ้าง Hillary ซึ่งเมื่อเราเพิ่ม Hillary เข้าไป:
Bill
Hillary
Mary

ซึ่ง Hillary สามารถใช้ Slot ของ Bob ได้ จึงไม่มีเหตุผลที่เราจะกำจัดพื้นที่ (Slot) นั้น ถ้าข้อมูลที่เรากำลังทำ Index มีความน่าจะเป็นที่จะไปใช้พื้นที่เดิมนั้น ๆ ในภายหลัง
หลังการ Rebuild อาจจะทำให้ระบบของคุณช้าลง โดยเฉพาะกับระบบ OLTP ทั้งนี้เนื่องจากระบบยังคงต้องมีการเพิ่มหรือขยายโครงสร้างของ Index ซึ่งเหล่านี้ต้องใช้เวลาและทรัพยากรของระบบไม่น้อยในการขยายตัวกลับไปสู่ โครงสร้างเดิม (ที่เสถียรอยู่แล้ว) เมื่อเวลาผ่านไป Index ส่วนใหญ่จะเข้าสู่ Steady State ถ้าเรา Rebuild มัน Index จะกลับเข้าสู่สภาวะที่แม้จะกระชับ แต่ก็ไม่ได้สะท้อนโครงสร้างที่แท้จริง และในที่สุดมันก็จะขยายตัวกลับไปสู่โครงสร้างเดิมที่มีขนาดและลักษณะที่ เหมาะสมกับงานที่มันรองรับ แต่ปัญหาก็คือการขยายตัวกลับไปนั้นจะต้องแลกกับการใช้ทรัพยากรของระบบอย่าง มาก
ดังนั้นก่อน Rebuild ควรเก็บข้อมูลสถิติ, ประสิทธิภาพ, IO ฯลฯ ของ Index ตัวนั้น และเปรียบเทียบค่าเหล่านี้หลังทำ Index Rebuild ถ้าไม่ได้อะไรจากการทำ Index Rebuild ก็ไม่ควรทำ
ข้างล่างนี้เป็นผลจากการ Rebuild Index จากเหตุการณ์จริง

 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> HELP!!!! Riddle me this batman...

Why does an rebuilding an index cause increased redolog generation AFTER the index has
been built?

I have a table 35 million rows and an index (nothing is partitioned) Transactions against
this table are constant. It's always 500,000 rows per day. This generally creates 10
logs a day

Once a month the indexes are rebuit. (Alter index rebuild)
On the day following the indexes rebuild 50 logs are created
On the following days 45...40...35...30....25....down to 10 at 10 logs this remains
constant at 10 Mining the logs we see that we have increase INTERNAL INDEX UPDATES

Why does this happen?? Is this always the case??
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 
จากตัวอย่างการ Rebuild Index ข้างต้นจะเห็นได้ว่า ระบบจะต้องสร้าง Redo เพิ่มขึ้นมากกว่าเดิมถึง 4.5 เท่า ซึ่งส่งผลกับประสิทธิภาพของระบบโดยรวม

Index มีลักษณะคล้าย ๆ มนุษย์ คือมีน้ำหนักที่เหมาะสมกับตัวมันเองตามธรรมชาติ เราบางคนอาจจะผอม บางคนอ้วน บางคนสูง บางคนเตี้ย แม้ว่าเราจะสามารถที่จะพยายามจะเข้าคอร์สลดน้ำหนัก แต่แล้วในที่สุดเราก็มักจะมีแนวโน้มที่จะกลับไปเป็นอย่างที่เราเคยเป็น ซึ่งก็เช่นเดียวกับ Index จะเป็นอย่างไรถ้า Index ตัวหนึ่งที่อ้วน พยายามที่จะผอม (เข้าคอร์สลดน้ำหนัก) ทุก ๆ เดือนเพื่อที่จะอ้วนอีกทีตอนกลางเดือน ในขณะที่ต้องแลกกับการสร้าง Redo อีกจำนวนมากเพียงเพื่อจะกลับมาอ้วนเหมือนเดิม และเมื่อกลับไปที่ขนาดของ Index อ้วนตามที่มันต้องการแล้ว และเมื่อถึงตอนนั้น เราก็จะ Rebuild เพื่อที่จะให้ Index ผอมอีกทีหนึ่ง เพื่อที่จะทรมานให้ระบบจะต้องใช้พลังในการขยายตัวให้ได้ขนาดที่ต้องการอีก เป็นอย่างนี้ไม่รู้จบ

ดังนั้นเราควรยกเลิกการ Rebuild Index ตาม Schedule เราจะ Rebuild Index ก็ต่อเมื่อเราสามารถจะระบุได้ว่าปัญหาเรื่อง Performance ที่พบมาจาก Index จริง ๆ และการ Rebuild จะแก้ปัญหานั้นได้

หมายเหตุ: อาจจะบางกรณีที่การ Rebuild มีประโยชน์อย่างเช่นการ Rebuild Bitmap Index หลังจากการโหลดข้อมูลขนาดใหญ่ แต่ใน OLTP ส่วนใหญ่ความจำเป็นในการ Rebuild Index มีน้อยมาก

เรียบเรียงจาก:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2290062993260
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730

ความลึกลับของ Trigger ตอน3 (จบ)

วิธีการที่ถูกต้อง การแก้ไขโครงสร้างของตาราง จากวิธีการนำไปใช้และการออกแบบตารางจะเห็นได้ว่าเป็นวิธีการที่ผิด ปัญหานี้จำเป็นจะต้องใช้สองตารางดังนี้

SQL> create table primary_currency
2 (country varchar2(2) primary key,
3 currency varchar2(3)
4 )
5 /

Table created.

SQL> create table other_currencies
2 (country varchar2(2),
3 currency varchar2(3),
4 constraint other_currencies_pk
5 primary key (country,currency)
6 )
7 /

Table created.



และหากต้องการความสะดวกในการเรียกดูข้อมูลก็ควรจะเรียกดูผ่านวิวที่ Join เอาสองตารางเข้าด้วยกันแล้วแทน
 

โดยเราแยกตารางออกดังนี้ซึ่งเราได้บังคับใช้กฎไปจากการสร้าง Primary Key แล้วว่าแต่ละประเทศจะมีเงินสกุลหลักได้เพียงหนึ่งเดียวจาก Primary Key ในตาราง primary_currency อย่างไรก็ตามในโลกแห่งความเป็นจริง เราอาจจะต้องการระบุด้วยว่า "ทุกประเทศจะต้องมีอย่างเงินสกุลหลักอย่างน้อยหนึ่งสกุล" ด้วย ซึ่งเราสามารถที่จะกำหนดข้อบังคับนั้นได้โดยเพียงแค่เพิ่ม Contraint เข้าไปในตารางดังนี้


SQL> alter table other_currencies add
2 constraint must_have_at_least_one_primary
3 foreign key (country)
4 references primary_currency(country)
5 /

Table altered.

เสร็จ แล้วครับ คราวนี้เราจะได้ตารางที่มีการบังคับใช้กฎที่เราต้องการ โดยไม่ต้องเขียนโปรแกรม, ดูแลและเข้าใจง่าย Foreign Key ช่วยควบคุมไม่ให้สามารถเพิ่ม Currency สำหรับประเทศที่ยังไม่มี Primary Currency ได้ และที่สำคัญคือ ถูกต้อง

ปิดท้าย
ทริกเกอร์ ควรจะถูกใช้ด้วยความระมัดระวัง ถ้าเป็นการใช้เพื่อควบคุมความสอดคล้องกันของตาราง (Entity Integrity) จะต้องระวังให้มาก คิดให้ถี่ถ้วนว่าถ้ามีคนสอง หรือสามคนเข้ามาทำงานกับกลุ่มข้อมูลเดียวกัน ในเวลาเดียวกัน หรือไล่เลี่ยกัน อาจจะต้องลองทดสอบดูกับการรันในหลาย Session หรือถ้าใช้ทริกเกอร์ในการให้ค่าใด ๆ กับคอลัมน์ก็ต้องระวังกับเรื่องการดูแลในอนาคต เพราะไม่งั้นอาจจะเจอกับปัญหาที่ว่า "เอ๊ะ หนูไม่ยักรู้ว่ามันจะเป็นอย่างนี้" ถ้าพิจารณาเรื่องของการใช้งานพร้อม ๆ กัน (Concurrency) การทำงานที่ไม่ได้อยู่ในระดับ Transaction ของทริกเกอร์ และการดูแลรักษาที่ยุ่งยาก ทริกเกอร์ควรจะเป็นสิ่งสุดท้าย ไม่ใช่สิ่งแรกที่คุณจะทำ แต่ใช้ทริกเกอร์เมื่อคุณไม่สามารถจะหาวิธีการอื่นได้แล้ว

แล้ว ถ้ามีกฎอีกล่ะว่า "แต่ละประเทศจะมีอย่างมากที่สุดและอย่างน้อยที่สุด 1 สกุลเงินหลัก และสกุลเงินหลักจะต้องไม่เป็นเงินสกุลรอง (Other Currencies)" อันนี้จะซับซ้อนหน่อย ซึ่งมีคุณสมบัติเหมือน "Antiforeign Key" ซึ่งคุณสมบัติดังกล่าวไม่มีในระบบฐานข้อมูล เราสามารถจะใช้วิธีการอื่นในการบังคับใช้กฎนี้ได้ โดยทำให้เราสามารถแน่ใจได้ว่าถ้าเรา Join ตาราง PRIMARY_CURRENCY กับ OTHER_CURRENCIES โดยใช้คอลัมน์ COUNTRY and CURRENCY, เราจะได้ 0 เรคคอร์ดเสมอ ลองดู Listing ข้างล่างจะสร้าง Materialized View ซึ่งจะทำการ Join ตารางทั้งสองด้วยคอลัมน์ COUNTRY and CURRENCY โดยที่จะมีผลเป็น 0 เรคคอร์ดเสมอ

SQL> create materialized view log
2 on primary_currency with rowid
3 /
Materialized view log created.

SQL> create materialized view log
2 on other_currencies with rowid
3 /
Materialized view log created.

SQL> create materialized view primary_is_not_other
2 refresh fast
3 on commit
4 as
5 select a.rowid arid, b.rowid brid
6 from primary_currency a, other_currencies b
7 where a.country = b.country
8 and a.currency = b.currency
9 /
Materialized view created.

SQL> alter table primary_is_not_other
2 add constraint primary_curr_cannot_be_other
3 check (arid is null and brid is null)
4 /

Table altered.

ตอน นี้เราได้ Materialized View ที่จะทำการ Refresh เมื่อมีการ Commit คำสั่ง DML ที่ใช้กับตารางแม่ (primary_currency และ other_currencies) ซึ่งจะแสดง Error ถ้าผลการ Join ของสองตารางเป็น Null และรับประกันว่าผลจากการ Join จะได้เป็น 0 rows เสมอ ซึ่งจะไม่มีปัญหาในเรื่องการทำธุรกรรมพร้อม ๆ กัน และผลที่ได้จะถูกต้องเสมอ ซึ่งเป็นการให้ฐานข้อมูลเป็นตัวควบคุมกฎนี้ให้กับเรา

ขอจบบทความเรื่อง "ความลึกลับของทริกเกอร์" โดยบริบูรณ์ และขอให้สนุกกับการใช้ทริกเกอร์อย่างระมัดระวังนะครับ

หลังจากที่หายไปนาน

สวัสดีครับ นานมากแล้วครับที่ผมไม่ได้เขียนบทความเลย ข้ออ้างเก่า ๆ ที่นำมาใช้ด้วยความละอายแก่ใจก็คือไม่มีเวลา โดยเฉพาะทั้งงานประจำ และงานส่วนตัวซึ่งจะหมายถึงครอบครัวเสียมากกว่าสิ่งอื่นใด

ตอนนี้มีความเปลี่ยนแปลงในงานที่ผมทำ ความรับผิดชอบต่าง ๆ เปลี่ยนไป โอกาสที่จะได้เขียนบทความรับใช้สังคมคงจะน้อยลงไปบ้างในช่วงแรก ๆ แต่ผมก็จะยังเขียนต่อไปนะครับตราบใดที่ยังมีผู้สนใจอ่าน โดยหวังว่าจะมีประโยชน์บ้างไม่มากก็น้อย

ระยะหลังนี้ผมได้มีโอกาสจับฐานข้อมูลที่เป็น Microsoft SQL Server มากขึ้น และคิดว่าน่าจะมีโอกาสได้นำประสบการณ์และสิ่งที่พบเจอในฐานข้อมูลตัวนี้มาให้อ่านกัน โดยเฉพาะคุณสมบัติต่าง ๆ เมื่อเทียบกับ Oracle ไม่ว่าจะเป็นเรื่องของการใช้ NULLs, Isolation Level (SQL Server) กับ Read Consistency (Oracle), คำสั่งและเทคนิคต่าง ๆ ซึ่งบางส่วนก็คล้ายกัน และบางส่วนก็ต่างกัน ซึ่งเป็นเรื่องน่าสนุกทีเดียวครับ

ขอขอบคุณผู้อ่านทุกท่านครับ

ธนากร ถาวรศาสนวงศ์
27 พค. 55