[20121101]tkprof抽取sql語句.txt

[20121101]tkprof抽取sql語句.txt

有時候跟蹤分析sql語句,並不是要看裡面的遞迴的執行,或者效能問題.而是要收集跟蹤命令的執行序列.

檢視tkprof的幫助:

Usage: tkprof tracefile outputfile [explain= ] [table= ]

              [print= ] [insert= ] [sys= ] [sort= ]

  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.

  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.

  print=integer    List only the first 'integer' SQL statements.

  aggregate=yes|no

  insert=filename  List SQL statements and data inside INSERT statements.

  sys=no           TKPROF does not list SQL statements run as user SYS.

  record=filename  Record non-recursive statements found in the trace file.

...

有一個引數record=filename,可以記錄跟蹤整個sql語句序列,自己做一個簡單測試看看:

alter session set events '10046 trace name context forever, level 12';

show parameter cursor;

drop table t purge ;

create table t (a number);

insert into t values(1);

commit ;

insert into t values(2);

rollback;

variable va number;

exec :va := 1

select * from t where a=:va;

desc t

alter session set events '10046 trace name context off';

確定跟蹤檔案:

$ tkprof /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_30100.trc record=aaa.sql

SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',        6,'big integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM ;

drop table t purge  ;

create table t (a number) ;

insert into t values(1) ;

commit  ;

insert into t values(2) ;

rollback ;

BEGIN :va := 1; END;

/

select * from t where a=:va ;

alter session set events '10046 trace name context off' ;

--除desc t外,其他都記錄下來.

--show parameter cursor;變成了

SELECT NAME NAME_COL_PLUS_SHOW_PARAM, DECODE(TYPE, 1,'boolean', 2,'string', 3,'integer', 4,'file', 5,'number', 6,'big integer', 'unknown') TYPE, DISPLAY_VALUE

       VALUE_COL_PLUS_SHOW_PARAM

  FROM V$PARAMETER

 WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ)

 ORDER BY NAME_COL_PLUS_SHOW_PARAM, ROWNUM ;