Using dbms_monitor

Oracle 10g中提供了一個新的包進行SQL Trace: DBMS_MONITOR。它提供了多種級別的trace[@more@]

SQL> select sid,serial# ,username from v$session;

SID SERIAL# USERNAME

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

140 57

145 1

147 1

150 1

153 34 SYS

155 1

156 1

159 5 TEST

160 1

161 1

162 1

SID SERIAL# USERNAME

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

163 1

164 1

165 1

166 1

167 1

168 1

169 1

170 1

19 rows selected.

SQL> exec dbms_monitor.session_trace_enable(159,5,true,false);

The third parameter is for waits( default is TRUE),and the fourth parameter is for bind variables(default is false)

To turn off the trace:

SQL>exec dbms_monitor.session_trace_disable(159,5).

To trace the current session,set the SID the serial# to null;

Exec dbms_monitor.session_trace_enable(null,null);

Setting trace based on identifier

To set the trace based on client identifier as the user, run the following:

SQL> exec dbms_session.set_identifier('client.liu');

To verify the client identifier,

SQL> select sid,serial#,username,client_identifier from v$session where client_identifier is not null;

SID SERIAL# USERNAME CLIENT_IDENTIFIER

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

136 37 TEST client.liu

159 5 TEST tony.liu

SQL>

Now we can set the trace for this client identifier:

SQL> exec dbms_monitor.client_id_trace_enable('client.liu',true,false);

The second parameter is for waits(default is TRUE), and the third parameter is for bind variables(default is FALSE)

To disable this client identifier trace,

SQL> exec dbms_monitor.client_id_trace_disable('client.liu');

Setting Trace for the Service Name/Module Name/Action Name

In order to use the action name, the module name and the service name must be present, in order to use the module name, the service name must be present. Tracing will be enable for a given combination of service name, module name,and action name globally for a database unless an instance name is specified for a procedure. The service name is is determined by the connect string used to connect to a service.

An database is represented to clients as a service; that is, the database performs work on behalf of clients. A database can have one or more services associated with it. For example, you could have one database with two different services for clients : book.us.acme.com for clients making book purchases and soft.us.acme.com for clients making purchases. In this example ,the database name is sales.acme.com, so the service name isn’t even based on the database name. The service name is specified by the SERVICE_NAMES parameter in the initialization parameter file. The service name defaults to the global database name,a name comprising the database name(DB_NAME parameter) and the domain name(DB_DOMAIN parameter).

To enable tracing for a service name,

SQL>exec dbms_monitor.serv_mod_act_trace_enable(service_name=>’orcl’);

This will trace all sessions with a service name of orcl.

To enable tracing for a combination service,module , and action.

SQL>exec dbms_monitor.serv_mod_act_trace_enable(service_name=>’orcl’,module_name=>’salary_update’,action_name=>’insert_item’);

To Disable tracing in the preceding code, use the procedure

SERV_MOD_ACT_TRACE_DISABLE, as shown here:

SQL>exec

dbms_monitor.serv_mod_act_trce_disable(service_name=>’orcl’, module_name=>’salary_update’,action_name=>’insert_item’);

To trace for entire db or instance(not recommended),

Execute dbms_monitor.database_trace_enable(‘waits=>true,binds=>false,instance_name=>’orcl’);

Enable tracing views

Dba_enabled_traces

Dba_enabled_aggregations

Example:

SQL> execute dbms_monitor.client_id_stat_enable('client.liu');

PL/SQL procedure successfully completed.

SQL> select * from dba_enabled_aggregations;

AGGREGATION_TYPE PRIMARY_ID QUALIFIER_ID1

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

QUALIFIER_ID2

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

CLIENT_ID client.liu

Trcsess multiple trace files into one file

The oracle10gallows trace data to be selectively extracted from multiple trace files and saved into a single trace file based on criteria such as session ID or module name. This command-line utility is especially useful in connection pooling and shared configurations, where each user request could end up in a separate trace file. TRCSESS lets you obtain consolidated trce information pertaining to a single user session.

Thie consolidated trace file can be created according to several criteria:

Session id

Client id

Service name

Action name

Module name

Example 1

Trcsess utput=trace.trc service=’orcl’

Example 2

Exec dbms_session.set_identifier(‘client’);

Exec dbms_monitor.client_id_stat_enable(‘client’);

Trcsess utput=trace.trc client=client *.trc