Sunday, December 26, 2010

โอ้! ขวางทาง Lock ตอนที่ 2/2


(ต่อจากตอนที่แล้ว โอ้! ขวางทาง Lock ตอนที่ 1/2)

ประเภทของการ Lock และคอลัมน์ ID1/ID2 ในตาราง V$LOCK
เราสามารถหาว่า Session ใดกำลัง Block Session อื่น ๆ อยู่ได้โดยไม่ยากจากการหาข้อมูลในตาราง V$LOCK โดยที่แรกที่เราจะดูคือคอลัมน์ TYPE ซึ่งแสดงประเภทของการ Lock ที่มีอยู่หลายตัว แต่ประเภทการ Lock ส่วนใหญ่นั้นเป็นการ Lock เพื่อการทำงานของ System เอง โดยปกติแล้ว System Lock จะอยู่เพียงครู่เดียว และการจูน Library Cache, Undo Logs หรืออื่น ๆ โดยใช้ข้อมูลใน V$LOCK ก็ไม่ค่อยจะได้ประโยชน์สักเท่าใด (ดูรายการ Lock แบบ System ได้ใน Oracle Database Reference ในบทที่เกี่ยวกับ V$LOCK)
ส่วนการ Lock ประเภทที่เรียกว่า User Lock นั้นมีเพียง 3 แบบ คืือ TX, TM และ UL, โดย
  • UL(User-Defined) คือ Lock ที่เกิดจากการกำหนดของ Package ชื่อ DBMS_LOCK
  • TX Lock เป็นการที่ Transaction หนึ่ง ๆ ทำการ Lock แถวของข้อมูลในตารางที่จะทำการเปลี่ยนแปลง (Row Transaction Lock), TX Lock จะเกิดขึ้นเพียงครั้งเดียวต่อหนึ่ง Transaction ที่จะทำการเปลี่ยนแปลงข้อมูล โดยไม่สำคัญว่าคุณจะเปลี่ยนแปลง Object (เช่นตาราง) จำนวนกี่ตัว คอลัมน์ ID1 และ ID2 จะชี้ไปที่ Rollback Segment และรายการ Transaction ที่เกิดขึ้นใน Transaction Table
  • TM Lock คือ DML Lock ซึ่งจะเกิดขึ้นตามจำนวนของ Object ที่ถูก Lock เพื่อทำการเปลี่ยนแปลง โดยคอลัมน์ ID1 จะแสดง Object ที่กำลังถูกเปลี่ยนแปลง
โหมดของการ Lock
คุณสามารถดูข้อมูลเพิ่มเติมเกี่ยวกับ TM และ TX Lock ได้โดยการดูที่คอลัมน์ LMODE (Lock Mode) และคอลัมน์ REQUEST ทั้งสองคอลัมน์ใช้รหัสตัวเลขชุดเดียวกันในการบอกสถานะของการ Lock โดยยิ่งตัวเลขมากการ Lock ก็จะยิ่งแน่นหนา (Exclusive - หมายถึงปิดกั้นการเข้าถึงของ Session อื่น ๆ มากขึ้น) เช่น 0 หมายถึงไม่ Lock เลย และ 6 หมายถึง Exclusive Lock
ปกติแล้วแต่ละ Session จะต้องขอ Exclusive TX Lock จึงจะทำการเปลี่ยนแปลงข้อมูลได้ (LMODE = 6) และถ้า Session ไม่สามารถขอ Exclusive Lock ได้เนื่องจากมีบางแถวของข้อมูลที่มันต้องการเปลี่ยนแปลงถูก Lock อยู่ก่อนหน้าแล้วโดย Session อื่น คอลัมน์ LMODE ของ Session นั้นก็จะเป็น 0 โดยมีคอลัมน์ REQUEST เป็น 6 เพื่อจะบอกว่ากำลัง (รอ) ร้องขอ Exclusive Lock อยู่ เราสามารถดูลักษณะการทำงานนี้ได้จากแถวของ V$LOCK ที่ได้แสดงไปในบทก่อนหน้าดังนี้

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ------ -- -------- -------- ----- -------- ------- ------
AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6 685 0
ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0 697 1

สังเกตดูว่าคอลัมน์ ID1 และ ID2 ของ Session 2 ซึ่งกำลังถูก Block และรอขอ TX Lock (LMODE=0, REQUEST=6) ชี้ไปที่ Rollback และ Transaction Entries ของ Session 1 นั่นทำให้เราสามารถหา Session ที่กำลัง Block อยู่ได้
บางครั้งคุณอาจจะเห็น TX Lock ใน Mode 4 (LMODE=4), Shared Mode หมายถึง Data Block ที่เก็บแถวของข้อมูลที่เราต้องการเปลี่ยนแปลงไม่มี Interested Transaction List (ITL) เหลือ เราก็จะเห็น Session ร้องขอ TX Lock ใน Mode 4 ระหว่างที่รอให้ ITL ว่าง ดังนั้นถ้าเราเจอ TX-4 Lock เราอาจจะต้องเพิ่ม INITRANS พารามิเตอร์ให้กับ Object ตัวนั้น

ITL (Interested Transaction List)
เมื่อแถวของข้อมูลในตารางถูก Lock โดย Trasaction ใด ๆ ข้อมูลการ Lock นั้นจะถูกเก็บอยู่ในส่วนหัวของ Block ของข้อมูล (Block Header) ที่แถวของข้อมูลนั้นเก็บอยู่ ข้อมูลส่วนนี้เราเรียกว่า Interested Transaction List (ITL) โดยแบ่งเป็น Slot, ใน ITL เป็นที่เก็บข้อมูลของ Transaction Address และ Rowid, ITL สามารถเก็บข้อมูลการ Lock ได้หลาย Transaction โดยเมื่อ Transaction ร้องขอ Lock มันก็จะใส่ rowid ของแถวที่ต้องการไว้ใน ITL และเมื่อ Transaction อื่น ๆ ต้องการ Lock มันก็จะทำเช่นเดียวกัน เมื่อ Transaction ทำการ Commit หรือ Rollback มันก็จะปล่อย Lock พร้อมทั้งข้อมูลใน Slot ของ Block Header ด้วย เพื่อคืนพื้นที่ให้สามารถนำมาใช้ได้ต่อไป การเซ้ทพารามิเตอร์ INITTRANS และ MAXTRANS ตอนสร้างตารางเป็นตัวกำหนดจำนวน Slot ที่สามารถเปิดได้เมื่อจำนวน Transaction ที่ต้องการเข้าเกินกว่าจำนวน Slot ที่มี Slot ใหม่จะถูกเปิดตราบใดที่ยังไม่เกิน MAXTRANS อย่างไรก็ดีถ้าจำนวน Slot เกินจำนวนที่จะขยายได้แล้ว (เนื่องจากพื้นที่ใน Block ไม่พอแล้ว หรือเกิน MAXTRANS) Transaction จะต้องรอซึ่งจะทำให้เกิดการ Lock ในโหมด 4 ขึ้น

TM Lock โดยปกติจะร้องขอที่ Mode 3 (LMODE=3, Shared-Row Exclusive) และ 6 ซึ่งถ้าเป็นคำสั่ง DDL (เช่นพวกคำสั่ง ALTER เป็นต้น) จะร้องขอ TM Exclusive Lock (CREATE TABLE ไม่ต้องใช้ TM Lock เพราะ Object นั้นไม่เคยมีอยู่) ส่วนคำสั่ง DML จะต้องใช้ Shared-Row Exclusive Lock ดังนั้นจากแถวที่เราได้จาก V$LOCK เราสามารถเห็นว่าเป็น DML Lock

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ----- -- ------ ---- ----- -------- ------ ------
ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0 697 0
ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0 685 0

การระบุ Object ที่ถูก Lock อยู่
เราสามารถจะระบุ Object ที่ถูก Lock ได้จากคอลัมน์ ID1 จากแถวใน V$LOCK ที่เป็น TM Lock โดยนำไป Join กับตาราง DBA_OBJECTS ดังนี้

SQL> select object_name from dba_objects where object_id=88519 ;

OBJECT_NAME
--------------
TSTLOCK

การระบุแถวในตารางที่ถูก Lock
ส่วนใหญ่แล้วการรู้ว่าตารางใดที่ถูก Lock ก็เพียงพอแล้ว แต่หากเราต้องการข้อมูลถึงระดับแถวที่ถูก Lock ก็สามารถทำได้ ดังนี้

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
2* from v$session where sid=479 ;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
88519 16 171309 0
ซึ่งให้ข้อมูลกับเราเกี่ยวกับ Object ID, Relative File Number, Block ใน Data File และแถวที่อยู่ใน Block ซึ่ง Session กำลังรอ (Lock) อยู่ ข้อมูล 4 ตัวนี้เมื่อนำมาประกอบกันก็จะเป็น ROWID แบบขยายนั่นเอง (Extended ROWID) เราสามารถจะสร้าง Extended ROWID จากข้อมูลทั้งสี่นี้โดยใช้ Package ชื่อ DBMS_ROWID ซึ่งมีฟังก์ชัน ROWID_CREATE ที่ใช้ในการแสดง ROWID

SQL> select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
4 from v$session s, dba_objects do
5 where sid=543
6 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
------------ ------------- -------------- --------------- ------------- ------------------
TSTLOCK 88519 16 171309 0 AAAVnHAAQAAAp0tAAA

ซึ่งทำให้เราสามารถนำมาหาแถวของข้อมูลที่เราต้องการในตารางได้

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR
--- ---
1 a

บทสรุป
เราได้เห็นวิธีการที่จะระบุ Session ที่ Block Session อื่น และวิธีการค้นหาเพื่อตรวจสอบแถวของข้อมูลที่มี Session รอทำการ Lock อยู่ และดิฉันหวังว่า เราได้เรียนรู้บางสิ่งบางอย่างบ้างเกี่ยวกับกระบวนการที่เกี่ยวข้องกับ V$LOCK

เกี่ยวกับผู้เขียน
Natalka Roshak เป็น Senior Oracle and Sybase Database Administrator, เป็นนักวิเคราะห์ระบบ และ Architect ทำงานอยู่ใน Kingston, Ontario และทำงานเป็นที่ปรึกษาในอเมริการเหนือ สคริปต์และทิปของเธอสามารถหาดูได้จาก Online DBA Toolkit ที่ http://toolkit.rdbms-insight.com/

แปลจาก What's blocking my lock? โดย Natalka Roshak on Sat, 2006-04-01 18:00 www.orafaq.com/blog/natalka_roshak

Saturday, October 16, 2010

ปัญหา NLS Character Set ของหมวยอินเตอร์

ปัญหา NLS Character Set ของหมวยอินเตอร์
คุณอาดีคะ หนูกุ้มใจจิง ๆ ค่ะ ไม่รู้จะไปปรึกษาใครได้แล้ว คือหนูทำงานเป็นโปรแกรมเมอร์ของบริษัทอินเตอร์ ฯ แห่งหนึ่ง Boss ใหญ่หนูเป็นอเมริกันน่ะค่ะ วันหนึ่ง Boss ก็เรียกหนูไปพบ แล้วเขาก้อสั่งมาว่าต้องการให้ฐานข้อมูลของเราเก็บข้อมูลได้หลาย ๆ ภาษาให้ไปหาวิธีมา หนูไปถามพี่แอ๊ด(มิน)ดูแล้ว พี่แอ๊ดบอกว่าฐานข้อมูลของเรามีคาร์ ๆ อะไรเซ็ท ๆ นี่ล่ะค่ะเห็นบอกว่าเป็น US แล้วเขายังพึมพำแกมบ่นอีกด้วยค่ะว่า ไผสิท๊ามมม..ด้าย หนูจะทำยังไงดีคะคุณอาดี..อิ๊ก..อิ๊ก..


คริสตีน หว่อง (T T)

คุณอาดี(บีเซอร์ทิฟาย): โจทย์ที่หนูคริสตีนได้รับมานั้นเป็นที่พบเห็นได้ไม่น้อยในบริษัทต่างชาติที่มีสาขาอยู่ในต่างประเทศ โดยเฉพาะบริษัทที่ใช้ภาษาอังกฤษเป็นภาษาหลัก ระบบเดิมของหนูคริสตีนใช้ฐานข้อมูล Oracle โดยมี US7ASCII เป็น Character set หลัก (Database Character Set) ที่ใช้กับคอลัมน์ที่มี Data Type เป็น CHAR หรือ VARCHAR2 และมี UTF8 เป็น National Character Set ซึ่งใช้กับคอลัมน์ที่มี Data Type เป็น NCHAR หรือ NVARCHAR2 (Listing 1)
(Listing 1) **************************************************************
SQL> column parameter format a30
SQL> column value format a30
SQ>> select parameter ,value from nls_database_parameters;

PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET UTF8
NLS_RDBMS_VERSION 10.1.0.5.0

20 rows selected.

แต่เดิมไม่ได้มีการใช้ column type ที่เป็น NCHAR (หรือ NVARCHAR2) แต่อย่างใด requirement ใหม่กำหนดให้ฐานข้อมูลสามารถรองรับข้อมูลที่เกี่ยวกับที่อยู่อาศัยที่เป็นภาษาอื่นๆ ที่นอกเหนือจากภาษาปัจจุบันที่ใช้อยู่ เช่นให้รองรับ ภาษาจีน เป็นต้น จริงๆแล้วเดิมระบบของหนูคริสตีนมีการใช้ภาษาอื่น ๆ นอกเหนือจากภาษาอังกฤษในฐานข้อมูลอยู่แล้ว เช่นภาษาในยุโรปบางภาษาเป็นต้น แต่ก็เป็นตัวอักษรแบบ single byte โดยใช้ Database Character Set รวมๆกัน คือ US7ASCII
วิธีแก้ปัญหา:
การเซ็ทอัพให้ฐานข้อมูลรองรับได้ทุกๆภาษาโดยใช้ UNICODE ได้ มีสองวิธี 
วิธีแรก
คือเซ็ทอัพให้ทั้งฐานข้อมูลเป็นแบบ UNICODE วิธีนี้เหมาะกับกรณีที่ข้อมูลเป็นแบบหลายภาษาเฉลี่ยๆ กัน ไม่สามารถบอกได้ว่าภาษาใดมากกว่า และมีภาษาทั้งภาษายุโรป และเอเชียคละกันอยู่ เนื่องจากตัวอักษรที่ encode แบบ UNICODE จะมีขนาดของตัวอักษรใหญ่กว่าแบบ single-byte จึงทำให้เปลืองเนื้อที่บนดิสก์และหน่วยความจำมากกว่า และประสิทธิภาพจะด้อยกว่าฐานข้อมูลแบบ single-byte 
วิธีที่สอง
คือเซ็ทเป็นฐานข้อมูลแบบ single-byte แล้วเซ็ทแค่บางคอลัมน์ให้รองรับได้หลายภาษา วิธีนี้จะเหมาะกับฐานข้อมูลที่มีภาษาที่ใช้ตัวอักษรแบบ single-byte เป็นหลัก (เช่นภาษาอังกฤษ) แล้วมีบางคอลัมน์เป็นภาษานานาชาติ วิธีนี้จะได้ performance ของระบบที่ดีกว่าและประหยัดทรัพยากรระบบมากกว่า
การเซ็ทอัพนี้ทำได้เมื่อตอน create database เท่านั้น หากต้องการเปลี่ยนจะต้อง recreate database ใหม่หรือใช้ CSALTER script ร่วมกับ exp/imp utilities
เนื่องจากระบบเดิมใช้ Database Character Set เป็นแบบ single-byte Character Set (US7ASCII) ซึ่งไม่สามารถรองรับภาษาทางเอเชียได้ และเนื่องจากความต้องการใช้ภาษาเพิ่มเติมเหล่านี้ในเฉพาะบางคอลัมน์เท่านั้น จึงใช้วิธีการกำหนดให้บางคอลัมน์เป็น NCHAR (,NVARCHAR2) เพื่อรองรับภาษาที่เป็น multi-byte (UNICODE) โดยคอลัมน์เหล่านี้จะใช้ National Character Set ที่เป็น UTF8 ตามที่ได้กำหนดไว้เมื่อตอน create database

ผลข้างเคียงที่อาจเกิดขึ้นได้:
การเปลี่ยนคอลัมน์จากเดิมเป็น CHAR (, VARCHAR2) ไปเป็น NCHAR (,NVARCHAR2) จะไม่มีผลกระทบอะไร เนื่องจากเป็นการเปลี่ยนไปเป็น Character Set ที่ใหญ่กว่า (ดู Listing 2)

(Listing 2) **************************************************************
SQL> create table test_char (cname char(10), vname varchar2(10));

Table created.

SQL> insert into test_char values ('SCOTT','SCOTT');

1 row created.

SQL> select dump(cname,1010), dump(vname,1010) from test_char2;

DUMP(CNAME,1010)
----------------------------------------------------------------
DUMP(VNAME,1010)
----------------------------------------------------------------
Typ=96 Len=10 CharacterSet=US7ASCII: 83,67,79,84,84,32,32,32,32,32
Typ=1 Len=5 CharacterSet=US7ASCII: 83,67,79,84,84

--<< สังเกต CharacterSet เป็น US7ASCII

SQL> alter table test_char modify (cname nchar(10), vname nvarchar2(10));

Table altered.

SQL> select dump(cname,1010) , dump(vname,1010) from test_char;

DUMP(CNAME,1010)
----------------------------------------------------------------
DUMP(VNAME,1010)
----------------------------------------------------------------
Typ=96 Len=10 CharacterSet=UTF8: 83,67,79,84,84,32,32,32,32,32
Typ=1 Len=5 CharacterSet=UTF8: 83,67,79,84,84

--<< สังเกต CharacterSet เป็น UTF8 เมื่อแปลงมาเป็น NCHAR (,NVARCHAR2)

*** DUMP เป็นฟังก์ชันของ Oracle ที่จะแสดงรหัสของตัวอักษรออกมาเป็นไบท์

แต่ในทางกลับกันเราไม่สามารถที่จะเปลี่ยน NCHAR (,NVARCHAR2) ไปเป็น CHAR (,VARCHAR2) โดยไม่ลบข้อมูลออกจากคอลัมน์นั้นก่อนได้ ด้วยเหตุผลที่กล่าวมาแล้วคือ NCHAR (,NVARCHAR2) มีขนาดของ Character Set ที่ใหญ่กว่า (ดู Listing 3)

(Listing 3) **************************************************************
SQL> desc test_char
Name Null? Type
----------------------- -------- ----------------------
CNAME NCHAR(10)
VNAME NVARCHAR2(10)

SQL> alter table test_char modify (cname char(10), vname varchar2(10));
alter table test_char modify (cname char(10), vname varchar2(10))
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype{/xtypo_code}
นอกจากนี้เราสามารถเปลี่ยนจาก NCHAR มาเป็น NVARCHAR2 หรือในทางกลับกันจาก NVARCHAR2 มาเป็น NCHAR ได้ อย่างไรก็ดีให้สังเกตดู space ที่ถูก pad เข้าไปเมื่อ data type ถูกเปลี่ยนเป็น NCHAR ด้วย (ดู Listing 4)
(Listing 4) **************************************************************
SQL> alter table test_char modify (cname nvarchar2(10), vname nchar(10));

Table altered.

SQL> select dump(cname,1010), dump(vname,1010) from test_char;

DUMP(CNAME,1010)
-----------------------------------------------------------------
DUMP(VNAME,1010)
-----------------------------------------------------------------
Typ=1 Len=10 CharacterSet=UTF8: 83,67,79,84,84,32,32,32,32,32
Typ=96 Len=10 CharacterSet=UTF8: 83,67,79,84,84,32,32,32,32,32

ผลกระทบที่อาจจะเกิดกับ application: NCHAR (, NVARCHAR2) กำหนดความกว้างของคอลัมน์เป็นจำนวนตัวอักษร ในขณะที่ CHAR (,VARCHAR2) กำหนดเป็น byte ดังนั้นหากเดิม กำหนดเป็น CHAR(10) ซึ่งกินพื้นที่ 10 bytes เมื่อแปลงเป็น NCHAR(10) อาจจะกินพื้นที่ได้ตั้งแต่ 10 - 40 ไบท์ ขึ้นอยู่กับว่าข้อมูลที่เก็บเป็นภาษาอะไรเช่นถ้าเป็นภาษาเอเชียใช้ 3 bytes ต่อตัวอักษรก็จะกินพื้นที่ถึง 30 bytes เป็นต้น

Saturday, August 28, 2010

โอ้! ขวางทาง Lock


ถ้าคุณเคยรับโทรศัพท์จากผู้ใช้ระบบที่อารมณ์บูดอันเนื่องมาจากธุรกรรมที่เขาหรือเธอกำลังทำอยู่บนโปรแกรมนั้นมันดูเหมือนแฮ็งก์ ๆ ช้า ๆ ไม่ไปไหนเลย หรือจากนักพัฒนาระบบที่ไม่เข้าใจว่าทำไม Session ทั้งหลายที่เกิดขึ้นจากการรันโปรแกรมที่เขาหรือเธอเขียนขึ้นนั้น มัน "Block" กันเอง คุณรู้ว่าจะมีประโยชน์มากที่สามารถระบุได้ว่าใคร (หรือ Session ใด) ไป Block ชาวบ้านเขา (หมายถึง Session อื่น) และจะมีประโยชน์ยิ่งขึ้นถ้ารู้ว่า Object ตัวใด (เช่น ตารางใด) กำลังถูก Block ไปจนกระทั่งรู้ว่าแถวใดกำลังถูก Block อยู่
ทดลองสร้าง Session ที่ล๊อคเรคคอร์ดที่ไป Block การล๊อคของเรคคอร์ดของ Session อื่น
{xtypo_info}ผู้แปล -- ปกติเมื่อ Session ต้องการทำการเปลี่ยนแปลงข้อมูล Session นั้นจะต้องทำการ Lock เรคคอร์ดที่ต้องการเปลี่ยนแปลงเสียก่อนโดยการใช้คำสั่ง SELECT ... FOR UPDATE, คำสั่ง DML บางอย่างเช่น Update, Delete จะทำการ Lock เรคคอร์ดที่มันไปกระทำไว้ทันที จนกว่าจะมีการ Commit หรือ Rollback คำสั่งนั้น ในขณะที่ Session กำลังรันคำสั่งเหล่านี้อยู่ (และยังไม่ได้ Commit หรือ Rollback) Session อื่น ๆ ที่รันคำสั่งที่จะทำการ Lock เรคคอร์ดเดียวกันกับเรคคอร์ดที่ถูก Lock อยู่ เราเรียกเหตุการณ์นี้ว่า Session แรกกำลัง Block Session หลังอยู่{/xtypo_info}

เริ่มต้นด้วยการสร้างสถานการณ์ที่ผู้ใช้ระบบคนหนึ่งไป Block ผู้ใช้ระบบอีกคนหนึ่ง ลองเปิด 2 Session แล้วรันคำสั่งต่อไปนี้ใน Session ที่ 1 เพื่อสร้างตารางทดสอบ

SQL> create table tstlock (foo varchar2(1), bar varchar2(1));

Table created.

SQL> insert into tstlock values (1,'a');

1 row created.

SQL> insert into tstlock values (2, 'b');

1 row created.

SQL> select * from tstlock ;

FOO BAR
--- ---
1   a
2   b

2 rows selected.

SQL> commit ;

Commit complete.


ต่อไปก็ทำการล๊อคข้อมูลทั้งตาราง โดยยังอยู่ใน Session 1
SQL> select * from tstlock for update;

และใน Session 2 ลองพยายามอัพเดทแถวดู
SQL> update tstlock set bar='a' where bar='a' ;

คำสั่งข้างบนนี้จะแฮงก์ไปเฉย ๆ โดยถูก Block จาก Session 1 ที่กำลังล๊อคข้อมูลทั้งตารางอยู่

การค้นหา Session ที่กำลัง Block Session อื่นอยู่

Oracle ได้เตรียม View ชื่อ DBA_BLOCKERS ซึ่งจะแสดงรายการ SID ซึ่งกำลัง Block SID อื่นอยู่ แต่ว่าจากประสบการณ์ของตัวผู้เขียน พบว่าจะช้ากว่าการคิวรีจาก V$LOCK และไม่ได้ให้ข้อมูลอื่นใดเพิ่มเติมนอกจาก SID ที่กำลัง Block คนอื่นอยู่ ในขณะที่ V$LOCK คิวรีได้เร็วกว่าทำให้ง่ายในการระบุ Session ที่ Block คนอื่น ๆ อยู่พร้อมกับข้อมูลอื่น ๆ ที่เป็นประโยชน์ในการวิเคราะห์
SQL> select * from v$lock ;

ADDR     KADDR    SID TY ID1    ID2   LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ----- ----- ------- ----- -----
AF9E2C4C AF9E2C60 479 TX 131078 16739     0       6   685     0
ADDF7EC8 ADDF7EE0 422 TM 88519  0         3       0   697     0
ADDF7F74 ADDF7F8C 479 TM 88519  0         3       0   685     0
ADEBEA20 ADEBEB3C 422 TX 131078 16739     6       0   697     1
....     ....     ... .. ...... ..        .       .   ...     .


สังเกตคอลัมน์ BLOCK ถ้า Session ใด ๆ กำลัง Block Session อื่นอยู่ ค่า BLOCK จะเท่ากับ 1, นอกจากนั้นคุณสามารถจะชี้ Session ที่กำลังถูก Block ได้โดยเปรียบเทียบค่าในคอลัมน์ ID1 และ ID2, Session ที่ถูก Block จะมีค่าของ ID1 และ ID2 เหมือนกับ Session ที่ Block มันอยู่ และตัว Session ที่ถูก Block นี้จะมีค่าในคอลัมน์ REQUEST > 0 เสมอ เพื่อแสดงว่ามันกำลังร้องขออยู่ (แต่ยังไม่ได้)
ในคิวรีข้างบนเราจะเห็นว่า SID 422 กำลัง Block SID 479, SID 422 มีลักษณะคล้าย ๆ กับ Session 1 ในตัวอย่างของเรา และ SID 479 ก็คล้ายกับ Session 2
เพื่อให้ดูง่ายขึ้นเราอาจจะเขียนคิวรีแบบนี้
SQL> select l1.sid, ' IS BLOCKING ', l2.sid
2 from v$lock l1, v$lock l2
3 where l1.block =1 and l2.request > 0
4 and l1.id1=l2.id1
5 and l1.id2=l2.id2;

SID 'ISBLOCKING' SID
--- ------------ ---
422 IS BLOCKING  479

1 row selected.


และเราอาจจะเพิ่มข้อมูลบางอย่างเข้าไป โดยการ Join กับ V$SESSION เพื่อให้ดูง่ายขึ้น
SQL> select s1.username || '@' || s1.machine
2 || ' ( SID=' || s1.sid || ' ) is blocking '
3 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;

BLOCKING_STATUS
--------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 ) is blocking BULKLOAD@yttrium ( SID=479 )

1 row selected.


ยังคงมีข้อมูลในวิว V$LOCK อีกมาก ก่อนที่จะสามารถอ่านข้อมูลเหล่านี้ เราจำเป็นจะต้องเข้าใจเพิ่มเติมเกี่ยวกับประเภทของการล๊อค และความหมายของคอลัมน์ ID1 และ ID2 เสียก่อน ขอบคุณที่ให้ความสนใจ โปรดติดตามตอนต่อไปนะคร๊าบ

แปลจาก What's blocking my lock? โดย Natalka Roshak on Sat, 2006-04-01 18:00 www.orafaq.com/blog/natalka_roshak

Thursday, August 5, 2010

Recreate Constraint กับ DBMS_METADATA


สมถวิวได้รับมอบหมายให้ดูแลระบบฐานข้อมูล Oracle มาได้สองเดือน โดยที่ทีมพัฒนาระบบได้ขอร้องให้เธอช่วยเป็น DBA จำเป็นให้กับทีมด้วย ด้วยไฟที่ร้อนแรงเธอแนะนำให้ทีมพัฒนาระบบสร้าง Foreign Key กับทุก ๆ ตารางที่มีการอ้างอิงไปยังตาราง Master เพื่อที่จะได้ไม่มีข้อมูลที่เป็น "กำพร้า" (Orphan) ในตารางลูก เช่นในระบบเก่ามีรายการชำระเงินบางรายการในตารางธุรกรรม ที่ไม่พบสัญญาในตารางที่เป็น Master ทำให้ผู้จัดการข้อมูลต้องเกาหัวแกร็ก ๆ โชคดีว่าข้อมูลการชำระเงินเหล่านี้เกิดขึ้นนานแล้ว และปัจจุบันไม่มีผลแล้ว

สมถวิวในนามของ DBA จำเป็นได้บรรจงสร้าง Constraint ต่าง ๆ ไม่ว่า Primary Key, Foreign Key ลงในตารางที่ทีมพัฒนากำลังเขียนโปรแกรมกันอยู่ เพื่อควบคุมการอ้างอิงกันของข้อมูลให้ถูกต้องที่สุด และป้องกันการปวดหัวในอนาคต วันหนึ่งสมโคดซึ่งเป็น Developer ในทีมได้บ่นกับสมถวิวว่า ทำไมต้องมี Constraint เยอะแยะ ทดสอบโปรแกรมลำบากมาก จะทำข้อมูลทดสอบก็ยาก จะลบก็ยาก ต้องไปตามลบกันเป็นทอด ๆ และแนะนำสมถวิวว่า "เอาออกเหอะ ตัวเอง เด๋วทีมดีเวลล็อป ควบคุมจากโปแก็มเอง" สมโคดกะจะให้โปรแกรมเป็นตัวเช็ค โดยคิดว่าการให้ User เลือกจากดร็อปดาวน์ลิสต์น่าจะทำให้ข้อมูลที่เข้ามาจะต้องมีในตารางมาสเตอร์เท่านั้น

สมถวิวเองก็เคยเจอปัญหานี้เหมือนกันคือไอ้เจ้า Foreign Key นี้มันช่างทำให้การทำงานเก้งก้างจัง จะลบก็ยาก จะเพิ่มก็ยาก แต่จะให้ลบ หรือไม่สร้างเลยละก็ เมินเสียเถอะ! เธอไม่อยากต้องมาปวดหัวกับข้อมูลประหลาด ๆ ในอนาคต เอ...แต่จะแก้ปัญหาอย่างไรดี เธอรู้ว่าการ Disable Constraint ก็ไม่สามารถจะแก้ปัญหานี้ได้ในบางกรณี แต่จะทำยังไงดีเธอเตรียมกระดาษกับปากกาเอาไว้แล้ว เธอจะจดรายละเอียด Constraint ให้ละเอียดเลย พร้อมทั้งคอลัมน์ต่าง ๆ ที่อยู่ใน Constraint ฐานข้อมูลตัวนี้มีแค่ 200 ตารางเอง แล้วก็มีตารางที่ต้องอ้างอิงตารางอื่นประมาณแค่ 150 ตาราง เธอรู้สึกปวดมวนในท้องขึ้นมาทันที แล้วถ้าเธอสร้างผิดแล้วไม่เหมือนเดิมล่ะ...เอถ้าเราสามารถจะลบ Constraint ออก และสร้างใหม่ได้แบบง่าย ๆ ก็ดีสินะ

สมถวิวคิดถูกแล้วที่จะควบคุมมาตรฐานหรือ Intergrity ของฐานข้อมูลซึ่งเป็นหน้าที่หนึ่งของ DBA แต่ปัญหาของการนี้คือเราจะต้องเก็บเอา Foreign Key Constraint เก่าเอาไว้ก่อนลบ แล้วเอามาสร้างใหม่เมื่อต้องการกลับมาสร้างกลับเพื่อควบคุม Constraint อีกครั้ง โชคดีที่ Oracle ได้เตรียม Utility สำหรับการสร้าง DDL (Data Discription Language - คำสั่งที่ใช้ในการสร้างวัตถุต่าง ๆ ที่เกี่ยวกับข้อมูล เช่นตาราง, อินเด็กซ์เป็นต้น) เอาไว้ให้แล้ว ซึ่งน่าจะช่วยสมถวิวในการ Drop และสร้าง Constraint ใหม่ได้มาก ลองดูคำสั่งข้างล่างนี้

SQL> select
2 to_char(DBMS_METADATA.get_ddl('REF_CONSTRAINT',CONSTRAINT_NAME))
3 from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R';

TO_CHAR(DBMS_METADATA.GET_DDL('REF_CONSTRAINT',CONSTRAINT_NAME))
-------------------------------------------------------------------------

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE


คำสั่งข้างบนนี้สามารถรันได้จาก Schema ที่เก็บตารางต่าง ๆ ได้ ไม่ต้องมีสิทธิ์เป็น DBA, สังเกตตรง DBMS_METADATA ซึ่งเป็น Package ชื่อก็บอกอยู่แล้วว่าเกี่ยวกับ Metadata หรือเป็นอะไรที่เกี่ยวกับข้อมูลของ Data อีกที ตัวฟังก์ชันที่เรานำมาให้ใช้คือ "GET_DDL" ซึ่งจะแสดง DDL หรือคำสั่งที่ใช้ในการสร้างวัตถุต่าง ๆที่เกี่ยวกับข้อมูลดังได้กล่าวมาแล้วตัวอย่างคำสั่งที่สามารถสร้างขึ้นมาได้จากฟังก์ชันนี้เช่น คำสั่งในการสร้างตาราง, คำสั่งในการสร้างอินเด็กซ์ หรือจากตัวอย่างนี้คือคำสั่งในการสร้าง Constraint เป็นต้น สังเกตว่าเราใช้คำสั่งนี้ร่วมกับ Data Dictionary View เช่นในกรณีนี้คือ USER_CONSTRAINTS ถ้าเราใช้คำสั่ง
{xtypo_code}SQL> select CONSTRAINT_NAME from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R';

เราจะได้ชื่อ Constraint ทั้งหมดที่เป็น Foreign Key เราเพียงแต่ใส่ฟังก์ชัน GET_DLL (ซึ่งเป็นส่วนหนึ่งของ Package ชื่อ DBMS_METADATA) เข้าไปเราก็จะได้ลิสต์ของคำสั่งในการสร้าง Constraint ที่เป็น Foreign Key ทั้งหมดใน Schema ปัจจุบัน

SQL> select to_char(DBMS_METADATA.get_ddl('REF_CONSTRAINT',CONSTRAINT_NAME))
2 from user_constraints where constraint_type = 'R';

***ตรงฟังก์ชัน to_char เราสามารถเอาออกได้ ที่ใส่ไว้เนื่องจากข้อมูลที่ได้มีประเภทข้อมูลเป็น CLOB บาง Interface อาจจะไม่สามารถแสดงผลได้

ตรงที่เป็น 'REF_CONSTRAINT' เป็นค่าคงที่ที่เราต้องรู้ว่าจะใส่คำว่าอะไรในการคิวรีเช่นถ้าต้องการ Index ก็อาจใช้คำสั่งร่วมกับ Dictionary View ชื่อ USER_INDEXES เช่น
SQL> select to_char(DBMS_METADATA.get_ddl('INDEX',INDEX_NAME))
2 from USER_INDEXES;

TO_CHAR(DBMS_METADATA.GET_DDL(
-------------------------------------------------------------------------

CREATE INDEX "SCOTT"."IND_MVDEPT_DNAME" ON "SCOTT"."MV_DEPT" ("DNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"


CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"


CREATE UNIQUE INDEX "SCOTT"."PK_DEPT1" ON "SCOTT"."MV_DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
...
...
ดูค่าคงที่สำหรับ Object Type ได้ที่ http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA

เมื่อเราได้คำสั่งมาแล้วเราก็สามารถที่จะบันทึกเก็บไว้ หลังจากที่เรา Drop เอา Constraint (หรือ Index) ออกแล้วเราก็สามารถนำคำสั่งเหล่านี้กลับมารันเพื่อให้วัตถุที่เป็น Constraint หรือ Index ต่าง ๆ กลับมาดังเดิม เช่นในกรณีของสมถวิว เธออาจจะรันคำสั่งนี้ขึ้นมาเก็บไว้ก่อน แล้วจึง Drop Constraint เมื่อทำงานต่าง ๆ ที่มี Constraint เป็นอุปสรรคเสร็จแล้ว ก็สามารถสร้าง Constraint ขึ้นมาใหม่โดยใช้คำสั่งที่เก็บไว้ข้างต้น สมถวิวก็จะสามารถคงความเป็น DBA ที่ดีไว้ โดยที่ยังสามารถให้ความสะดวกกับเพื่อนร่วมงานของเธอได้
เวอร์ชั่น: Oracle9i,10g

Thursday, July 15, 2010

Query Plan เปี๊ยนไป๋!!?

Query Plan เปี๊ยนไป๋!!?
Oracle Version: 10g
เมื่อเราทำการ Gather Statistics กับตารางที่ข้อมูลไม่ได้มีการปลี่ยนแปลงสามารถทำให้ Query Plan ที่เกิดกับคิวรีบนตารางเหล่านั้นเปลี่ยนไปได้ เช่นหากวันหนึ่งเราทำการ Gather Statistics รันคิวรี (และบันทึก Query Plan) เอาไว้ วันต่อมาเรา Gather Statistics อีก (และไม่ได้แก้ไขข้อมูลเดิมแต่อย่างใด) เมื่อเราคิวรีข้อมูลในตารางเหล่านั้นเราอาจจะพบว่า Query Plan ที่ได้ เปลี่ยนไป ซึ่ง Query Plan ที่ได้ ดูเหมือนวาจะดีกว่าตัวก่อน สาเหตุเป็นเพราะอะไร?

ความเปลี่ยนแปลงข้างต้นทางหนึ่งอาจจะมาจากค่าดีฟอลต์ของพารามิเตอร์ในการ Gather Statistics ที่ชื่อ METHOD_OPT ใน DBMS_STATS โดยใน Oracle10g พารามิเตอร์ตัวนี้มีค่าดีฟอลต์เป็น SIZE AUTO หลังจากที่เรารันคิวรี Oracle จะจำรูปแบบการคิวรีรวมถึงคอลัมน์ต่าง ๆ ไว้ในตาราง Dictionary ชื่อ SYS.COL_USAGE$ ซึ่งครั้งต่อไปที่เรารัน DBMS_STATS เพื่อรวมรวมสถิติของตารางเดิมเหล่านี้ DBMS_STATS จะคิวรีตารางนี้ก่อนเพื่อดูว่ามีคอลัมน์ไหนบ้างที่จะควรจะเก็บข้อมูล Histogram ด้วยโดยดูจาก Workload ของคิวรีตัวก่อนหน้า

ลองดูกับตัวอย่างง่าย ๆ กันนะครับ เริ่มจากการสร้างตารางเล็ก ๆ ตารางหนึ่งซึ่งมีข้อมูลที่มีความ "เบ้" (Skew) สูง ๆ อยู่ในคอลัมน์ชื่อ ID และรวบรวมสถิติแบบปกติก่อน โดยเราจะทำให้ข้อมูลมีความเบ้สูงโดยที่หากคิวรีข้อมูลในคอลัมน์ ID ที่มีค่าอยู่ระหว่าง 0 ถึง 4 ค่าใดค่าหนึ่งจะได้จำนวนแถวประมาณ 20% ของข้อมูลทั้งหมด ในขณะที่ถ้าเราคิวรีเอาค่า 99 ฐานข้อมูลจะแสดงผลออกมาเพียงแถวเดียว
SQL> create table t
2 as
3 select mod(rownum,5) id, a.*
4 from all_objects a;
Table created.

SQL> update t
2 set id = 99
3 where rownum = 1;
1 row updated.

SQL> create index t_idx on t(id);
Index created.

SQL> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T' );
4 end;
5 /

SQL> select column_name, count(*)
2 from user_tab_histograms
3 where table_name = 'T'
4 and column_name = 'ID'
5 group by column_name;

COLUMN_NAME COUNT(*)
----------- ------------
ID 2

ตอนนี้คอลัมน์ ID ไม่มี Histogram ที่สมบูรณ์ โดยดีฟอลต์ -- มีแค่ 2 Bucket ดังที่แสดงในคิวรีตาราง USER_TAB_HISTOGRAMS เพื่อที่จะให้ Optimizer เข้าใจในลักษณะความเบ้ของข้อมูล ผมต้องการ Histogram ที่มีมากกว่า 2 Bucket การมีแค่ 2 Bucket ใน USER_TAB_HISTOGRAM จะบอก Optimizer ได้แค่ว่าค่าใดสูงสุดและต่ำสุดเท่านั้น จากการ Gather Statistics ตอนนี้ Optimizer มีข้อมูลดังต่อไปนี้
  1. ค่าสูงสุด (99)
  2. ค่าต่ำสุด (0)
  3. จำนวนของค่าที่ไม่ซ้ำกัน (กรณีนี้คือ 6 ได้แก่ 0,1,2,3,4,99)
  4. จำนวนแถวในตาราง (50,119)
ด้วยข้อมูลเหล่านี้ Optimizer จะเชื่อว่าค่าแต่ละค่า (ใน 6 ค่า) มีจำนวนแถวเท่า ๆ กันในตารางคือ 50119/6 = 8,353 ซึ่งถ้าคิวรีกำหนด WHERE ID = <ค่าใด ๆ> จะให้แถวเป็นจำนวน 8,353 แถว เพื่อพิสูจน์เราลองคิวรีด้วย ID=1 หรือ ID=99
{xtypo_code}SQL> set autotrace traceonly explain

SQL> select *
2 from t
3 where id = 1;

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU) | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8353 | 783K | 163 (2) | 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 8353 | 783K | 163 (2) | 00:00:02 |
-----------------------------------------------------------------------


SQL> select *
2 from t
3 where id = 99;

Execution Plan
-----------------------------------------------------------------------
Plan hash value: 1601196873
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU) | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8353 | 783K | 163 (2) | 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 8353 | 783K | 163 (2) | 00:00:02 |
-----------------------------------------------------------------------


Optimizer เชื่อว่ามีแถวที่ค่า ID = 1 อยู่ 8,353 แถว ซึ่งมากกว่าประมาณ 10% ของจำนวนแถวของข้อมูลทั้งตาราง มันจึงเลือกที่จะทำการอ่านข้อมูลทั้งตาราง (Table Access Full) แทนที่จะใช้อินเด็กซ์ เช่นเดียวกับเมื่อใช้ WHERE ID = 99 ซึ่งมีอยู่เพียงแถวเดียวในตาราง แต่ Optimizer คิดว่ามีอยู่ 8,353 แถว มันจึงใช้วิธีการเดียวกันคือ Table Access Full คราวนี้ลอง Gather Statistics อีกครั้ง โดยใช้คำสั่งเหมือนกับคำสั่งก่อนหน้า
SQL> set autotrace off
SQL>begin
2 dbms_stats.gather_table_stats
3 ( user, 'T' );
4 end;
5 /
SQL> select column_name, count(*)
2 from user_tab_histograms
3 where table_name = 'T'
4 and column_name = 'ID'
5 group by column_name;

COLUMN_NAME COUNT(*)
------------ -----------
ID 5


สังเกตว่าการทำ Gather Statistics ครั้งหลังนี้ เราได้มากกว่า 2 Bucket ใน USER_TAB_HISTOGRAMS, DBMS_STATS ที่ใช้ค่าพารามิเตอร์เป็น AUTO จะรวบรวมข้อมูลมากขึ้นกว่าเดิม ถ้าเราคิวรีตาราง SYS.COL_USAGE$ ตอนนี้ เราจะพบว่ามีแถวข้อมูลใหม่เพิ่มขึ้นมาแถวหนึ่งเป็นคอลัมน์และตารางที่เราทำการคิวรีก่อนหน้า ซึ่งหมายความว่ามีคิวรีในระบบที่ใช้เครื่องหมายเท่ากับกับคอลัมน์ดังกล่าว ซึ่งนั่นเป็นความลับที่ทำให้ DBMS_STATS เปลี่ยนวิธีการรวบรวมสถิติ และทำให้เกิดความเปลี่ยนแปลงขึ้นอย่างมากใน Query Plan ต่อไปนี้
SQL> select *2 from t3 where id = 1;

Execution Plan
-----------------------------------------------------------------------
Plan hash value: 1601196873
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU) | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10260| 961K | 164 (2) | 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 10260| 961K | 164 (2) | 00:00:02 |
-----------------------------------------------------------------------

SQL> select *
2 from t
3 where id = 99;

Execution Plan
-----------------------------------------------------------------------
Plan hash value: 470836197
-----------------------------------------------------------------------
| Id| Operation |Name |Rows|Bytes|Cost(%CPU)|Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | |1 |96 |2 (0) |00:00:01|
| 1 | TABLE ACCESS BY INDEX ROWID|T |1 |96 |2 (0) |00:00:01|
|* 2| INDEX RANGE SCAN |T_IDX|1 | |1 (0) |00:00:01|
-----------------------------------------------------------------------


สังเกตดูว่า Cardinality (Rows) ต่างกันอย่างไร ระหว่างเดิม 8,353 เปลี่ยนไปเป็น 10,260 (กรณี WHERE ID = 1) หรือ 1 (กรณี WHERE ID = 99) การเปลี่ยนแปลงไปของ Cardinality นี้มีผลต่อผลรวมของ Cost ทั้งหมดของตัว Query Plan ซึ่งทำให้ Optimizer สามารถเลือก Query Plan ได้ดีกว่า

และนี่คือข้อเท็จจริงที่ทำให้ Query Plan อาจจะเปลี่ยนเมื่อเวลาผ่านไป ซึ่งมีผลมาจากวิธีการรวบรวมสถิติของ Oracle ที่เปลี่ยนแปลงไป เมื่อเรากำหนดค่าของพารามิเตอร์ METHOD_OPT ใน DBMS_STATS ให้เป็น AUTO (AUTO ปกติเป็นค่าดีฟอลต์)
เรียบเรียงจาก:
http://www.oracle.com/technology/oramag/oracle/06-may/o36asktom.html

Sunday, June 27, 2010

การ Join กับ Query Plan

 การ Join กับ Query Plan
สอง Query ข้างล่างนี้ให้ผลเหมือนกันแต่ตัวไหนเร็วกว่า?
1) SELECT distinct D.deptno, D.dname
FROM dept D, emp E
WHERE E.deptno = D.deptno
ORDER BY D.deptno;

2) SELECT D.deptno, D.dname
FROM dept D
WHERE EXISTS
(
SELECT 1
FROM emp E
WHERE E.deptno = D.deptno))ORDER BY D.deptno;

ปกติเราต้องใช้การ Join ของสองตารางหรือมากกว่า ก็ต่อเมื่อเราต้องการคอลัมน์จากตารางทุกตารางที่เอามา Join กัน ดังนั้นเราไม่ควรจะ Join ตาราง EMP เข้ากับตาราง DEPT ถ้าในผลลัพธ์ที่เราต้องการไม่มีคอลัมน์ของ EMP อยู่เลย ซึ่งถ้าเป็นกรณีอย่างนี้คุณควรจะใช้ WHERE EXISTS หรือไม่ก็ WHERE IN มากกว่า (Cost-Based Optimizer [CBO] เห็นสองตัวนี้เหมือนกัน) เราอาจจะเขียน Query แบบนี้
select deptno, dname
from dept
where exists
( select NULL
from emp
where emp.deptno
= dept.deptno )
order by deptno;

หรือแบบนี้
select deptno, dname
from dept
where deptno in
( select deptno
from emp )
order by deptno;

ทั้งสองกรณี Optmizer อาจจะใช้ Semi Join (ซึ่งเอา deptno ใน dept ทีละ Row ไปเทียบกับ deptno ใน emp และจะหยุดการเปรียบเทียบของ Row นั้นทันทีที่พบว่ามี) หรือไม่ก็ใช้อินเด็กซ์กับ Nested Loops ในการหาใน EMP ว่ามี Row นั้นอยู่หรือเปล่า
{xtypo_info}Semi-Join ระหว่างตาราง 2 ตารางจะให้ผลเป็นแถวจากตารางแรกที่สามารถจับคู่กับแถวในตารางที่สองได้หนึ่งหรือมากกว่าหนึ่งคู่ ความแตกต่างระหว่าง Semi-Join และการ Join แบบธรรมดาคือแถวจากในตารางแรกจะถูกดึงมาครั้งเดียว แม้ว่าในตารางที่สองจะมีอยู่สองแถวที่สามารถจับคู่กับตารางแรกได้, Semi-Join จะถูกเขียนโดยใช้รูปแบบ EXISTS หรือ IN{/xtypo_info}

ตัวอย่างต่อไปนี้แสดงว่าโดยทั่วไปแล้วจะเกิดอะไรขึ้น เมื่อ Optimizer ตรวจพบว่า DEPT มีขนาดเล็ก และ EMP มีขนาดใหญ่ โดยมันจะสแกนแต่ละ Row ของ DEPT และจะเข้าไปหา DEPTNO (โดยใช้อินเด็กซ์) ในตาราง EMP ซึ่งมีขนาดใหญ่ ในทางกลับกันถ้า DEPT มีขนาดใหญ่ Optimizer ก็จะทำ Semi Join ทั้งก้อนโดยไม่ใช้ Index เราจะใช้ก๊อปปี้ของตาราง EMP และ DEPT ในการทดสอบดังนี้
SQL> create table emp as
2 select * from scott.emp;
Table created.

SQL> create table dept as
2 select * from scott.dept;
Table created.

SQL> create index emp_deptno_idx
2 on emp(deptno);
Index created.

จากนั้นเราก็เมคค่าสถิติของตารางขึ้นมาเอง โดยใช้ SET_TABLE_STATS ใน Package DBMS_STATS เพื่อที่จะทำให้ EMP ดูเหมือนตารางที่ใหญ่ มีขนาด 1,000,000 แถว และให้ DEPT ดูเหมือนตารางที่มีขนาดเล็กเพียง 100 แถว นอกจากนั้นเราจะบอก Optimizer ว่ามีอินเด็กซ์บนคอลัมน์ EMP.DEPTNO แต่เนื่องจาก Oracle10g จะทำการคำนวณสถิติของอินเด็กซ์ตั้งแต่ตอนสร้างอินเด็กซ์ เราจึงต้องเอาสถิติเดิมของอินเด็กซ์ออกก่อน และใส่ (เมค) สถิติใหม่เข้าไปให้สอดคล้องกับสถิติของตารางที่เราเมคเอาไว้แต่แรก
SQL> begin
2 dbms_stats.set_table_stats
3 ( user,
4 'EMP',
5 numrows => 1000000,
6 numblks => 100000 );

7 dbms_stats.delete_index_stats
8 ( user,
9 'EMP_DEPTNO_IDX' );

10 dbms_stats.set_index_stats
11 ( user,
12 'EMP_DEPTNO_IDX',
13 numrows => 1000000,
14 numdist => 10000,
15 numlblks =>10000 );

16 dbms_stats.set_column_stats
17 ( user,
18 'EMP',
19 'DEPTNO',
20 DISTCNT => 10000 );

21 dbms_stats.set_table_stats
22 ( user,
23 'DEPT',
24 numrows=> 100,
25 numblks => 100 );
26 end;
27 /

ตอนนี้เราก็พร้อมที่จะจับตาดูการตัดสินใจของ Optimizer เราใช้ AUTOTRACE เพื่อที่จะดู Query Plan ที่สร้างขึ้นจากคิวรีทั้งสองตัว เราแสดงคิวรีทั้งสองเพื่อที่จะแสดงให้เห็นว่าเมื่อ Developer ใช้ CBO (Cost-Based Optimizer) เขาไม่จำเป็นต้องตัดสินใจว่าจะเขียนอย่างไรให้ดีที่สุด (คือเพียงในระดับที่ยอมรับได้ก็เพียงพอ ส่วนที่เหลือให้ Optimizer ช่วยจัดการ) -- Optimizer จะเข้าใจรูปแบบของคิวรีที่เขียนขึ้นและเลือกวิธีการนำข้อมูลออกมาที่ดีที่สุด ซึ่งแตกต่างโดยสิ้นเชิงกับวิธีแบบ Rule-Based Optimizer (RBO) ซึ่งเป็น Optimizer แบบเก่าที่เราจะต้องเป็นผู้กำหนดแปลนในการรันคำสั่งเอาเอง ซึ่งจะมองเห็น WHERE IN กับ WHERE EXISTS ว่าแตกต่างกันโดยสิ้นเชิง

เมื่อเราใช้ CBO กับตาราง EMP ซึ่งมีขนาดใหญ่ และ DEPT ซึ่งมีขนาดเล็ก, Oracle อาจจะใช้ Query Plan ดังแสดงข้างล่างนี้ คืออ่านทุก ๆ แถวของ DEPT และทำสแกนอินเด็กซ์เฉพาะช่วง (Index Range Scan) บนตาราง EMP เพื่อหา DEPTNO ที่ตรงกับใน DEPT และเนื่องจากการสแกนจากในอินเด็กซ์เพื่อเปรีบบเทียบนี้มีแค่ 100 ครั้ง (เนื่องจากเราทำให้ Optimizer เห็นว่ามีข้อมูลใน DEPT แค่ 100 แถว เมื่อนำ 100 แถวเข้าไปหาในอินเด็กซ์จึงต้องทำทั้งหมด 100 ครั้ง) และเนื่องจาก (เราทำให้ Optimizer เห็นว่า) ตาราง EMP มีขนาดใหญ่ Optimizer จึงใช้ Query Plan นี้ทั้งกับกรณี WHERE IN และ WHERE EXISTS
SQL> set autotrace traceonly explain
SQL> select deptno, dname
2 from dept where deptno in
3 ( select deptno
4 from emp )
5 order by deptno;

Execution Plan
-----------------------------------------------------------
Plan hash value: 3383088615

-----------------------------------------------------------
 
| Id | Operation       | Name          | Rows  | Bytes |
-----------------------------------------------------------
| 0  |SELECT STATEMENT |               | 100   | 3500  |
| 1  |SORT ORDER BY    |               | 100   | 3500  |
| 2  |NESTED LOOPS SEMI|               | 100   | 3500  |
| 3  |TABLE ACCESS FULL| DEPT          | 100   | 2200  |
|* 4 |INDEX RANGE SCAN | EMP_DEPTNO_IDX| 1000K | 12M   |
-----------------------------------------------------------

Predicate Information (identified by operation id):
 
-----------------------------------------------------------

4 - access("DEPTNO"="DEPTNO")
 

SQL> select deptno, dname
 
2 from dept where exists
3 ( select null
4 from emp
5 where emp.deptno =
6 dept.deptno )
7 order by deptno;

Execution Plan
 
------------------------------------------------------------
Plan hash value: 3383088615

------------------------------------------------------------
 
| Id | Operation        | Name          | Rows  | Bytes |
------------------------------------------------------------
| 0  | SELECT STATEMENT |               | 100   | 3500  |
| 1  | SORT ORDER BY    |               | 100   | 3500  |
| 2  | NESTED LOOPS SEMI|               | 100   | 3500  |
| 3  | TABLE ACCESS FULL| DEPT          | 100   | 2200  |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX| 1000K | 12M   |
------------------------------------------------------------

Predicate Information (identified by operation id):
 
------------------------------------------------------------

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")



ต่อมาหากจำนวนของข้อมูลของ DEPT เพิ่มขึ้น และ Optimizer เห็นว่าจำนวนครั้งที่มันจะต้องเข้าไปตรวจข้อมูลจากอินเด็กซ์ของตาราง EMP มากขึ้นมาก ๆ แปลนที่ Optimizer จะใช้ในการดึงข้อมูลก็จะเปลี่ยนไป จากเดิมที่ใช้ Nested Loops ร่วมกับ Index Range Scan ก็จะเปลี่ยนไปเป็นการทำงานกับข้อมูลทั้งก้อน (แทนการใช้อินเด็กซ์) เพื่อที่จะให้เห็นภาพเราต้องบอก Optimizer ว่าตอนนี้ DEPT มีขนาดใหญ่ขึ้นมาก
{xtypo_code}SQL> begin
2 dbms_stats.set_table_stats
3 ( user,
4 'DEPT',
5 numrows=> 100000,
6 numblks => 10000 );
7 end;
8 /

จากนั้นก็รันคิวรีใหม่อีกทีหนึ่ง และดู Query Plan ที่เกิดขึ้นดังนี้
SQL> set autotrace traceonly explain
SQL> select deptno, dname
2 from dept where deptno in
3 ( select deptno
4 from emp )
5 order by deptno;

Execution Plan
-----------------------------------------------------------------
Plan hash value: 3127359958
-----------------------------------------------------------------
| Id | Operation           | Name           | Rows | Bytes |
-----------------------------------------------------------------
| 0  | SELECT STATEMENT    |                | 100K | 3417K |
| 1  | SORT ORDER BY       |                | 100K | 3417K |
|* 2 | HASH JOIN SEMI      |                | 100K | 3417K |
| 3  | TABLE ACCESS FULL   | DEPT           | 100K | 2148K |
| 4  | INDEX FAST FULL SCAN| EMP_DEPTNO_IDX | 1000K| 12M   |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------
2 - access("DEPTNO"="DEPTNO")
SQL> select deptno, dname
2 from dept where exists
3 ( select null
4 from emp
5 where emp.deptno =
6 dept.deptno )
7 order by deptno;

Execution Plan
 
---------------------------------------------------------------
Plan hash value: 3127359958

---------------------------------------------------------------
 
| Id | Operation           | Name          | Rows | Bytes |
---------------------------------------------------------------
| 0  | SELECT STATEMENT    |               | 100K | 3417K |
| 1  | SORT ORDER BY       |               | 100K | 3417K |
|* 2 | HASH JOIN SEMI      |               | 100K | 3417K |
| 3  | TABLE ACCESS FULL   | DEPT          | 100K | 2148K |
| 4  | INDEX FAST FULL SCAN| EMP_DEPTNO_IDX| 1000K| 12M   |
---------------------------------------------------------------

Predicate Information (identified by operation id):
 
---------------------------------------------------------------

2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


เราจะเห็นว่าขณะนี้ในคิวรีทั้งสองกรณี Optimizer เลือกใช้ (Index) Full Scan และ Hash Semi Join แทนการเข้าไปค้นใน Index ดังทีแรก เนื่องมาจากว่าไม่งั้นมันก็ต้องเข้าไปค้นในอินเด็กซ์เป็นจำนวน 100,000 ครั้ง
เรียบเรียงจาก:
http://www.oracle.com/technology/oramag/oracle/06-may/o36asktom.html

Friday, April 30, 2010

ความลึกลับของ Trigger ตอนที่ 1

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

เรื่องนี้เป็นเรื่องจริงที่เกิดขึ้น แต่ไม่ได้จริงเพราะโปรแกรมมีผีสิงหรอกครับ แต่เนื่องมาจากสมชาติไม่รู้จักการทำงานของทริกเกอร์ (Trigger แปลว่า ไก เช่นไกปืน หรือแปลว่าเหนี่ยวไกก็ได้) มาก่อน หรืออาจจะรู้แต่ไม่ได้เฉลียวใจ จึงทำให้ต้องนั่งผีหลอกวิญญาณหลอนอยู่อย่างนั้น

ทริกเกอร์ (Trigger) มีลักษณะคล้าย Stored Procedure ประเภทหนึ่งที่จะถูกกระตุ้นให้ทำงานเมื่อมีการกระทำกับตารางในฐานข้อมูล การกระทำเช่น การเพิ่ม (Insert), การแก้ไข (Update), หรือการลบ (Delete) จะมีผลให้ทริกเกอร์ทำงาน ด้วยคุณสมบัติอันน่าทึ่งอันนี้ทำให้ผู้ที่ได้รู้จักทริกเกอร์ใหม่ ๆ จะรู้สึกตื่นเต้น และอยากหาทางใดทางหนึ่งในการใช้มันให้ได้ เช่นการแปลงค่าก่อนนำไปใส่ไว้ในคอลัมน์, ใช้เช็คค่าที่ถูกต้องก่อนนำไปใส่ในคอลัมน์, หรือการสร้างข้อมูล Audit เป็นต้น ในขณะที่ทริกเกอร์มีคุณสมบัติดี ๆ หลายประการ แต่ขณะเดียวกันการใช้ทริกเกอร์ก็มีข้อพึงระวังเพื่อไม่ให้เกิดเรื่องลึกลับ อย่างที่สมชาติเจอ

สาเหตุหลักที่ทำให้ควรหลีกเลี่ยงการใช้ทริกเกอร์
1. ทริกเกอร์ทำให้ยุ่งยากในการดูแลในระยะยาว เนื่องจากตัวมันซ่อนอยู่ และรันโดยตัวมันเอง ซึ่งอาจจะเกิดขึ้นเมื่อใดก็ได้ หากถูกกระตุ้นโดยเหตุการณ์ต่าง ๆ ซึ่งคนส่วนใหญ่จะไม่ได้นึกถึง หรือลืมไปว่ามีทริกเกอร์อยู่ตรงนั้น
2. ทริกเกอร์มักจะถูกนำมาใช้อย่างไม่เหมาะสม โดยเฉพาะสถานการณ์ที่ไม่สามารถ Rollback ได้

ความยุ่งยากในการดูแลในระยะยาว
สมมติว่าคุณ กำลังทำงานในโครงงานของคนอื่น หรือเพราะว่าคุณต้องทำงานที่คนอื่นทำค้างไว้ ถ้าคุณต้องทำการสำรวจตัวโปรแกรม หรืออาจจะเป็น Stored Procedure ก็อาจจะเป็นวิสัยที่ทำได้ แต่ถ้าคุณพบกับโปรแกรมที่อาศัยทริกเกอร์ที่กระจายอยู่บนตารางต่าง ๆ มากมาย คุณคงต้องเกาหัวแกร็ก ๆ เพราะไม่รู้จะเริ่มตรงไหนดี?

ทริกเกอร์ทำ งานแบบเป็นผลข้างเคียงและเป็นเอกเทศ ไม่ได้อยู่กับโฟลวงานหลักที่คุณสามารถตรวจดูได้ทีละบรรทัด เมื่อคุณทำการอัพเดทเรคคอร์ดระบบอาจจะตอบกลับว่า 1 Row Proccessed แต่อาจจะมีสิ่งต่าง ๆ อีก 500 สิ่งเกิดขึ้นจากทริกเกอร์ก็ได้ นอกจากนี้ทริกเกอร์มักไม่ได้รับการจัดทำเป็นเอกสารอย่างเหมาะสม หรืออาจจะถูกมองข้ามไปเมื่อต้องทำการตรวจสอบตัวโปรแกรม

ลองดูจากตัว อย่างในเว็บไซท์ของ Tom Kyte ดังต่อไปนี้
"เรามีปัญหาเกี่ยวกับการอัพ เดทคอลัมน์ (varchar2) ผลการอัพเดทแสดงว่ามี 1,972 แถวที่ถูกอัพเดท (และเราได้ commit แล้ว) หลังจากนั้นเราดึงข้อมูลมาดูพบว่าคอลัมน์ไม่ได้ถูกอัพเดท แต่เมื่อเราอัพเดทคอลัมน์อื่นกลับทำให้ค่าในคอลัมน์นี้มีค่าเปลี่ยนแปลงไป"

เหตุการณ์ นี้จบเมื่อผู้ถามพบว่ามีประโยคนี้ :new.name := :new.fname || :new.lname อยู่ในทริกเกอร์ ซึ่งหมายถึงการที่ระบุให้กำหนดค่าให้กับคอลัมน์ name ด้วยค่าของ fname || lname (คือคำสั่งให้อัพเดทคอลัมน์ name โดยเอา fname (ชื่อ) ไปรวมกับ lname (นามสกุล) แล้วนำไปใส่ในคอลัมน์ name)

ผู้ออกแบบโปรแกรมไม่ควรจะใช้คอลัมน์ name ซึ่งรวมเอาชื่อกับนามสกุลเข้าไว้ด้วยกัน เพราะเรามีคอลัมน์ชื่อกับคอลัมน์นามสกุลอยู่แล้ว เราสามารถจะใส่ฟังก์ชั่น Concatenate (||) ไว้ในวิว โดยไม่ต้องนำค่าชื่อ + นามสกุลไปเก็บไว้ในคอลัมน์จริง ๆ นอกไปจากนี้ยังมีการเขียนทริกเกอร์อัพเดทคอลัมน์ name ที่ทำให้เกิดความสับสนขึ้นไปอีก เพราะเมื่อผู้ใช้ระบบต้องการอัพเดทคอลัมน์ name จะทำไม่ได้ถ้าไม่ได้หยุด (Disable) การทำงานของทริกเกอร์ไว้ แต่เมื่ออัพเดทคอลัมน์ fname, lname ข้อมูลในคอลัมน์ name กลับเปลียนไป

SQL> create table test_name (fname varchar2(10), lname varchar2(20), name varchar2(30));

Table created

SQL> insert into test_name values ('Somchai','Klaharn','Somchai Klaharn');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test_name;

FNAME LNAME NAME
---------- -------------------- ------------------------------
Somchai Klaharn Somchai Klaharn


SQL> create or replace trigger upd_testname_trg
2 before update on test_name
3 for each row
4 declare
5 -- local variables here
6 begin
7 :new.name := :new.fname || ' ' || :new.lname;
8 end upd_testname_trg;
9 /

Trigger created

SQL> update test_name set fname = 'Tanakorn',lname = 'Tavornsasnavong';

1 row updated

SQL> select * from test_name;

FNAME LNAME NAME
---------- -------------------- ------------------------------
Tanakorn Tavornsasnavong Tanakorn Tavornsasnavong

SQL> commit;

Commit complete

SQL> update test_name set name = 'Somchai Klaharn';

1 row updated

SQL> commit;

Commit complete

SQL> select * from test_name;

FNAME LNAME NAME
---------- -------------------- ------------------------------
Tanakorn Tavornsasnavong Tanakorn Tavornsasnavong

SQL> alter trigger upd_testname_trg disable;

Trigger altered

SQL> update test_name set name = 'Somchai Klaharn';

1 row updated

SQL> select * from test_name;

FNAME LNAME NAME
---------- -------------------- ------------------------------
Tanakorn Tavornsasnavong Somchai Klaharn


ทริกเกอร์ทำให้การดูแลระบบทำได้ยาก, สับสนง่าย และยังซ่อนตัวเองจากตรวจสอบ ในการตรวจสอบ Code ทริกเกอร์มักจะถูกลืมจากการตรวจสอบด้วย อาจจะเนื่องจากคนส่วนใหญ่เห็นว่าทริกเกอร์เป็นส่วนหนึ่งของวัตถุในฐานข้อมูล (ดังเช่น ตาราง เรามักจะไม่ไปตรวจสอบคำสั่งเช่น CREATE TABLE ตอนตรวจสอบ Code ในโปรแกรม)

การนำไปใช้อย่างไม่เหมาะสม

บ่อยครั้งที่ ผู้สร้างทริกเกอร์อาจจะไม่ได้คิดถึงพฤติกรรมของมันอย่างทั่วถึง ดังเช่น Code ต่อไปนี้

SQL> create trigger send_mail
2 after insert on purchase_order
3 for each row
4 begin
5 utl_mail.send
6 (sender=>'database@x.com',
7 recipients=>'orders@x.com',
8 subject=>'New Order '||:new.po_number,
9 message=>'... ');
10 end;
11 /

Trigger created.


Code ข้างต้นถูกต้องในแง่ของไวยกรณ์ และหลังจากที่ Insert ข้อมูลลงในตารางทริกเกอร์ก็จะทำงานได้อย่างถูกต้อง แต่มีความผิดพลาดในการนำไปใช้ กล่าวคือถ้าเกิดการ Rollback เมล์ที่ถูกส่งไปให้กับผู้รับจะไม่สามารถยกเลิกได้ ในการนี้ SMTP (Simple Mail Transfer Protocol) จะไม่รับรู้ถึงการยกเลิกหรือ Rollback ในฐานข้อมูล การ Rollback อาจจะเกิดจากสาเหตุอื่น ๆ ซึ่งอาจจะไม่ได้เกิดจากความจงใจเช่นการ Disconnect แบบไม่ปกติจากการปิดโปรแกรม SQL*Plus, การเกิดปัญหาเน็ทเวอร์ค เป็นต้น การใช้ทริกเกอร์กับยูทิลิตี้ต่าง ๆ เช่น UTL_FILE (จัดการไฟล์), UTL_MAIL (จัดการเมล์), UTL_HTTP (การใช้บริการของ HTTP) ฯลฯ ล้วนประสบปัญหาเดียวกัน ดังนั้นข้อควรจำประการหนึ่งในการใช้ทริกเกอร์คือ "อย่าใช้ทริกเกอร์กับระบบงานที่ไม่สามารถ Rollback ได้"

(ยังไม่จบ นะครับ ยังมีตอนต่อไป...ขอขอบคุณที่ให้ความสนใจและโปรดติดตามตอนต่อไปนะครับ)

เรียบเรียงจาก "The Trouble with Triggers"
โดย Tom Kyte, Oracle Magazine Volume XXII / Number 5 September/October2008, ASK TOM

เซ็ท Parameter ผิด คิดจนหัวบวม

ถ้าเราเซ็ทพารามิเตอร์บางตัวผิด (Oracle ไม่สามารถยอมรับค่านั้นได้) อาจจะทำให้เราไม่สามารถ Startup Database ได้ ดังเช่นตัวอย่างของการตั้งค่าพารามิเตอร์ SGA_MAX_SIZE ซึ่งจะต้อง Restart ระบบฯ หากค่าที่กำหนดเป็นค่าที่ไม่สามารถยอมรับได้ (Invalid) จะทำให้ระบบฯ Startup ไม่ขึ้น ดังเรื่องของสมสรวงที่จะกล่าวถึงต่อไปนี้

สมสรวง ต้องการเพิ่มหน่วยความจำให้กับระบบฐานข้อมูลออราเคิล โดยเขาต้องการให้ฐานข้อมูลใช้หน่วยความจำ 6GB โดยการเซ็ทพารามิเตอร์ SGA_TARGET ซึ่งสามารถทำได้โดยไม่ต้อง Restart ระบบฯ โดย Server มีหน่วยความจำทั้งหมดอยู่ 8GB บนเครื่อง โดยเขาต้องการเซ็ท SGA_MAX_SIZE ให้มากที่สุดเท่าที่จะทำได้ เขาจึงตัดสินใจเซ็ท SGA_MAX_SIZE ให้เท่ากับหน่วยความจำทั้งหมดที่มี

พารามิเตอร์ SGA_MAX_SIZE ใช้ในการกำหนดลิมิตสูงสุดของหน่วยความจำที่สามารถกำหนดให้ SGA_TARGET ได้ เป็นพารามิเตอร์ที่ต้อง Restart ระบบฐานข้อมูลจึงจะมีผล ในขณะที่พารามิเตอร์ SGA_TARGET สามารถกำหนดได้โดยไม่ต้อง Restart ระบบฯ

SQL> alter system set sga_max_size=8g scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27102: out of memory
OSD-00022: additional error information
O/S-Error: (OS 8) Not enough storage is available to process this command.

เนื่อง จาก SGA_MAX_SIZE เป็นพารามิเตอร์ที่ต้องการการ Restart ระบบฐานข้อมูล แต่เมื่อเขา Startup ฐานข้อมูลปรากฎว่าเขาพบ Error ORA-27102: out of memory และไม่สามารถที่จะเปิดฐานข้อมูลได้เลย

สาเหตุที่เป็นเช่นนี้ เนื่องจาก เมื่อสมสรวงใช้คำสั่ง alter system set sga_max_size=8g scope=spfile นั้น ออราเคิลจะบันทึกค่าของพารามิเตอร์นี้ไว้ในไฟล์ %ORACLE_HOME%\dbs\SPFILEsid.ORA และเมื่อสมสรวงใช้คำสั่ง STARTUP เพื่อเปิดระบบฐานข้อมูล ออราเคิลจะไปอ่านพารามิเตอร์ในไฟล์นี้ในการเปิดระบบ เนื่องจากค่าที่ระบุ SGA_MAX_SIZE มีขนาดเกินกว่าที่หน่วยความจำที่มี (หน่วยความจำที่มีอยู่ 8G นั้นส่วนหนึ่งถูกใช้ไปโดย O/S) จึงไม่สามารถเปิดระบบฐานข้อมูลได้

ผมว่าออราเคิลน่าจะมีฟังก์ชันในการตรวจสอบพารามิเตอร์ใน SPFile ก่อนที่จะยอมปิดระบบฐานข้อมูล และแจ้งเตือนให้ผู้บริหารระบบทราบถึงผล (Error) อันอาจจะเกิดขึ้นหากมีการ Startup ด้วย SPFile ตัวปัจจุบัน

การแก้ไข
ใน %ORACLE_BASE%\admin\orcl10g\pfile\ มีไฟล์พารามิเตอร์ชื่อ init.ora.xxxxxxxxxxxx อยู่ ซึ่งเป็นเท็กซ์ไฟล์ธรรมดา (ต่างจาก SPFile ซึ่งเป็น Binary) เราสามารถเลือกที่จะ Startup ระบบฯ ด้วยไฟล์พารามิเตอร์ตัวนี้แทน SPFile ได้ เราเรียกไฟล์นี้ว่า Pfile (Parameter File) เป็นไฟล์ลูกพี่ลูกน้องกับ Server Parameter File (SPFile) ซึ่งค่าของพารามิเตอร์ที่กำหนดไว้ภายในอาจจะเหมือนหรือต่างกันก็ได้

และ เนื่องจากการที่เราพยายาม Startup ด้วย SPFile ครั้งล่าสุดทำให้ระบบจำค่าพารามิเตอร์ที่ผิดเอาไว้ เราจึงจะต้องเคลียร์ค่าเหล่านี้โดยปิด Service => ลบหรือเปลี่ยนชื่อของ SPFile (ตัวเก่า) => แล้วเปิด Service ใหม่ => แล้วจึง Startup ระบบฯ ด้วย PFile

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tanakorn>oradim -shutdown -shuttype srvc -sid orcl10g

C:\Documents and Settings\tanakorn>ren C:\oracle\product\10.2.0\db_1\dbs\spfileorcl10g.ora spfileorcl10g_ori.ora

C:\Documents and Settings\tanakorn>oradim -startup -sid orcl10g

C:\Documents and Settings\tanakorn>exit

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\oracle\product\10.2.0\admin\orcl10g\pfile\init.ora.312255320268'
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 167775108 bytes
Database Buffers 436207616 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.

หลังจากที่เราเปิดระบบฯ ได้แล้ว เราควรจะสร้าง SPFile จาก Pfile ที่เราใช้ในการเปิดระบบฯ เอาไว้ เนื่องจาก SPFile ตัวเก่าใช้ไม่ได้แล้ว เมื่อสร้างเสร็จแล้วให้ Restart ระบบฯ อีกทีเพื่อให้ระบบฯ กลับมาใช้ SPFile อย่างเดิม

การเปิดฐานข้อมูลด้วย SPFile มีประโยชน์ที่เห็นได้ชัดคือทำให้เราสามารถที่จะเปลี่ยนค่าในพารามิเตอร์ได้ โดยสะดวก โดยไม่ต้องไปแก้ไขในเท็กซ์ไฟล์อย่าง PFile

SQL> create spfile from pfile='C:\oracle\product\10.2.0\admin\orcl10g\pfile\init.ora.312255320268';

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 167775108 bytes
Database Buffers 436207616 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

เป็นการเตรียมการที่ดีที่จะสร้าง PFile จาก SPFile ตัวปัจจุบันทุกครั้้ง ก่อนที่จะแก้ไขพารามิเตอร์ที่จะต้องทำการ Restart ระบบฯ เพื่อที่หากเกิดปัญหาจะสามารถนำเอา PFile ตัวนั้นมาใช้ Start ระบบฯได้เลย จากตัวอย่างเราใช้ไฟล์เก่า ซึ่งพารามิเตอร์บางตัวอาจจะไม่เหมือนกับที่ปรากฏใน SPFile ซึ่งอัพเดทกว่าเสมอ

SQL> conn / as sysdba
Connected.
SQL> create pfile from spfile;

File created.

Pfile ที่สร้างขึ้นโดยดีฟอลต์จะไปอยู่ที่ %ORACLE_HOME%\database\ (หรือในกรณีนี้คือ C:\oracle\product\10.2.0\db_1\database\)

Monday, April 5, 2010

อินเด็กซ์ที่ค่าไม่ซ้ำ (Unique Index)

อินเด็กซ์ที่ค่าไม่ซ้ำ (Unique Index) ในที่นี้หมายถึง B-Tree Index ที่เป็นแบบ Unique ซึ่งถือเป็นสุดยอดของอินเด็กซ์เนื่องจากความเร็วและการนำไปใช้งานที่ง่าย เราจะสร้างตารางใหม่ชื่อ MY_OBJECTS จากตาราง ALL_OBJECTS (ALL_OBJECTS เป็นตาราง Meta Data ของ Oracle มีอยู่ในทุก Schema) โดยมีฟิลด์ OBJECT_ID เป็นเลขรันนิ่ง เราจะใช้ฟิลด์นี้ในการสร้างอินเด็กซ์แบบไม่ซ้ำ

SQL> conn scott/tiger
Connected.

SQL> create table tmp_objects as select owner,object_name from all_objects;

Table created.

SQL> insert into tmp_objects select * from tmp_objects;

107518 rows created.

SQL> insert into tmp_objects select * from tmp_objects;

215036 rows created.

SQL> insert into tmp_objects select * from tmp_objects;

430072 rows created.

SQL> insert into tmp_objects select * from tmp_objects;

860144 rows created.

SQL> insert into tmp_objects select * from tmp_objects;

1720288 rows created.

SQL> commit;

Commit complete.

SQL> create table my_objects as select rownum as object_id,owner,object_name from tmp_objects;

Table created.

SQL> select count(*) from my_objects;

COUNT(*)
----------
3440576

SQL> drop table tmp_objects;

Table dropped.

หลังจากนั้นเราสร้างอินเด็กซ์แบบค่าไม่ซ้ำ (Unique Index) บนคอลัมน์ OBJECT_ID เมื่อเราคิวรีโดยใช้ OBJECT_ID=15000 แล้วจะพบว่า Optimizer สามารถใช้วิธีการค้นข้อมูลแบบอินเด็กซ์ค่าเดียว (Index Unique Scan) ซึ่งมีความเร็วสูงที่สุด แต่ถ้าเราใช้ OBJECT_ID > 15000 แล้วทำให้ Optimizer ไม่มีทางเลี่ยงที่จะต้องค้นหาแบบอินเด็กซ์ช่วงแทน (Index Range Scan) สังเกตดูค่าในคอลัมน์ Cost (%CPU) ของการคิวรีทั้งสองแบบ จะเห็นว่าการค้นหาแบบอินเด็กซ์ช่วงใช้เวลาสูงกว่าแบบอินเด็กซ์ค่าเดียว

SQL> create unique index myobjects_idx1 on my_objects(object_id);

Index created.

SQL> select * from my_objects where object_id = 15000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3640416373

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 1 | 47 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | MYOBJECTS_IDX1 | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=15000)

SQL> select * from my_objects where object_id < 15000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2829829274

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50492 | 2317K| 433 (1)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 50492 | 2317K| 433 (1)| 00:00:06 |
|* 2 | INDEX RANGE SCAN | MYOBJECTS_IDX1 | 50492 | | 122 (1)| 00:00:02 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"<15000)

Note
-----
- dynamic sampling used for this statement

คราวนี้ลองสร้างอินเด็กซ์ที่ค่าซ้ำกันได้บ้าง (Non Unique Index) บนคอลัมน์ OBJECT_ID เช่นกัน เมื่อเราคิวรีไม่ว่าจะโดยใช้ OBJECT_ID=15000 หรือ OBJECT_ID>15000 จะพบว่า Optimizer จะไม่สามารถใช้วิธีการค้นข้อมูลแบบอินเด็กซ์ค่าเดียว (Index Unique Scan) ได้เลย เนื่องจากอินเด็กซ์ที่เราสร้างไม่ได้เป็นอินเด็กซ์ที่ค่าไม่ซ้ำ (Unique Index) นั่นเอง ลองสังเกตดูอีกว่าเมื่อเราใช้เงื่อนไข OBJECT_ID>1500000 Optimizer จะใช้วิธีการ TABLE ACCESS FULL แทน เนื่องจากมันรู้ว่ามีข้อมูลจำนวนมากที่มี OBJECT_ID > 1500000 การเข้าไปหาข้อมูลตรง ๆ จากตารางน่าจะเร็วกว่าการไปค้นข้อมูลในอินเด็กซ์ก่อนแล้วค่อยไปหาในตาราง ลองเปรียบเทียบกับการหาหนังสือในห้องสมุด ถ้าเรารู้เลขเรียกหนังสือเราก็จะไปที่ตู้ดัชนีแล้วดูว่าหนังสืออยู่ที่ไหน แต่ถ้าเราต้องการหาหนังสือจำนวนครึ่งหนึ่งของหนังสือทั้งห้องสมุด เราคงไม่ไปใช้ตู้ดัชนี การเดินไปที่หิ้งแล้วไล่ไปทีละเล่มเลยดูเหมือนจะเร็วกว่า

SQL> drop index myobjects_idx1;

Index dropped.

SQL> create index myobjects_idx1 on my_objects(object_id);

Index created.

SQL> select * from my_objects where object_id = 15000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2829829274

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 1 | 47 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MYOBJECTS_IDX1 | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=15000)

Note
-----
- dynamic sampling used for this statement

SQL> select * from my_objects where object_id < 15000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2829829274

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50492 | 2317K| 441 (1)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 50492 | 2317K| 441 (1)| 00:00:06 |
|* 2 | INDEX RANGE SCAN | MYOBJECTS_IDX1 | 50492 | | 130 (1)| 00:00:02 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"<15000)

Note
-----
- dynamic sampling used for this statement

สรุปก็คือ ปกติการค้นข้อมูลแบบอินเด็กซ์ค่าเดียว (Index Unique Scan) จะเร็วกว่าการค้นหาแบบอินเด็กซ์ช่วง (Index Range Scan) ดังนั้นในทุก ๆ กรณีถ้าค่าในคอลัมน์นั้นไม่ซ้ำกันเลยให้ใช้อินเด็กซ์ที่ไม่มีค่าซ้ำ (Unique Index) เพื่อให้ Optimizer สามารถค้นข้อมูลแบบอินเด็กซ์ค่าเดียวไ้ด้เสมอ และในกรณีใด ๆ ถ้าเราสามารถจะใช้เครื่องหมายเท่ากับใน Where Clause ได้ย่อมจะให้ผลดีกว่าการใช้เครื่องหมายมากกว่าหรือน้อยกว่าหรือ LIKE หรืออื่น ๆ เนื่องจากสาเหตุเดียวกัน นอกจากนี้การ Optimizer อาจจะเลือกที่จะทำการตรงไปดูค้นข้อมูลในตารางโดยตรงเลยก็ได้ หากมันเห็นว่าการทำแบบนั้นเร็วกว่า โดยปกติถ้าข้อมูลที่จะดึงออกมามีปริมาณไม่เกิน 10 เปอร์เซ็นต์ของข้อมูลทั้งหมดแล้ว Optimizer จึงจะไปใช้อินเด็กซ์ (อาจจะมากหรือน้อยกว่าขอให้ผู้อ่านลองไปทดสอบเป็นการบ้านดูนะครับ) เช่น ถ้าข้อมูลมี 3 ล้านเรคคอร์ด ถ้าคิวรีโดยใช้ Where Clause แล้วผลที่ได้ไม่เกิน 3 แสนเรคคอร์ด Optimizer จะใช้อินเด็กซ์ทีมีในการคิวรี แต่ถ้าเกินมันก็จะไปค้นข้อมูลจากตารางโดยตรง

Monday, February 8, 2010

Oracle Heterogeneous Service (HS) การเชื่อมต่อ Oracle กับฐานข้อมูลตัวอื่น ๆ เช่น Microsoft SQL Server หรือ AS400 (ตอนที่ 2)

เมื่อครั้งที่แล้วเราได้อ่านเรื่องของ ณัฐพล และอธิชาติซึ่งใช้วิธีการที่แตกต่างกันในการแก้ปัญหาเรื่องการเชื่อมต่อระบบฐานข้อมูลต่างยี่ห้อกันโดย ณัฐพลใช้วิธีการ Export Import ข้อมูลเข้าออกจากฐานหนึ่งไปยังอีกฐานหนึ่งทุกวัน ส่วนอธิชาตินั้นใช้วิธีการที่เราเรียกว่า Heterogeneous Service ซึ่งจะทำให้ระบบฐานข้อมูลต่างยี่ห้อสามารถคุยกันได้โดยตรง ครั้งนี้เราจะมาลองดูวิธีปฏิบัติกันนะครับ
สำหรับขั้นตอนนั้นเราสามารถแบ่งได้เป็นหัวข้อ ๆ ดังนี้ครับ
1. ลงโปรแกรมที่จำเป็นของระบบฐานข้อมูลที่เราต้องการเชื่อมต่อด้วย เช่นหากเราต้องการจะเชื่อมต่อกับ AS400 เราจะต้องลงโปรแกรม IBM AS400 Client Access Express เพื่อที่จะได้ Driver ของ AS400 มาสร้าง ODBC ลองปรึกษา System Admin ที่ดูแล AS400 อยู่นะครับเขาน่าจะช่วยคุณได้ อันนี้ไม่ยาก
2. คราวนี้เราก็สร้าง Datasource ใน ODBC ชื่อ "AS400" ดังตัวอย่างข้างล่างนี้ครับ








3. สร้างไฟล์ชื่อ ORACLE_HOME\hs\admin\initAS400.ora เพื่อให้ Oracle รู้จัก ODBC Datasource ที่เราสร้างขึ้น ORACLE_HOME คือ Oracle Home ที่เราลง Oracle ไว้ซึ่งจะต่างกันไปตามเวอร์ชั่นที่เราลง Oracle ไว้ ตัวอย่างเช่น บนเครื่องวินโดวส์ XP ของผม ORACLE_HOME จะเป็น C:\oracle\product\10.2.0\db_1 ผมก็จะสร้างไฟล์ไว้ที่ C:\oracle\product\10.2.0\db_1\hs\admin\initAS400.ora เป็นต้น (ชื่อไฟล์จะพิมพ์ตัวใหญ่หรือเล็กก็ได้) ในไฟล์นี้ให้เราพิมพ์ข้อความลงไปดังนี้

HS_FDS_CONNECT_INFO = AS400 #ODBC DSN
HS_FDS_TRACE_LEVEL = OFF

โดยตรง "AS400" จะต้องเหมือนกับชื่อที่เราตั้งไว้ตอนสร้าง ODBC Datasource

4. จากนั้นเราจะต้องอัพเดท Listener โดยเพิ่มส่วนของ AS400 เข้าไป เพื่อให้ Oracle รู้จักเสมือนว่า AS400 เป็น Instance หนึ่งที่รันอยู่บนเครื่องของมัน โดยเราจะเข้าไปเพิ่มเติมข้อความในไฟล์ LISTENER.ORA ซึ่งบนเครื่องของผมจะอยู่ที่ ORACLE_HOME\network\admin\LISTENER.ORA (แทนที่ ORACLE_HOME ด้วย C:\oracle\product\10.2.0\db_1)

(SID_DESC=
(SID_NAME=AS400)
(ORACLE_HOME=C:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
)

ให้ใส่ตรงส่วน SID_LIST ดังตัวอย่างข้างล่าง (สีแดงคือส่วนที่เพิ่มเข้าไป)

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
         (SID_NAME = PLSExtProc)
         (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
         (PROGRAM = extproc)
      )
      (SID_DESC =
         (SID_NAME =AS400)
         (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
         (PROGRAM = hsodbc)
      )
  )

ตรง PROGRAM=hsodbc ให้พิมพ์ตรงตัวเลย ส่วนตรง ORACLE_HOME=ORACLE_HOME=C:\oracle\product\10.2.0\db_1 ถ้า Oracle Home ของคุณแตกต่างไปจากนี้ ก็ให้แก้ตาม Oracle Home ที่แท้จริงของคุณ หลังจาก Save ไฟล์แล้วเราจะต้อง Refresh Listener (Listener เป็นโปรแกรมเล็ก ๆ ที่ทำหน้าที่รอคอยการเชื่อมต่อจากเครื่อง Client) การ Refresh สามารถทำได้โดยการพิมพ์ที่ OS Prompt ดังนี้ (บนเครื่องผมจะเป็น DOS Prompt)

C:\>lsnrctl reload

5. จากนั้นเราก็ต้องอัพเดทไฟล์ TNSNAMES.ORA ไฟล์นี้เป็นไฟล์ที่เครื่อง Oracle Client ใช้ในการ Connect กับฐาน
ข้อมูล ในไฟล์จะประกอบด้วยข้อมูลว่าจะให้ Connect ไปที่เครื่องไหน ชื่อ Database คืออะไรเป็นต้น ไฟล์นี้จะอยู่ที่ ORACLE_HOME\network\admin\TNSNAMES.ORA

AS400 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=AS400))
(HS=OK)
)

ตรง HOST= จะเป็น "localhost" เสมอเนื่องจากเราต้องการให้ Connect เข้าหา AS400 ODBC ที่เราสร้างไว้บนตัวมัน ส่วน SID ก็คือชื่อของ Listener ที่เราได้สร้างไว้ในขั้นตอนก่อนหน้า ในไฟล์ TNSNAMES.ORA อาจจะมีข้อมูลของการ Connect ฐานข้อมูลอื่นอยู่ก่อนแล้ว ให้เราหาที่ ๆ จะเพิ่มข้อความใหม่ที่เหมาะสม โดยดูจากฟอร์แมตของเดิมเป็นหลัก

6. จากนั้นเราก็พร้อมที่จะติดต่อกับ AS400 แล้ว โดยการจะติดต่อเราจะต้องติดต่อผ่าน Database Link เท่านั้น เราไม่สามารถจะ Connect โดยตรง เช่นเราไม่สามารถจะ Connect โดยใช้วิธี SQL> connect qpcs@as400 ได้
การสร้าง Database Link ทำได้โดยการ Connect เข้าฐานข้อมูล Oracle โดยใช้ User ที่ต้องการให้ติดต่อกับฐานข้อมูล AS400 เช่น User ที่ต้องการเก็บข้อมูลพักของ AS400 หรือ User ที่ต้องการ Join ตารางใน Schema ของตนกับ AS400 โดย User นั้นจะต้องมีสิทธิ์ในการสร้าง Database Link ถ้าไม่มีก็ให้ DBA Grant ให้โดยใช้คำสั่ง

SQL> grant create database link to scott;

จากตัวอย่างเรา grant สิทธิ์ในการสร้าง Database Link ให้กับ Scott เราจะ Connect โดยใช้ User Scott แล้วสร้าง Database Link ก่อนอื่นเราจะต้องทราบ Username และ Password ที่จะใช้ในการ Connect เข้า AS400 ก่อน สมมติว่าเราจะ Connect เข้าสู่ AS400 ด้วย User QPCS (เป็น User จริง ๆ ที่มีอยู่บน AS400 และมีสิทธิ์ในตารางที่เราต้องการข้อมูล) โดยมี Password เป็น PassW01 เราก็สามารถจะสร้าง Database Link ได้ดังนี้

SQL> create database link as400 connect to qpcs identified by passw01 using 'AS400';

เพียงเท่านี้เราก็สามารถที่จะ Connect เข้ากับฐานข้อมูล AS400 โดยตรงโดยผ่าน User Scott บน Oracle

SQL> conn scott
Enter password:
Connected.
SQL> create database link as400 connect to qpcs identified by passw01 using 'AS400';

Database link created.

SQL> select * from ACCLIB.TBACCOUNT@as400;

คิวรีข้างบนนี้จะแสดงผลข้อมูลจากตาราง TBACCOUNT ใน Library ACCLIB ได้โดยตรงแบบ Real Time ถ้าเราต้อง
การสร้าง Copy ของตารางเก็บไว้ก็เพียงใช้คำสั่ง create as select เช่น

SQL> create table acclib_tbaccount nologging as select * from acclib.tbaccount@as400;

ถ้าเราไม่ซีเรียสเรื่องดึงข้อมูลจาก AS400 แบบ Real Time การสร้าง Copy ไว้บน Oracle จะทำให้การทำงานราบรื่นขึ้น เนื่องจากระหว่างวันที่มีการใช้งานระบบ AS400 อาจจะมีการ Lock ตาราง เป็นระยะ ๆ ซึ่งอาจจะทำให้เราไม่สามารถที่จะ Select ข้อมูลได้

บทความที่เกี่ยวเนื่องกัน
Oracle Heterogeneous Service (HS) การเชื่อมต่อ Oracle กับฐานข้อมูลตัวอื่น ๆ เช่น Microsoft SQL Server หรือ AS400 (ตอนที่ 1)