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