[20211221]分析sql语句遇到的问题.txt

--//昨天在跟踪sql语句时遇到的问题，在测试环境做一个记录。

1.环境：

SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试：
SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        30       4817 38137                    DEDICATED 38138       26        182 alter system kill session '30,4817' immediate;

@ 10046on 12
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
@ 10046off

SCOTT@book>  @ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38138_0001.trc

@ 10046on 12
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
@ 10046off

3.问题：
--//打开跟踪文件/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38138_0001.trc：
$ grep sql_id /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38138_0001.trc
$ extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38138_0001.trc

--//我当时纳闷，为什么跟踪文件里面无法抽取sql语句。查看第一个跟踪文件，马上发现问题：
$ extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38138.trc
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee#
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj#=:1
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
select * from dept where deptno=10
select * from dept where deptno=10
select * from dept where deptno=10
alter session set events '10046 trace name context off'

--//仅仅看见3次select * from dept where deptno=10，也就是在第3次后该语句是软软解析，以后在跟踪执行计划不变没有重分析的情
--//况下，都是软软解析，跟踪文件就无法看到执行语句了。
--//问题的本质是我在同一个会话里面跟踪2次，并且修改跟踪文件名,我当时就是遇到这样的情况.

--//如果使用第2个跟踪文件执行
$tkprof /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38138_001.trc
output = aa
TKPROF: Release 11.2.0.4.0 - Development on Wed Dec 22 09:42:26 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

$ cat aa.prf
TKPROF: Release 11.2.0.4.0 - Development on Wed Dec 22 09:40:25 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38138_0001.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38138_0001.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       0  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       0  SQL statements in trace file.
       0  unique SQL statements in trace file.
      96  lines in trace file.
       7  elapsed seconds in trace file.

4. 附上extractsql.sh脚本:
$ cat extractsql.sh
#! /bin/bash
awk '/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT'