[20170703]Oracle Call Interface Tracing

[20170703]Oracle Call Interface (OCI) Tracing.txt

--//不熟悉這些東西,僅僅做一個記錄.

The Oracle Call Interface (OCI) is a set of APIs which provides interaction with an Oracle database. It supports all
phases of a SQL statement execution.

If you ever wondered how to trace OCI function calls you can do it by setting EVENT_10842 environment variable.

--//例子:
set ORA_CLIENTTRACE_DIR=d:\temp
set EVENT_10842=server=all;user=all;stmt=all;level=15

--//注意:必須設定DIAG_ADR_ENABLED=off 在 sqlnet.ora中.在client還是服務端呢?

For ORA_CLIENTTRACE_DIR to take effect Automatic Diagnostic Repository (ADR) has to be disabled. You can disable it by
setting DIAG_ADR_ENABLED=off in your sqlnet.ora.

A tracefile is generated for each connection in the format of ora_skgu_<pid>.trc1 where pid is the process id of the
connection on the (client) system.
 
The following levels are supported:
1 - Trace all server attach and server detach calls for servers listed in "server" attribute of the environment
     variable.
2 - Trace all session begin, logon, session end, logoff calls for the users listed in "user" attribute of the
     environment variable.
3 - Trace all prepare, execute, fetch calls for the specified statement types listed in "stmt" attribute of environment
     variable.
4 - Trace all Bind, Define, Describe calls.
5 - Trace all OCI LOB calls
7 - Get statistical info on all connection pooling /connection related calls
8 - Get statistical info on all session info
9 - Get statistical info on all handle info
10 - Get statistical info on time taken in execute and fetch calls
11 - Get statistical info on transaction related calls
15 - Trace all calls with statistical info.

--//根據上面的資訊自己測試看看:
1.環境:
SCOTT@book> @ &r/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.在client端執行:
R:\>set ORA_CLIENTTRACE_DIR=r:\
R:\>set EVENT_10842=server=all;user=all;stmt=all;level=15

--//登入後,執行sqlplus語句沒有任何像ora_skgu_<pid>.trc1檔案產生.
--//先嚐試修改client的sqlnet.ora檔案看看.
SCOTT@78> host ls -l r:\*.trc1
-rw-rw-rw-   1 user     group       22794 Jul  3 10:42 r:\ora_skgu_6708.trc1

--//OK,確實可以產生跟蹤檔案.裡面一堆資訊,看不懂..^_^.