SQL跟踪分析学习笔记
(整理于2006年东莞裕元)
分析SQL語句性能三種方法:
一.TKPROF實用程序
用SQL_Trace來產生跟蹤文件內容,用TKPROF實用程序來對跟蹤文件內容進行格式化以達到可讀.
1.設置跟蹤初始化參數. (INITsid.ORA)
1>用戶級自我跟蹤(SQL_TRACE)
SQL>Alter session SET SQL_TRACE = TRUE;
SQL>Alter session SET SQL_TRACE = FALSE;
2>實例級跟蹤(SQL_TRACE)
SQL>Alter SYSTEM SET SQL_TRACE = TRUE;
SQL>Alter SYSTEM SET SQL_TRACE = FALSE;
3>用戶級的DBA跟蹤.
可以使用Oracle的PL/SQL包DBMS_SYSTEM來跟蹤用戶的情況.
<1>從V$SESSION中查出用戶的sid和serial#
SQL>select s.username,s.sid,s.serial#,p.spid,s.program
from v$session s,v$process p
where s.paddr = p.addr and s.username is not null and p.background is null;
USERNAME SID SERIAL# SPID PROGRAM
----------------- -------- ------- ----- ----------
SYSTEM 6 2301 1156 PLSQLDev.exe
SCOTT 10 2642 1948 Sqlexp.exe
<2>跟蹤用戶會話.
SQL>EXECUTE SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,2642,TRUE);
產生跟蹤文件:UNIX: ora_prod_1948.trc
NT : ora01948.trc
<3>停止跟蹤.
SQL>EXECUTE SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,2642,FALSE);
4>當設置SQL_TRACE為有效﹐則系統對每條SQL語句的執行情況提供:
.解析﹑執行﹑取數據的計數.
.CPU時間和占用時間.
.物理讀和邏輯讀.
.處理行數目.
.所解析的用戶名.
.每次提交和回滾的情況.
5>跟蹤文件產生在初始化文件參數USER_DUMP_DEST所指的目錄中.
2.用TKPROF格式化跟蹤文件.
TKPROF是一個獨立的可以在操作系統下運行的程序.它完成對跟蹤文件進行格式化.
$tkprof ora_12558.trc trace.txt
表示將ora_12558.trc跟蹤文件進行格式化﹐輸出可讀文件為trace.txt.
TKPROF命令語法:
TKPROF filename1,filename2 [SORT = [option][,option]]
[PRINT = integer]
[AGGREGATE = [YES|NO]]
[INSERT = filename3]
[SYS = [YES|NO]]
[[TABLE = schema.table]|[EXPLAIN = user/password]]
[RECORD = filename]
二.EXPLAIN PLAN
1.建立執行計划輸出表(PLAN_TABLE).
$Oracle_HOME/rdbms/admin/utlxplan.sql
2.EXPLAIN PLAN 分析執行計划.
EXPLAIN PLAN [SET STATEMENT_ID = 'statement_info']
[INTO plan_table] FOR sql_statement;
例:
SQL>EXPLAIN PLAN FOR select * from emp,dept where emp.dept_no = dept.deptno;
3.檢查PLAN_TABLE執行計划.
三.AUTOTRACE.
1.使用AUTOTRACE的准備.
1>每個需要執行AUTOTRACE的用戶要有自己的PLAN_TABLE表.
$Oracle_HOME/rdbms/admin/utlxplan.sql
2>以SYS登錄并運行plustrce.sql腳本來創建角色PLUSTRACE;
$Oracle_HOME/sqlplus/admin/plustrce.sql
3>授權PLUSTRACE角色給每個希望進行AUTOTRACE的用戶.
SQL>GRANT PLUSTRACE TO username;
2.使用AUTOTRACE.
AUTOTRACE只能在SQL*PLUS下執行﹐所以只需要在SQL語句前加上set autotrace on即可.
還有一些第三方軟件可分析SQL性能.
1.SQL Expert
2.PLSQL Developer
3.Toador