hi

i would like to know the best trace method in oracle for a session in connection pool environment.
so if i want to trace a specific session for a user (the application is asp.net) i cant decide any session he own or to trace???because i cant obtain machine name or osuser.

Hi Nabil,

>> best trace method in oracle for a session in connection pool environment.

Well, you need something to tell Oracle which session to trace. For example, The DBMS_MONITOR package has routines for enabling and disabling statistics aggregation as well as for tracing by session ID, or tracing based upon a combination of service name, module name, and action name.

You are going to need some method for determining which session you want traced, a awy to walk forward from asp.net to Oracle. Surely, your app server layer knows which Oracle session is which.

Here are the Oracle tracing choices:

http://www.dba-oracle.com/t_tracing_oracle_sessions.htm

Packages - Session tracing with the dbms_monitor and dbms_trace packages.

v$ scripts - Oracle provides many data dictionary views for tracing session and SQL consumption including v$process, v$session, v$sql.

AWR Tables - The automatic workload repository has many tables such as dba_hist_sqlstat and for displaying session trace details. See the book "Oracle Tuning: The Definitive Reference", for working session tracing scripts.

ASH tables - The 10g automatic session history has many tables (dba_hist_active_session_history) for tracing the consumption of individual sessions. See the book "Oracle Tuning: The Definitive Reference", for working session tracing scripts.

SQL Trace - The ancient TKPROF and MetaLink SQL tracing utility provides SQL session tracing.

Trace files - Oracle has the 10046 and 10053 trace utilities for displaying of the specific consumption of specific SQL statement execution.

Hi,

" i cant obtain machine name or osuser"

http://www.dba-oracle.com/art_builder_prop...or_auditing.htm

select
sid,
serial#,
status,
username,
osuser,
machine,
process,
program
from
v$session
where
audsid=userenv('sessionid')
and
osuser = 'ASPNET';



Will the dbms_application_info package get you the session numbers?

http://www.dba-oracle.com/job_scheduling/k...ng_sessions.htm

This is related:

http://www.freelists.org/archives/oracle-l...6/msg01025.html

Java app using connection pooling. Want to trace a user action.
The ability to turn 10046 tracing off/on would be included in the code, as
would calls to DBMS_APPLICATION_INFO to identify the session. Would the code
need to initiate tracing (and DBMS_APPLICATION_INFO) each time one of the
connections is grabbed?

I implemented your requirements while developing an application
some years ago.
Here are some implmentation details:

1)I wrapped the connectionpool class and the calls for
getting/releasing the connections were made trough the wrapper, this
means that in the wrapper I can enable tracing while leasing the
connection and disable while releasing it.

2)the ability to turn off/on the tracing was dictated by two factors:
the trace level of the class wrapper itself (very easy to do if you
use log4J or similar that gives automatically the trace level for the
class) and an optional parameter passed to wrapper while leasing the
connection.
Here is some java pseudocode
//inside a servlet o something else
Connection conn=pool.getConnection(clientid,enableTrace);
//client id serves the purpose of identifing the beginning
//and the end of this program in the trace file
//enableTrace is a boolean parameter indicating whether or not activate trace
try {
...
//use the connection
...
} finally {
pool.releaseConnection(conn);//disable trace
}


inside the pool class there is something similar to:
public Connection getConnection(String clientId,boolean enableTrace) {
if (logger.isDebugEnabled() || enableTrace) {
//execute sql to enable trace and set the clientId through a SQL o
PL/SQL call
}
//eventually fills a map with connId,clientId
...
}

public Connection releaseConnection(Connection conn) {
//get form the map the clientId
//execute sql or pl/sql to mark the end of the session trace
//stop the trace only if logger.isDebugEnabled() is false
}
...
}