最近碰到一個奇怪的問題，在生產和其他比較正式的環境中進行sql trace都沒問題，但就是測試環境的資料庫不知道怎麼的, 設定sql_trace,開啟診斷事件，dbms_system,dbms_monitor都試了，就是沒有trace日誌，我都懷疑是不是有些配置給禁用了。

檢視基本的引數設定，沒有發現什麼問題。
SQL> show parameter statis

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
statistics_level                     string      TYPICAL
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE

下午不甘心，還是測試了一把，還是沒有日誌。不過最終發現了問題。
得到當前的session

SQL> select sid,serial# from v$session where sid=174;

       SID    SERIAL#
---------- ----------
       174        571
然後在另外一個session中開啟trace
SQL> exec dbms_system.SET_SQL_TRACE_IN_SESSION(174,571,true);
PL/SQL procedure successfully completed.
檢視系統級對應的process id，是8790
SQL> select spid,username,pid from v$process where addr=(select paddr from v$session where sid=174 and serial#=571);
SPID                     USERNAME               PID
------------------------ --------------- ----------
8790                     oracle                  20
我也不指望它生成日誌了，看看process
> ps -ef|grep 8790
oracle    8790     1 0 06:07 ?        00:01:34 ora_d003_TESTABP4
oracle   25499 23099 0 13:53 pts/3    00:00:00 grep -i 8790
發現這個程式是一個共享服務程式。
再次檢視session的情況，確實是。

SQL> select sid,serial#,server from v$session where sid=174;

SID SERIAL# SERVER
---------- ---------- ---------------------------
174 571 SHARED
看看當前的session server情況
SQL> select server,count(*)from v$session group by server;

SERVER                        COUNT(*)
--------------------------- ----------
DEDICATED                           25
NONE                               493
SHARED                               2
有些session的server狀態時none,有些是shared,有些事dedicated.如果是none說明當前沒有共享服務程式來服務了。如果是shared說明還有共享服務程式，正在使用共享連線。
關於共享程式對應的user可以看到更多的資訊，sys對應的都是dedicated的session，其他的使用者連進來都是使用共享服務程式。

SQL> select username,server from v$session group by username,server;

USERNAME                       SERVER
------------------------------ ---------
                               DEDICATED
N1                             NONE
SYS                            DEDICATED
TESTDB4                         NONE
TESTDB7                         NONE
TESTDB7                         SHARED
TESTDB8                         NONE
TESTDB15                        NONE
TESTDB21                        NONE
TESTDB23                        NONE
TESTDB24                        NONE
TESTDB26                        NONE
TESTDB37                        NONE
TESTDBO26                       NONE

檢視共享服務的配置，可以看到設定了5個dispatcher

SQL> show parameter dispatchers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (DESCRIPTION=(SDU=2048) (ADDRE
                                                 SS=(PARTIAL=TESTE)(PROTOCOL=TCP
                                                 )(HOST=indlin224))) (DISPATCHE
                                                 RS=5)
max_dispatchers                      integer     20
SQL> show parameter servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers                   integer     100
parallel_max_servers                 integer     10
parallel_min_servers                 integer     2
parallel_servers_target              integer     192
shared_servers                       integer     20

檢視監聽器的情況，可以看到共享服務的分發器有5個，從D000到D004。

> lsnrctl service indlin224

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-APR-2014 14:22:01

Connecting to (address=(protocol=tcp)(host=indlin224)(port=1521))
Services Summary...

Service "TESTABP4" has 1 instance(s).
Instance "TESTABP4", status READY, has 6 handler(s) for this service...
    Handler(s):
      "D004" established:3003 refused:0 current:127 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=indlin224)(PORT=28961))
      "D003" established:3097 refused:0 current:114 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=indlin224)(PORT=57578))
      "D002" established:3566 refused:0 current:114 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=indlin224)(PORT=47939))
      "D001" established:3857 refused:0 current:114 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=indlin224)(PORT=37229))
      "D000" established:3118 refused:0 current:113 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=indlin224)(PORT=18463))
      "DEDICATED" established:0 refused:0 state:ready
      ......
         LOCAL SERVER
The command completed successfully

監聽的配置就是簡單的一句話
indlin224=
    (address_list=
        (address=
            (protocol=tcp)
            (host=indlin224)
            (port=1521)))
可以透過v$process來關聯查詢每個session對應的process program情況

SQL>   select sid,serial#,server from v$session where sid=(select sid from v$mystat where rownum<2);
       SID    SERIAL# SERVER
---------- ---------- ---------
       163        697 SHARED

SQL> select spid,pid,program from v$process where addr=(select paddr from v$session where sid=163 and serial#=697);
SPID                            PID PROGRAM
------------------------ ---------- ------------------------------------------------
8798                             24 (S002)

共享服務的部分點到為止，來看和做trace的關係。
如果稍微等一會，透過兩外一個session來查詢session 8796的process情況，就發現程式程式設計了D001.
SPID                     USERNAME        PROGRAM
------------------------ --------------- ------------------------------------------------
8786                     oracle          (D001)
檢視對應的session server情況，就從shared變成了none

SQL> select sid,serial#,server from v$session where sid=2947 and serial#=2427
2 /

SID SERIAL# SERVER
---------- ---------- ---------
2947 2427 NONE

討論了這麼多共享伺服器的東西，那麼話說過來，能不能對這類session做trace呢。甚至pl/sql在共享服務模式下是否也能夠做trace.
oracle給出的解釋如下。

How to Use PL/SQL Tracing with Multi-Threaded Server (MTS) (Doc ID 238935.1)
PL/SQL tracing cannot be used with the multi-threaded server (MTS).

Oracle trace files are opened at the process level, not the session level.

If SQL_TRACE is turned on while running MTS, trace information for more than
just your user session will be seen because MTS allows many user processes to
share very few server processes.

By turning on SQL_TRACE in MTS mode, all user sessions that are currently
sharing the same shared server process will be traced. The output from sessions
using MTS, XA or Oracle8 OCI may be spread across several trace files making it
very difficult to interpret.

So, if performance tuning is being done and SQL_TRACE needs to be turned on, a
dedicated connection must be used. In other words, if possible run the session
to be traced using a DEDICATED connection or do not use an MTS (Multi-threaded
Server) connection.

session跟蹤失效的問題和原因

相關文章