最近碰到一个奇怪的问题，在生产和其他比较正式的环境中进行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
Copyright (c) 1991, 2010, Oracle. All rights reserved.
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 < indlin224 pid:>
(ADDRESS=(PROTOCOL=tcp)(HOST=indlin224)(PORT=28961))
"D003" established:3097 refused:0 current:114 max:1022 state:ready
DISPATCHER < indlin224 pid:>
(ADDRESS=(PROTOCOL=tcp)(HOST=indlin224)(PORT=57578))
"D002" established:3566 refused:0 current:114 max:1022 state:ready
DISPATCHER < indlin224 pid:>
(ADDRESS=(PROTOCOL=tcp)(HOST=indlin224)(PORT=47939))
"D001" established:3857 refused:0 current:114 max:1022 state:ready
DISPATCHER < indlin224 pid:>
(ADDRESS=(PROTOCOL=tcp)(HOST=indlin224)(PORT=37229))
"D000" established:3118 refused:0 current:113 max:1022 state:ready
DISPATCHER < indlin224 pid:>
(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
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 oracle@indlin224 (S002)
共享服务的部分点到为止，来看和做trace的关系。
如果稍微等一会，通过两外一个session来查询session 8796的process情况，就发现进程编程了D001.
SPID USERNAME PROGRAM
------------------------ --------------- ------------------------------------------------
8786 oracle oracle@indlin224 (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.