Oracle Session Tracing Part I
November 18, 2004
This is the first in a series introducing some of the new tracing concepts and options within Oracle. This installment focuses on the new CLIENT_IDENTIFIER environment variable that can be assigned to sessions.
The goal of this series is to inform DBAs on how to track and trace connected sessions so that they can properly determine where sessions are experiencing performance problems. This article presents the very important concept of assigning a client identifier to a session. This client identifier will be used in future articles, as this will be one way to initiate traces. This article first presents the issue of why we should use the client identifier and then how we can utilize it when looking at sessions as they are connected in real time.
To begin, we must first come to grips with the simple concept of the globalization of sessions within Oracle that occurs. The days of single dedicated server connections and one connection uniquely identifying a single user are long gone. Now many middle-tier applications make use of pooling mechanisms that hide the identity of a connected user or session that does not allow you or me to adequately track and trace effectively because of the reuse of session ids. This is where you need to start thinking of defining your own client identifier for the users that use your database systems. This is just what Oracle now has and using a client identifier allows you to either further segregate or consolidate sessions by assigning an identifier that describes the connection for a particular user or set of users. For example, suppose you have a development environment where everyone logs in as a particular schema user and because of the connection mechanisms employed, users will reuse the same session id sometimes when they connect. Well, if you ever wanted to trace these sessions to determine who was consuming vast amounts of resources or was issuing particular SQL, you would be very hard pressed to run a trace and filter for that user since the high activity would not guarantee any one user was on a particular session id. This is where you could employ the use of a client identifier since Oracle now lets us trace and report on a unique client identifier. To get this going all you need to do is invoke the DBMS_SESSION.SET_IDENTIFIER procedure when the session logs in. Figure 1 gives a very simplistic method through a logon trigger, to set the client identifier. You can use anything to distinguish the session, such as IP address, computer / host name, o/s user, or a predefined application name. I have made use of the DBMS_CONTXT calls for user environment information.
Now when we look at the V$SESSION view, we can see this CLIENT_IDENTIFIER set for various sessions. Listing 1 gives this type of output. Now, if we had the same database user connect through some polling mechanism, and if we set our client identifier properly, we have the potential to see a different client identifier. In addition, if we want to set the same client identifier for a group of users, we could do that also. There are also methods of setting the client identifier from within OCI and JDBC.
We will get to actually tracing in a future article, but the columns supplied in the SQL select statement in Listing 1 allow for filtering of trace statistics. I hope that you can see that by setting a client identifier, we have just added great power in determining the unknown users of our database. Through extra tracing options, we can now determine whom and what is causing problems in our database. Also, note that because this field can be changed at will, there is nothing to limit you to keeping the same CLIENT_IDENTIFIER for the life of the session. This may come in handy if your session actually performs different tasks that distinguish it from other sessions that originally had the same identifier.
After setting this client identifier, we have already seen that you may view this setting by a query to the V$SESSION view. You may can also see the statistics around a particular CLIENT_IDENTIFIER from the V$ACTIVE_SESSION_HISTORY view. Listing 2 gives a query that we would typically issue to see the historical wait activity for sessions in the last 30 minutes. Notice that we have the same client identifier (CLIENT_ID) for each of the rows displayed. This is fine if you are concerned with only tracking down one session that might be experiencing high wait times but sometimes you are concerned aboutthe accumulated wait time that is caused by an application. To answer that question, we can group on the CLIENT_ID column now to get a summation for a particular client identifier. This does assume you have set your client identifier to something more than what I have set mine to, and that it is set to something in line with the application area of interest. Listing 3 shows just such a query you might issue to sum total waits for an application area or in my case user connections across multiple sessions.
In addition, if you ever find yourself wanting to know what your CLIENT_IDENTIFIER is set to, so that you can monitor or take some form of logical action within an application, you need only issue the following SQL.
If you ever wish to clear out the CLIENT_IDENTIFIER for a session, you need only execute the following procedure.
The use of a CLIENT_IDENTIFIER can come in handy for many situations where monitoring individual sessions or a group of sessions is required. It does not matter if those sessions connect to the same session id or different ones. If you choose your client identifier properly for groups of applications or users, you can generate true wait related information that tells you exactly what they have been doing. Stay tuned for more session tracing in the next part of this series.