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

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