The Dbms_monitor program turns on 10046 events

Tag: is the tool instance log date BMS Bin Service--

In a multi-tiered environment with a connection pool or shared server, a session can span multiple processes, even across multiple instances. Dbms_monitor is a built-in package introduced in Oracle 10g that enables you to track the sessions of any user from the client to the middle tier to the back-end database, making it easier to identify specific users who create a large amount of work. Dbms_monitor replaces traditional tracking tools, such as DBMS_ support. You need a DBA role to use Dbms_monitor.

End-to-end application tracking can be based on the following:

Session: Based on session ID (SID) and serial number.

Client identifier: Allows tracking to be set across multiple sessions. Specifies the end user based on the login ID. Use Dbms_session. The Set_identifier procedure sets the value.

Instance: Specifies the given instance based on the instance name.

Service Name: Specifies a set of related applications. Use Dbms_service. The Create_service procedure sets the value.

Module Name: The developer uses DBMS_ application _info in their application code. The set _module procedure sets the value. Use this name to represent the module or code that executes.

Action Name: The developer uses DBMS_ application _info in their application code. The set _action procedure sets the value. Use this name to represent the action that the module performs.

The last 3 trace options are associated hierarchically, and you cannot specify the operation name without specifying the module name and service name, but you can specify only the service name, or only the service name and module name.

1. Set up tracking based on session ID and serial number

To set up tracing based on the session ID and serial number, first determine the SID and serial number of the session you want to track:

Select Sid,serial#,username From V$session; SID serial# USERNAME ----------  ---------- ------------------------------ 156 3588 SCOTT 142 1054 SYS

To enable tracing, you can execute the following statement:

sql> exec dbms_monitor.session_trace_enable (156,3588,true,false);

The third parameter is for wait (by default, True), and the fourth parameter is used to bind the variable (false by default).

To turn off tracing, you can execute the following statement:

sql> exec dbms_monitor.session_trace_disable (156,3588);

To track the current session, you can set the SID and serial# to null:

sql> exec dbms_monitor.session_trace_enable (null,null);

2. Setting up tracing based on client identifier

To set up tracing based on the client identifier that represents the user, you can run the following statement:

sql> exec dbms_session.set_identifier (' Bryan ID ');

To verify the client identifier, execute the following statement:

Select Sid,serial#,username, Client_identifier From V$session Where client_identifier is not null; SID serial# USERNAME Client_identifier ---------- ----------  ------------------------   ------------------ 156 3588 SCOTT Bryan ID

You can now set the trace for this client identifier:

sql> exec dbms_monitor.client_id_trace_enable (' Bryan ID ', true,false);

The second parameter is used to wait (by default, True), and the third parameter is used to bind the variable (false by default).

To disable this client identifier trace, you can execute the following statement:

sql> exec dbms_monitor.client_id_trace_disable (' Bryan ID ');

3. Set the service Name/module name/operation name Tracking

In order to use the action name, you must have a corresponding module name and service name. In order to use the module name, you must have a service name. Enables tracing for a given combination of service name, module name, and operation name for a database at the global scope, unless the instance name is specified for the procedure. The service name is determined by the connection string used to connect to the service.

The Oracle database is represented as a client of the service, that is, the database performs the appropriate actions on behalf of the client. A database can have one or more services associated with it. For example, you can have a database with two different services for a Web client: The book.us.acme.com for the client that purchased the book, and the soft.us.acme.com for the client that purchased the software. In this example, the database name is sales.acme.com, so the service name is not based on the database name. The service name is specified by the Service_names parameter in the initial parameter file. The service name defaults to the global database name consisting of the database name (db_name parameter) and the domain name (db_domain parameter).

To enable tracing for the service name, you can execute the following statement:

sql> exec dbms_monitor.serv_mod_act_trace_enable (service_name=> ' ebk2 ');

This will track all sessions named EBK2.

To enable tracing for a combination of service names, module names, and operation names, you can execute the following statement:

sql> exec dbms_monitor.serv_mod_act_trace_enable (service_name=> ' ebk2 ',- Module_name=> ' salary_update ', action_name=> ' Insert_item ');

In order to disable tracing in the preceding code, you can use the procedure serv_mod_act_trace_disable, as follows:

sql> exec dbms_monitor.serv_mod_act_trace_disable (service_name=> ' ebk2 ',- Module_name=> ' salary_update ', action_name=> ' Insert_item ');

To track the entire database or instance, you can execute the following statement (this is not recommended):

Execute dbms_monitor. Database_trace_enable (waits = TRUE, binds = FALSE,- instance_name = ' ebk1 ');

Skills:

When using Dbms_monitor, make sure that tracing is disabled when you are done, otherwise, each session that meets the specified criteria is tracked.

4. Enable the Tracking view

Viewing the dba_enabled_traces and dba_enabled_aggregations views, you can see the statistics that are enabled for tracking and collection. You can use these views to ensure that all tracking options are disabled.

The Dbms_monitor program turns on 10046 events