Sunday, August 30, 2009

การตรวจสอบ Performance โดยใช้ Enterprise Manager

ใน Oracle ก่อนเวอร์ชั่น 9 การตรวจสอบ Performance แต่ละตัวเป็นเรื่องค่อนข้างยุ่งยาก และมักจะต้องทำใน text mode แต่ในปัจจุบันนี้ Oracle ได้พัฒนา Tools ที่ใช้ในการบริหารฐานข้อมูล เพื่อให้การบริหารฐานข้อมูลทำได้ง่ายขึ้นและใช้เวลาในการเรียนรู้น้อยลง Oracle Enterprise Manager ตัวที่นำมาให้ดูเป็นตัวอย่างนี้เป็นตัวที่มากับ Oracle10g Database ซึ่งสามารถใช้งานได้เลยถ้ามีการกำหนดให้ลงไว้เมื่อตอนสร้างฐานข้อมูล ผู้ใช้สามารถเข้า Enterprise Manager โดยพิมพ์ URL บน Web Browser เช่น http://hostname:5500/em หรือ http://hostname:1158/em แล้วแต่ว่า Port ถูกตั้งค่าเป็นอะไร ผมจะไม่เขียนถึงรายละเอียดตรงนี้นะครับ เมื่อเข้าสู่หน้าจอหลักของ Oracle Enterprise Mananger แล้ว คลิ๊กแท็ป Performance เลยครับ

Sessions: Waiting and Working เป็น Chart ที่สำคัญที่สุดอันหนึ่ง แสดงแผนภูมิของสภาวะการทำงานของระบบฐานข้อมูล ณ ช่วงเวลาหนึ่งจนถึงปัจจุบัน โดยกราฟจะแสดงจำนวน Session ที่กำลังใช้ CPU และกี่ Session ที่กำลังใช้งาน I/O, Redo Writes หรือกำลังทำงานผ่านระบบเน็ทเวอร์ค หรือการรอคอยประเภทอื่น ๆ

ในสภาวะที่เหมาะสม ทุก ๆ Sessions ควรจะทำงานบน CPU (เป็นพื้นที่สี่เขียวในแผนภูมิ) และไม่มีการรอคอย (Wait) ซึ่งเป็นพื้นที่สีอื่นที่นอกเหนือจาำกสีเขียวของ CPU) ถ้าปริมาณของสีอื่น ๆ มีมากกว่าสีเขียวในอัตราส่วนสูง แสดงว่ามีการรอคอยในระบบจำนวนมาก (สังเกตสีของชนิดของการรอคอยเพื่อจะได้รู้ว่าเป็นการรอคอยประเภทใด) เราสามารถคลิ๊กบน Legend เพื่อดูรายละเอียดของการรอคอยแต่ละประเภท อย่างไรก็ตามบางครั้งการรอคอยก็เป็นสิ่งที่หลีกเลี่ยงได้ยาก เช่นตอนที่เราโหลดข้อมูลจำนวนมากจาก Disk เข้าสู่ Database Buffer เป็นต้น



Hosts: Average Run Queue Length แสดงถึงการที่ Process มากกว่าหนึ่งตัว กำลังพยายามแย่งใช้ CPU กันอยู่ โดย Run Queue Length แสดงถึง Process ที่พร้อมจะใช้ CPU แต่ไม่สามารถจะใช้ได้ เพราะมี Process อื่นใช้อยู่ ตัวอยางเช่น เมื่อมี User ที่ได้ Lock ต้องการใช้ CPU แต่ไม่สามารถใช้งานได้ ในขณะที่มี User คนอื่นต้องการจะ Lock ตารางนั้นบ้าง User คนหลังจะต้องคอยจนกว่า คนแรกจะได้ใช้ CPU และปล่อย Lock อย่างไรก็ตามในบางครั้งเราอาจจะเห็นว่ามีการใช้งาน CPU ถึง 100% แต่ Response Time อาจจะดีก็ได้ เนื่องจากไม่มี Process ใด ๆ รอคอย CPU อยู่เลย

Hosts: Paging Rate แสดงอัตราที่ CPU เขียน Page of Memory ไว้บน Disk เนื่องจาก RAM ไม่พอซึ่งก่อให้เกิดผลเสียกับประสิทธิภาพของระบบอย่างมาก และถ้ามี Process ใด ๆ ที่รันอยู่ต้องการข้อมูลของ Page ที่ถูกเขียนไปไว้บน Disk ก็จะต้องรอให้ I/O ทำงานให้ ซึ่งเป็นการทำงานที่ช้ามากเมื่อเทียบกับการทำงานบน RAM ล้วน ๆ ดังนั้นระบบควรจะมี RAM อย่างเพียงพอในการทำงาน ซึ่งจะทำให้ระบบไม่ต้องทำการ Paging เลย



Instance Throughput เนื่องจาก Sessions Chart บอกเพียงว่ามีการใช้งาน CPU และการรอคอยเท่าใด แต่ไม่ได้บอกว่าการใช้งานเหล่านั้นสมเหตุสมผลเพียงใด แผนภูมินี้จะแสดงน้ำหนักของการใช้ข้อมูลบน Sessions Chart เช่นถ้าจำนวน Session ใน Sessions Chart พุ่งขึ้นสูง แต่ตัวเลขใน Throughput นี้ก็สูงขึ้นด้วย ก็อาจจะยอมรับได้ว่ามีคนเข้ามาใช้ระบบเป็นจำนวนมาก จึงทำให้ระบบทำงานมาก ซึ่งก็ถือว่าระบบไม่ได้ช้าผิดปกติแต่อย่างใด แต่อาจจะต้องพิจารณาในแง่อื่น เช่นเมื่อมี User เข้ามาใช้ระบบมากขึ้น ก็อาจจะต้องทำการปรับปรุงขนาดของฮาร์ดแวร์เป็นต้น อย่างไรก็ตามถ้า Throughput ลดต่ำลง แต่ Sessions Chart แสดงการแย่งใช้งานสูง อย่างนี้แสดงว่าอาจจะต้องทำการจูนระบบแล้ว



ในส่วนของ Active Session คลิ๊กที่ หัวข้อบน Legend ที่ต้องการดูรายละเอียดจะเข้าสู่หน้า Active Session Waiting ให้คลิ๊กที่ "User I/O"

Active Sessions Waiting: User I/O แผนภูมินี้แสดงการรอการทำงานของดิสก์ (User I/O) โดยแสดงการรอใช้งานดิสก์ของ Session ต่าง ๆ ณ ช่วงเวลาหนึ่ง ๆ เราสามารถเลือกช่วงเวลา (ห้านาที) ที่ต้องการดูรายละเอียดได้โดยคลิ๊กที่แถบด้านล่างใต้แกน X ของแผนภูมิ เมื่อคลิ๊กแล้วจะปรากฎรายละเอียดที่ด้านล่างของแผนภูมิ

Legend แสดงการรอคอยประเภทต่าง แสดงไว้เป็นสีเพื่อให้สามารถแยกความแตกต่างบนแผนภูมิได้

บริเวณด้านล่างที่แสดงรายละเอียด (Detail for Selected 5 Minute Interval) ประกอบด้วย 5 แท็ปคือ
- Overview - แสดงภาพรวมของรายละเอียด
- Top SQL – เพื่อดู SQL statement ที่ทำให้เกิดการรอคอยยาวนานที่สุด
- Top Session – เพื่อดู session ที่รอคอยยาวนานที่สุด
- Top Files – เพื่อดูไฟล์ที่มีการรอคอยใช้งานนานที่สุด
- Top Objects – แสดง object (ตาราง) ที่มีการรอใช้งานนานที่สุด

ข้อมูลที่แสดงบนแท็ปต่าง ๆ มีดังนี้
1) แท็ป Overview แสดง Top Waiting SQL (แท็ป Top SQL) และ Top Waiting Sessions (แท็ป Top Sessions)
- Top Waiting SQL แสดงคำสั่ง SQL ที่มีการรอคอยนานที่สุด คลิ๊กที่รายการที่มีค่า % มากที่สุด (ยกเว้น Other) เพื่อดูคำสั่ง และ execution plan
- Top Waiting Session แสดง session ที่มีการรอคอยสูงสุด คลิ๊กที่รายการที่มีค่า % มากที่สุด (ยกเว้น Other) เพื่อดูรายละเอียดของ session เช่น โปรแกรมที่ใช้, เวลาล็อกอิน, ประเภทของการเชื่อมต่อเป็นต้น



2) แท็ป Top SQL แสดง Top Waiting SQL และสามารถแสดงรายละเอียดของ SQL ได้ด้วยโดยการคลิ๊กที่ Legend ของคำสั่งที่มีเวลารอคอยสูงสุด (แต่ละบรรทัดของ Legend หมายถึงแต่ละคำสั่ง SQL คำสั่งที่มีการรอคอยสูงสุดจะปรากฎเป็นพื้นที่ขนาดใหญ่ที่สุดบน Pie Chart) เมื่อเราคลิ๊กที่ บรรทัดใด ๆ บน Legend เราจะสามารถดูคำสั่ง SQL และ Execution Plan ของมันได้
บริเวณทางขวามือของแท็ปนี้จะแสดงรายละเอียดของคำสั่งที่แสดงในแผนภูมิทางด้านซ้ายเพื่อให้รู้ว่าคำสั่งที่ช้านั้น ๆ ใช้เวลาไปกับอะไรเป็นส่วนใหญ่ เราสามารถคลิ๊กที่รายการในคอลัมน์ Wait Event เพื่อดูจำนวนของการรอคอยที่ระดับช่วงระยะเวลาต่าง ๆ



3) แท็ป Top Sessions ถ้ากล่าวโดยให้เห็นภาพง่าย ๆ Sessions ก็คือการเชื่อมต่อเข้าสู่ฐานข้อมูลของ Users นั่นเอง ในแท็ปนี้เราสามารถเห็นว่ามี Sessions ใดที่มีการรอคอยนานที่สุด รวมทั้งจำนวน Sessions อยู่ทางด้านซ้าย และประเภทของการรอคอยอยู่ทางด้านขวา รวมทั้งโปรแกรมและ Username ที่ใช้ในการเชื่อมต่อกับฐานข้อมูลของ Session นั้น ๆ ด้วย



4) แท็ป Top Files แสดงไฟล์ที่มีการใช้งานสูง ไฟล์ที่มีการใช้งานสูงก็จะทำให้เกิดการรอคอยในการเข้าถึงข้อมูลในไฟล์ ซึ่งการแก้ไขอาจจะต้องทำการย้ายวัตถุที่มีการใช้งานสูง ๆ เช่นตารางบางตารางออกจากไฟล์นั้น ๆ
บริเวณทางด้านซ้ายจะแสดงไฟล์ต่าง ๆ ที่มีการรอคอยสูงเปรียบเทียบกันในแผนภูมิ Pie Chart ส่วนทางด้านขวาจะแสดงเวลารอคอยโดยเฉลี่ยของแต่ละไฟล์



5) แท็ป Top Objects แสดงวัตถุเช่น (ตาราง) ในฐานข้อมูลที่มีการรอคอยสูง เราสามารถใช้ข้อมูลนี้ร่วมกับแท็ป Top Files ในการจะย้ายตารางที่มีการรอคอยสูง ๆ ออกจากไฟล์ได้
บริเวณทางด้านซ้ายจะแสดงวัตถุต่าง ๆ (ส่วนใหญ่จะเป็นตาราง) ที่มีการรอคอยสูงเปรียบเทียบกันในแผนภูมิ Pie Chart ส่วนทางด้านขวาจะแสดงเวลารอคอยโดยเฉลี่ยของแต่ละวัตถุ



Enterprise Manager ยังมี Features อื่น ๆ อีกมากมายที่ช่วยในการบริหารฐานข้อมูล ด้วยความที่เป็น Graphic จึงทำให้ใช้งานได้ง่าย และการเห็นภาพหรือกราฟต่าง ๆ จะทำให้เข้าใจได้ง่าย หวังว่า Features ที่เกี่ยวกับ Performance ที่นำมาลงนี้น่าจะเป็นตัวตั้งต้นที่จะทำให้ผู้อ่านสามารถขยายผลในการศึกษาการใช้งานต่อไปโดยไม่ยาก

บทความที่เกี่ยวเนื่องกัน
1.การตรวจสอบการทำงานของ Session
2.การใช้ TKPROF เพื่อแสดงสถิติการรันของคำสั่ง SQL
3.Performance monitoring & tuning

Saturday, August 15, 2009

การกู้ข้อมูลกรณี Redo Log File ที่ไม่ได้ Active อยู่เสีย

Redo Log File มีอยู่ 3 สถานะคือ
1) Current คือตัวที่ระบบฐานข้อมูลกำลังเขียนอยู่
2) Active คือตัวที่ระบบฯ ยังต้องใช้ในการทำ Instance Recovery อยู่ และ
3) Inactive คือสถานะที่ไม่ได้ใช้งานใด ๆ แล้ว

กรณีที่ Redo Log File เสีย หากตัว (หรือกลุ่ม) ที่เสียมีสถานะเป็น Inactive จะจัดการง่ายกว่ากรณีที่เป็น Current หรือ Active โดยมีขั้นตอนในการกู้ Redo Log File ดังนี้

1) เช็คดูว่า Redo Log File ตัวที่เสียนั้นมีสถานะเป็น Inactive
2) ถ้าใช่ เคลียร์ข้อมูลใน Redo Log File นั้น
3) Open database
4) แบ็คอัพฐานข้อมูลทั้งหมด (Whole Database Backup)

ในบทความนี้เราเน้นที่การแก้ปัญหา Redo Log File เสียเมื่อโหมดในการเก็บล็อกของระบบฐานข้อมูลเป็นแบบ No Archive (ไม่มีการ Archive Redo Log มาเก็บไว้)
เมื่อ Redo Log File เสีย เราจะพบ Error Message คล้าย ๆ กับตัวอย่างข้างล่างนี้

------------------------------------------------------------
D:\> set oracle_sid=orcl2
D:\> sqlplus / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 83887484 bytes
Database Buffers 75497472 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\REDO03.LOG'
------------------------------------------------------------

จากตัวอย่างเรารู้ว่า Redo Log File ตัวที่มีปัญหาคือ Onlinelog 3 ซึ่งมีพาธอยู่ที่ D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\REDO03.LOG

เมื่อทราบว่าปัญหาเกิดจาก Redo Log File เราจะเริ่มแก้ปัญหาด้วยวิธีการที่กล่าวมา
1) เราจะเช็คดูว่า Database อยู่ในโหมด No Archive และสถานะของ Onlinelog 3 เป็น Inactive

------------------------------------------------------------
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 9

SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
3 1 INACTIVE
2 1 INACTIVE
------------------------------------------------------------

2) จากผลการตรวจสอบพบว่าระบบฐานข้อมูลอยู่ในโหมด No Archive และไฟล์ที่เสีย (3) มีสถานะเป็น Inactive เราจะเคลียร์ล็อกที่อยู่ใน Redo Log File 3

------------------------------------------------------------
SQL> alter database clear logfile group 3;
Database altered.
------------------------------------------------------------

3) จากนั้นจึงเปิดระบบฐานข้อมูล

------------------------------------------------------------
SQL> alter database open;
Database altered.
------------------------------------------------------------

4) ระบบจะกลับมาเปิดใช้งานได้อีกครั้ง ข้อควรจำก็คือจะต้องทำการ Shutdown Immediate, Startup Mount และทำการแบ็คอัพทั้งฐานข้อมูล (Whole Database Backup) เก็บไว้ทันที

บทความที่เกี่ยวเนื่องกัน
1. การกู้ข้อมูลกรณี Control File เสีย

Sunday, August 2, 2009

NLS_LANG คือตัวแปร Environment บนฝั่ง Client ไม่ใช่บน Database Server

อย่างที่ผมได้ให้หัวเรื่องไว้เกี่ยวกับ NLS_LANG คือตัวแปรตัวนี้เป็นตัวแปรบนฝั่ง Client หรืออย่างน้อยก็เป็นตัวแปรของ Client Application (ซึ่งจริง ๆ แล้วอาจจะ Install ไว้บน Database Server ก็ได้) ที่จะติดต่อกับฐานข้อมูล เพื่อให้เห็นภาพลองดูวิธีการตั้งค่าตัวแปรตัวนี้บน Windows ดูกันหน่อยนะครับ

C:\>set NLS_LANG="ARABIC_UNITED ARAB EMIRATES.AR8MSAWIN"
C:\>echo %NLS_LANG%
"ARABIC_UNITED ARAB EMIRATES.AR8MSAWIN"

เรา set NLS_LANG บน client เพื่อบอกว่าขณะนี้เราจะ connect ด้วย environment แบบไหน โดยตอน select ถ้า Character Set ในตัวอย่างคือ (AR8MSAWIN) มีขนาดเล็กกว่า Database Character Set เราจะเห็นเป็น question mark หมายถึงด้วย environment ของเรา ไม่รู้จักตัวอักษรที่ database ส่งมาให้ เช่นถ้า Database Character Set เป็น UTF8 แต่เรา set NLS_LANG ที่เครื่องเป็น US7ASCII ตัวอักษรที่ส่งมาจาก Database ทีไม่อยู่ใน range ที่ US7ASCII รู้จักจะกลายเป็น ???

ในทางกลับกันตอน insert ถ้า database มี Character Set ที่เล็กกว่าของเครื่อง Client ที่ insert เช่น database เป็น US7ASCII แต่เครื่อง client set NLS_LANG=american_america.TH8TISASCII ข้อมูลภาษาไทยที่เรา insert เข้าไปจะกลายเป็น ??? แต่ถ้า database เป็น TH8TISASCII เหมือนกับเครื่อง Client (หรือเป็น Character Set ที่เป็น superset ของ TH8TISASCII จะสามารถ insert ได้ ดังตัวอย่างข้างล่าง เราConnect เข้ากับ orcl3 ซึ่งมี Character Set เป็น US7ASCII (พารามิเตอร์ชื่อ NLS_CHARACTER_SET) โดยใช้ sqlplus บน DOS Command Window

C:\>set nls_lang=american_america.th8tisascii
C:\>sqlplus oe@orcl3
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 15:04:40 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII

1 rows selected.

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

SQL> insert into test_char(cname) values ('ธนากร');
1 row created.

SQL> select * from test_char;
CNAME VNAME
---------- ----------
?????

จากตัวอย่างเรา Insert เข้าไปในตารางในฐานข้อมูลที่มี Character Set ที่เป็น US7ASCII ในขณะที่เครื่อง Client ที่ใช้ในการ Insert มี Character Set (ที่ีตั้งค่าโดย NLS_LANG) ที่มีขนาดใหญ่กว่า US7ASCII (TH8TISASCII มีขนาด 8 บิท และมีจำนวนตัวอักษรมากกว่า US7ASCII ซึ่งมีขนาด 7 บิท) ค่าที่ได้จากการ Insert ตัวอักษรที่ไม่อยู่ในชุดตัวอักษร US7ASCII เลยจึงกลายเป็น '?' ทุกตัว

คราวนี้เราลองมาทดสอบกับฐานข้อมูลที่มี Character Set ที่เป็น TH8TISASCII บ้าง (NLS_LANG ยังคงเป็น TH8TISASCII)

SQL> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET TH8TISASCII

1 rows selected.
SQL> create table test_char (cname nchar(10), vname nvarchar2(10));
Table created.

SQL> insert into test_char (cname) values ('ธนากร');
1 row created.

SQL> select * from test_char;
CNAME VNAME
---------- ----------
ธนากร
1 row selected.

ดังนั้นเพื่อให้แน่ใจว่าจะไม่เกิดการสูญเสียข้อมูลมีข้อควรระลึกถึงเกี่ยวกับ NLS_LANG ดังนี้
1. ตั้งค่า NLS_LANG ให้เป็นตัวเดียวกับ Database Character Set เสมอ
2. ถ้าทำอย่างกรณีข้อ 1 ไม่ได้ และต้องทำ DML กับฐานข้อมูล(เช่น Insert, Update, Delete) ให้ตั้งค่า NLS_LANG ให้เป็น Subset ของ Database Character Set
3. ถ้าทำอย่างกรณีข้อ 1 ไม่ได้ และต้องทำการ Select ข้อมูลอย่างเดียว ให้ตั้งค่า NLS_LANG ให้เป็น Superset ของ Database Character Set

หมายเหตุ
1. Database Character Set สามารถดูได้จากตัวแปร 'NLS_CHARACTERSET'
2. คำสั่ง Set NLS_LANG จะมีผลต่อ Session นั้น ๆ เท่านั้น ถ้าเราปิด Command Window แล้วเปิดใหม่จะต้อง Set ค่าตัวนี้ใหม่ ถ้าต้องการให้มีผลถาวรอาจจะเข้าไปตั้งค่า Environment Variables (คลิ๊กขวาที่ My Computer เลือก Properties => คลิ๊กเลือก Advanced Tab แล้วคลิ๊ก Environment Variables)
3. โดยปกติหากเราไม่ได้ตั้งค่า NLS_LANG ค่าดีฟอลต์จะเก็บอยู่ที่ Registry ของเครื่องใน HKEY_LOCAL_MACHINE => SOFTWARE => ORACLE => KEY_OraDb10g_home1 ให้ดูที่ Registry ทางขวามือชื่อ NLS_LANG ซึ่งส่วนที่อยู่หลังจุดจะเป็น Character Set เช่น AMERICAN_AMERICA.TH8TISASCII ก็หมายความว่าเครื่อง Client นี้ (ถ้าไม่ได้ตั้งค่า NLS_LANG ดังวิธีการอื่น ๆ ที่กล่าวมา) มี Character Set เป็น TH8TISASCII

บทความที่เกี่ยวเนื่องกัน
1. การใช้ NCHAR และการกำหนด Character Sets