Prev: SQL alteration
Next: Impdp - Full Database
From: Mladen Gogala on 2 Jul 2010 14:42
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
From: Maxim Demenko on 2 Jul 2010 15:26
On 02.07.2010 20:42, 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
>

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;dbms_monitor.client_id_trace_enable
should make tracefile "reappear".

Best regards

Maxim
From: Mladen Gogala on 2 Jul 2010 15:41
On Fri, 02 Jul 2010 21:26:17 +0200, Maxim Demenko wrote:

> On 02.07.2010 20:42, 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
>>
>>
> 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;dbms_monitor.client_id_trace_enable
> should make tracefile "reappear".
>
> Best regards
>
> Maxim

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
From: Mladen Gogala on 3 Jul 2010 16:28
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(a)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(a)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

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
 | 
Pages: 1
Prev: SQL alteration
Next: Impdp - Full Database