I executed the following code:


1 begin
2 DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(
3 client_id => 'Insight',
4 waits => TRUE,
5 binds => False);
6* end;
SQL> /

There aren't any trace files. I also enabled statistics collection but
all the stats are zero. The database is 10.2.0.5, Linux x86-64, RH 4.4.
Has anybody seen anything like that? I opened a TAR

--
http://mgogala.byethost5.com

On 02.07.2010 20:42, Mladen Gogala wrote:
Such situation may happen if trace was already enabled by other means
for session in question and tracefile was (re)moved, but session is
still open. If that was the case, then, oradebug
close_trace;dbms_monitor.client_id_trace_disable;d bms_monitor.client_id_trace_enable
should make tracefile "reappear".

Best regards

Maxim

On Fri, 02 Jul 2010 21:26:17 +0200, Maxim Demenko wrote:

That's not the case. At any rate, I want to trace 27 processes. Doing the
oradebug trick would defeat the purpose.



--
http://mgogala.byethost5.com

On Fri, 02 Jul 2010 18:42:52 +0000, Mladen Gogala wrote:

That doesn't work even on Oracle 11.2. In other words, significant part
of the DBMS_MONITOR functionality is simply not there. I opened SR and
the analyst asked me why am I doing traces and could I use AWR report
instead? This is a horrible bug, but I circumvented it by selecting all
SID,SERIAL# combinations for the given CLIENT_INFO and turning the trace
on for the session. The "trcsess" utility has no problems and collects
the trace files into the aggregate file which can then be analyzed by
orasrp. Be aware, however, that DBMS_MONITOR doesn't work as advertised.

Here is the situation for 11.2:

SQL> exec dbms_application_info.set_client_info('TEST1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
.....
14 rows selected.

Elapsed: 00:00:00.05
SQL> select e.ename,d.dname,d.loc
2 from emp e, dept d
3 where e.deptno=d.deptno
4 order by e.deptno;

ENAME DNAME LOC
---------- -------------- -------------
....
14 rows selected.

Elapsed: 00:00:00.02



SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.00
SQL>


From another session I checked whether the trace was enabled, by querying
DBA_ENABLED_TRACES:

SQL> select * from dba_enabled_traces;

TRACE_TYPE
---------------------
PRIMARY_ID
----------------------------------------------------------------
QUALIFIER_ID1
------------------------------------------------
QUALIFIER_ID2 WAITS BINDS PLAN_STATS INSTANCE_NAME
-------------------------------- ----- ----- ---------- ----------------
CLIENT_ID
TEST1

TRUE FALSE FIRST_EXEC

SERVICE_MODULE_ACTION
oracle.home
SQL*Plus
ALL_ACTIONS TRUE TRUE ALL_EXEC


Elapsed: 00:00:00.01
SQL>

So, let's see whether the trace file exists and what's in it:

SQL> oradebug setospid 5352
Oracle pid: 23, Unix process pid: 5352, image: oracle@medo
SQL> oradebug tracefile_name
/oracle/diag/rdbms/oracle/O11/trace/O11_ora_5352.trc
SQL> !less /oracle/diag/rdbms/oracle/O11/trace/O11_ora_5352.trc

Trace file /oracle/diag/rdbms/oracle/O11/trace/O11_ora_5352.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
ORACLE_HOME = /oracle/product/11.2.0/home1
System name: Linux
Node name: medo
Release: 2.6.18-194.8.1.el5PAE
Version: #1 SMP Thu Jul 1 19:46:23 EDT 2010
Machine: i686
Instance name: O11
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 5352, image: oracle@medo


*** 2010-07-03 16:01:28.878
*** SESSION ID143.75) 2010-07-03 16:01:28.878
*** CLIENT ID) 2010-07-03 16:01:28.878
*** SERVICE NAMEoracle.home) 2010-07-03 16:01:28.878
*** MODULE NAMESQL*Plus) 2010-07-03 16:01:28.878
*** ACTION NAME) 2010-07-03 16:01:28.878

Received ORADEBUG command (#1) 'tracefile_name' from process 'Unix
process pid: /oracle/diag/rdbms/oracle/O11/trace/O11_ora_5352.trc

That's it! Not only the trace file contains no performance trace, as it
should, even the client ID is not there! Notice that the client id is
empty! Basically, DBMS_MONITOR is badly broken. It doesn't work if I
enable the trace for the MODULE/SERVICE/ACTION combination, either.

Here is something for all the employees of the Oracle Corp. who are
reading this group:

http://en.wikipedia.org/wiki/Regression_test

Implementing proper regression testing before releasing the version into
the wild might not be a bad idea, after all. Is the DBMS_MONITOR
functionality available only to the those of us who have purchased the
additional Diag/Tuning Pack license?
--
http://mgogala.byethost5.com