[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 ;
