This is probably the most unique aspect of using DBMS_MONITOR. Because you have many session identification options, you're likely to find a way to identify session(s) of interest. Sessions can be identified by their Oracle instance, session identifier (v$session.sid), client identifier (v$session.client_identifier), service name (v$session.service_name), module (v$session.module), program (v$session.program), and various combinations.

Once the session is attached, data collection starts and will continue until the session qualifications are no longer met. For example, if the DBMS_MONITOR were set to trace all sessions with a program name of arpost, once a session's program name became arpost, a trace file associated with the running server process would be created and tracing details written. But once the session either disconnected or the program name changed, the tracing would stop. Then if the session's program were reset to arpost, the tracing information would once again start flowing. The power lies in the fact that it makes no difference which server process or client process the session is associated with at the time. If two different server processes are involved with a single or even multiple sessions with their program set to arpost, then two trace files will be created. This allows for connection pooling, Oracle's multithreaded shared server capability, and various other architectures to benefit.

Since the key is to identify Oracle server processes of interest, you need to investigate how the session identification columns are set for your application. You may be surprised by what you see, so run a simple query and take a look. Figure 5-19 is the result of such a query run on a real PeopleSoft system. While not shown because DBMS_MONITOR does not directly filter on this column, the client_info column provides a wealth of information about the PeopleSoft session. Notice that the application and the DBA have not set the client identifier column, so unless the DBA takes action to set this column, it will remain empty. By closely examining such a query on your system, you will begin to understand your session identification options.