Re: Tracing the given client id
Date: Sat, 3 Jul 2010 20:28:41 +0000 (UTC)
Message-ID: <i0o6hp$olt$1_at_news.eternal-september.org>
On Fri, 02 Jul 2010 18:42:52 +0000, Mladen Gogala wrote:
> 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
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_at_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_at_medo
- 2010-07-03 16:01:28.878
- SESSION ID:(143.75) 2010-07-03 16:01:28.878
- CLIENT ID:() 2010-07-03 16:01:28.878
- SERVICE NAME:(oracle.home) 2010-07-03 16:01:28.878
- MODULE NAME:(SQL*Plus) 2010-07-03 16:01:28.878
- ACTION NAME:() 2010-07-03 16:01:28.878
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.comReceived on Sat Jul 03 2010 - 15:28:41 CDT