การ
Track คำสั่ง SQLจะเกิดขึ้นที่ฝั่ง Server (Server Side) โดยที่
Server จะเก็บรายละเอียดของ ทุกๆ SQL statement ที่ถูกส่งมาทำงาน
และทำการ Dump ข้อมูลที่เกิดขึ้นระหว่างการทำงานไปเป็น trace file
ซึ่งเราต้องทำการเปลี่ยนรูปแบบของ Trace file ที่เกิดขึ้นนั้นให้อยู่ในรูปแบบที่อ่านได้ก่อนที่จะนำมาวิเคราะห์การทำงานของ
SQL Command ต่อไป
Parameters
ก่อนที่จะเริ่มทำการ
Trace นั้น มี database parameters (init.ora) อยู่ 2 ตัวที่เราต้อง
Set ค่า คือ
1.
USER_DUMP_DEST
เป็นการกำหนดตำเหน่งของ
Trace file ที่เกิดขึ้นว่า ควรจะเขียนอยู่ใน Directory ใดในฝั่ง
Server
2.
TIME_STATISTICS
ควรกำหนดให้เป็น
True เพื่อจะได้มีข้อมูลที่เกี่ยวข้องกับเวลาใน Trace file ด้วย
ซึ่งการกำหนดอาจทำได้ทั้งในระดับ Database หรือระดับ Session
ในการกำหนดระดับ
Database ให้ Set parameter "TIME_STATISTICS=TRUE" ใน File init.ora
หรือเราอาจจะเลือกกำหนดในระดับ Session ซึ่งสามารถทำได้โดย
| SQL>
ALTER SESSION SET TIMED_STATISTICS=TRUE; |
Enable
Trace
เราสามารถบอกให้
Server ทำการสร้าง Trace file ได้อยู่ 2 ระดับด้วยกันคือ
1.
ระดับ Database, ระบุ Parameter SQL_TRACE=TRUE ใน file init.ora
การระบุแบบนี้
Trace file จะถูกสร้างกับ "ทุก" Database Connection ซึ่งก็อาจทำให้เราได้ข้อมูลที่มากจนเกินไป
2.
ระดับ Session, ระบุเฉพาะ Session ที่เราสนใจ (Session-by-Session)
วิธีการระบุแบบนี้จะขึ้นอยู่กับว่าเราใช้
Tools อะไรในการทำงาน อย่างเช่น
SQL*PLUS:
USER
Privilege
| SQL>
alter session set sql_trace true;
Session
altered.
SQL> |
DBA
Privilege
| SQL>
execute sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);
|
ซึ่ง : sid และ serial# ได้มาจาก query:
| Select
username, sid, serial#, machine from v$session; |
PL/SQL:
| dbms_session.set_sql_trace(TRUE); |
Oracle
forms:
| start
forms with f45run32.exe statistics=yes |
หรือสร้าง statement ด้านล่างนี้ใน PRE-FORM trigger:
| forms_ddl('alter
session set sql_trace true'); |
Oracle
reports:
เพิ่ม statement ล่างนี้ใน BEFORE-REPORT trigger:
| srw.do_sql('alter
session set sql_trace true'); |
PRO*C:
| EXEC
SQL ALTER SESSION SET SQL_TRACE TRUE; |
เมื่อเราได้
Turn On "SQL Tracing" แล้วนั้น Server จะทำการ Dump Data ไปเป็น
Trace file ไปเก็บไว้ใน Directory ที่เราได้ระบุไว้ที่ฝั่ง Server
โดยชื่อ file ที่เกิดขึ้นก็จะขึ้นอยู่กับ Operating System ที่เราเลือกใช้
อย่างเช่น ในระบบ Unix ชื่อ file ก็จะได้เป็น "ora_<pid>.trc"
โดยที่ pid คือ process id
Dump
file ที่ได้จะอยู่ ณ Directory ที่เรากำหนดไว้ให้กับ Parameter ชื่อ
USER_DUMP_DEST ใน file init.ora ซึ่งเราสามารถตรวจสอบดูได้จาก SQL*Plus
หรือ svrmgrl ดังนี้
| SVRMGR>
SVRMGR> show
parameter user_dump_dest
NAME TYPE VALUE
-----------------------------------
------- ------------------------------
user_dump_dest
string /oracle/OraHome1/admin/viper/u
SVRMGR> |
และในกรณีของ
Unix นั้นโดยปกติ Permission ของ File จะไม่ยินยอมให้คนอื่น ที่ไม่ได้อยู่ใน
DBA Group อ่านได้ ถ้าหาก DBA (Database Admin) ต้องการให้คนอื่นสามารถอ่าน
Trace File ได้ โดยไม่จำกัดเฉพาะคนที่อยู่ใน DBA Group ก็อาจสามารถทำได้โดย
ทำการ Set Parameter "_TRACE_FILES_PUBLIC=TRUE" ใน File init.ora
ด้วย
Use
TKPROF
Trace
File ที่เกิดขึ้นใน Directory USER_DUMP_DEST นั้น ซึ่งเป็น Format
ที่อ่านเข้าใจยาก เราจึงต้องทำการเปลี่ยน Data ให้กลายเป็น Format
ที่อ่านรู้เรื่อง (Human-readable) โดยใช้คำสั่ง 'tkprof' ในการเรียกใช้
จะมีด้วยกัน 2 วิธี คือ
1.
การเรียกใช้อย่างง่าย (Simplest format)
| $tkprof
tracefile.trc outputfile.txt |
ตัวอย่างเช่น
มี Trace file ชื่อ ora_6743.trc เราสามารถเรียก command ได้ดังนี้
| $
tkprof ora_6743.trc /tmp/tkprof_6743.txt |
จากขั้นตอนนี้
เราจะได้ Output file ชื่อ /tmp/tkprof_6743.txt ซึ่งจะแสดง Statement
ที่เกิดขึ้นในขณะที่ทำงานกับ Session นั้น ดังตัวอย่างข้างล่าง
| select
*
from
scott.members
call
count cpu elapsed disk query current rows
-------
------ -------- ---------- ---------- ---------- ----------
----------
Parse
1 0.04 0.03 1 0 1 0
Execute
1 0.00 0.00 0 0 0 0
Fetch
2 0.00 0.00 1 2 4 4
-------
------ -------- ---------- ---------- ---------- ----------
----------
total
4 0.04 0.03 2 2 5 4
Misses
in library cache during parse: 1
Optimizer
goal: CHOOSE
Parsing
user id: SYS |
เราจะได้ข้อมูลจากทุก
ๆ SQL Statement ที่ถูก Execute โดย Application นั้นๆ
Note:
ในกรณีที่ ช่องที่แสดงข้อมูลที่เกี่ยวข้องกับเวลา (elapsed) แสดงออกมาเป็นค่า
0 นั้น แสดงว่า เรายังไม่ได้กำหนด Parameter TIMED_STATISTICS ให้เป็น
True หรือกำหนดในระดับ Session ได้โดย
| SQL>
ALTER SESSION SET TIMED_STATISTICS=TRUE; |
2.
อีกวิธีที่ซับซ้อนขึ้น (More sophisticated application optimization)
ซึ่งเราต้องการให้ 'tkprof' อธิบายถึง query plan ที่ใช้ด้วย สิ่งที่ต้องทำเพิ่มเติมคือ
ต้องเพิ่ม Parameter "explain=userid/password" ต่อท้ายในการเรียกใช้คำสั่ง
tkprof ด้วย
ตัวอย่างเช่น
| $
tkprof ora_6743.trc /tmp/tkprof_6743_explain.txt explain=scott/tiger@viper |
จากขั้นตอนนี้
เราจะได้ Output file ชื่อ /tmp/tkprof_6743_explain.txt ซึ่งจะแสดง
Statement ที่เกิดขึ้นในขณะที่ทำงานกับ Session นั้น ดังตัวอย่างข้างล่าง
| select
*
from
scott.members
call
count cpu elapsed disk query current rows
-------
------ -------- ---------- ---------- ---------- ----------
----------
Parse
1 0.04 0.03 1 0 1 0
Execute
1 0.00 0.00 0 0 0 0
Fetch
2 0.00 0.00 1 2 4 4
-------
------ -------- ---------- ---------- ---------- ----------
----------
total
4 0.04 0.03 2 2 5 4
Misses
in library cache during parse: 1
Optimizer
goal: CHOOSE
Parsing
user id: SYS
Rows
Row Source Operation
-------
---------------------------------------------------
4 TABLE ACCESS FULL MEMBERS |
จะเห็นได้ว่า
ด้วยวิธีแบบนี้จะมีข้อมูลของ Execution plan แสดงให้เราต่อจากข้อมูลของ
parse/execute/fetch
Reference:
[1]
Michele Cyran. Designing and Tuning for Performance Release 2
(8.1.6), United State: Oracle Corporation,December 1997 |