Thursday, April 16, 2009

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

Updated: 5/4/2009

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

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

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

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

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

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

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

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

Last Updated: 9/4/2009

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

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

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

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

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

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

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

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

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

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

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

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