I opened a SR on metalink Number 6060279.992, and metalink note Title: Tracing Enhancements in 10g is shared with me. This article explains the new tracing possibilities in 10g with the package dbms_monitor and give some examples how you can use them and there is no new information that is not given in the 10g Performance Tuning Guide.

This one was really strange..

Best reagrds,
Tonguc

Thank you for your interest Mr.Adolfsson,

In deed I did provide the test case as follows;

SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE username = 'SYS'
AND osuser = 'TURKCELL\TCHASYILMAZ';

set serveroutput on
execute dbms_session.set_identifier('tong');
EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE('tong');
BEGIN
dbms_monitor.session_trace_enable(session_id => 38,
serial_num => 3,
waits => TRUE,
binds => FALSE);
END;
/
commit ;

SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE username = 'SYS'
AND osuser = 'TURKCELL\TCHASYILMAZ';

SELECT * FROM dba_enabled_traces ;

EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_DISABLE('tong');
BEGIN
dbms_monitor.session_trace_disable(session_id => 38,
serial_num => 3) ;
END;
/
commit ;

SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE username = 'SYS'
AND osuser = 'TURKCELL\TCHASYILMAZ';

I have no idea what is going on here, maybe a new year suprise for me I am happy it does work for you;

-- Enterprise Edition Release 10.2.0.2.0 and DBA role granted and platform_name Solaris[tm] OE (64-bit)
SET SERVEROUTPUT ON
BEGIN
dbms_output.put_line('VERSION ' || dbms_db_version.version);
dbms_output.put_line('RELEASE ' || dbms_db_version.release);
END;
/

BEGIN
dbms_monitor.session_trace_enable(session_id => 98,
serial_num => 21659,
waits => TRUE,
binds => FALSE);
END;
/

SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE sid = 98 and SERIAL# = 21659 ;

SELECT * FROM dba_enabled_traces ;

Output :

VERSION 10
RELEASE 2

PL/SQL procedure successfully completed

PL/SQL procedure successfully completed

SID SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS

---

----------

---

---------------

---

98 21659 DISABLED FALSE FALSE

TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS INSTANCE_NAME

---

----------------------------------------------------------------

---

--------------------------------

---

-----

---

Also tried on my laptop at XE like you did;

-- Express Edition Release 10.2.0.1.0 and DBA role granted and platform_name Microsoft Windows IA (32-bit)
SET SERVEROUTPUT ON
BEGIN
dbms_output.put_line('VERSION ' || dbms_db_version.version);
dbms_output.put_line('RELEASE ' || dbms_db_version.release);
END;
/

BEGIN
dbms_monitor.session_trace_enable(session_id => 38,
serial_num => 3,
waits => TRUE,
binds => FALSE);
END;
/

SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE sid = 38 and SERIAL# = 3 ;

SELECT * FROM dba_enabled_traces ;

Output :

VERSION 10
RELEASE 2

PL/SQL procedure successfully completed

PL/SQL procedure successfully completed

SID SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS

---

----------

---

---------------

---

38 3 DISABLED FALSE FALSE

TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS INSTANCE_NAME

---

----------------------------------------------------------------

---

--------------------------------

---

-----

---

Best regards, and a happy new year sir.
Tonguç

...and Happy new year to you too

Well, I think the following will clear up a few things...

I left out the client id and stat enable bits from your test case before, without even thinking about why I did that. Until now that is

First there's the statistics (that oracle gathers all the time), which you from 10.x can "scope" in on not only session or system level but also client id, module, action and service:

SQL> select sid,username,client_identifier from v$session where username='SYS';
 
       SID USERNAME                       CLIENT_IDENT
---------- ------------------------------ ------------
       159 SYS
 
SQL> exec dbms_session.set_identifier('some.id');
 
PL/SQL procedure successfully completed.
 
SQL> select sid,username,client_identifier from v$session where username='SYS';
 
       SID USERNAME                       CLIENT_IDENT
---------- ------------------------------ ------------
       159 SYS                            some.id
 
SQL> select * from v$client_stats;
 
no rows selected
 
SQL> exec dbms_monitor.client_id_stat_enable('some.id');
 
PL/SQL procedure successfully completed.
 
SQL> select client_identifier,stat_name,value from v$client_stats;
 
CLIENT_IDENT STAT_NAME                           VALUE
------------ ------------------------------ ----------
some.id      user calls                              2
some.id      DB time                               314
some.id      DB CPU                                314
some.id      parse count (total)                     1
some.id      parse time elapsed                     67
some.id      execute count                           2
some.id      sql execute elapsed time              115
some.id      opened cursors cumulative               1
...
27 rows selected.
 
SQL> select sysdate from dual;
 
SYSDATE
----------
2006-12-29
 
SQL>
SQL> select client_identifier,stat_name,value from v$client_stats;
 
CLIENT_IDENT STAT_NAME                           VALUE
------------ ------------------------------ ----------
some.id      user calls                             11
some.id      DB time                              1582
some.id      DB CPU                               1582
some.id      parse count (total)                     3
some.id      parse time elapsed                    226
some.id      execute count                           9
some.id      sql execute elapsed time              901
some.id      opened cursors cumulative               3
...
27 rows selected.
 
SQL> exec dbms_monitor.client_id_stat_disable('some.id');
 
PL/SQL procedure successfully completed.
 
SQL> select * from v$client_stats;
 
no rows selected

Another tool we have that's now also "scopable" (by client id, etc.) is Sql trace. For example, you can trace across sessions without tracing the entire server. Using the same id as in above example:

SQL> select * from dba_enabled_traces;
 
no rows selected
 
SQL> exec dbms_monitor.client_id_trace_enable(client_id => 'some.id',waits => true);
 
PL/SQL procedure successfully completed.
 
SQL> select * from dba_enabled_traces;
 
TRACE_TYPE
---------------------
PRIMARY_ID
----------------------------------------------------------------
QUALIFIER_ID1
------------------------------------------------
QUALIFIER_ID2                    WAITS BINDS INSTANCE_NAME
-------------------------------- ----- ----- ----------------
CLIENT_ID
some.id
 
                                 TRUE  FALSE
 
 
SQL> exec dbms_monitor.client_id_trace_disable('some.id');
 
PL/SQL procedure successfully completed.
 
SQL> select * from dba_enabled_traces;
 
no rows selected

Since you got no rows from the view, I guess dba_enabled_traces does not include e.g. session level trace.

You come up with very good questions, that makes for a good excercise, right before new years and all Hope stats vs tracing is a bit clearer now!

Message was edited by:
orafad