SQL跟踪分析学习笔记

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