Key Concepts - Troubleshooting Oracle Performance - page 44

Note that other views showing SQL statements, for example v$sql , also contain the module and action columns.
One word of caution: the attributes are associated to a specific session, but a given SQL statement can be shared
between sessions having different module names or action names. The values shown by the dynamic performance
views are the ones that were set at hard parse time in the session that first parsed the SQL statement. This can be
misleading if you aren't careful.
Now that you have seen what is available, let's take a look at how you can set these values. The first method,
PL/SQL, is the only one that doesn't depend on the interface used to connect the database. As a result, it can be used
in most situations. The following four—OCI, JDBC, ODP.NET, and PHP—can be utilized only along with the specific
interface. Their main advantage is that the values are added to the next database call instead of generating extra
round-trips, which is what a call to PL/SQL does. Thus, the overhead of setting the attributes with them is negligible.

PL/SQL
To set the client identifier, you use the set_identifier procedure in the dbms_session package. In some situations,
such as when the client identifier is used along with a global context and connection pooling, it may be necessary to
clear the value associated with a given session. If this is the case, you can use the clear_identifier procedure.
To set the client information, the module name, and the action name, you use the set_client_info , set_module ,
and set_action procedures in the dbms_application_info package. For simplicity, the set_module procedure
accepts not only the module name but also the action name.
The following PL/SQL block, which is an excerpt of the session_attributes.sql script, shows an example:

BEGIN
dbms_session.set_identifier(client_id=>'helicon.antognini.ch');
dbms_application_info.set_client_info(client_info=>'Linux x86_64');
dbms_application_info.set_module(module_name=>'session_info.sql',
action_name=>'test session information');
END;

OCI
To set the four attributes, you use the OCIAttrSet function. The third parameter specifies the value. The fifth
parameter specifies, by means of one of the following constants, which attribute is set:

OCI_ATTR_CLIENT_IDENTIFIER •

OCI_ATTR_CLIENT_INFO

•

OCI_ATTR_MODULE •

OCI_ATTR_ACTION
The following code snippet, which is an excerpt of the session_attributes.c file, shows how to call the
OCIAttrSet function to set the client identifier:

•

text client_id[64] = "helicon.antognini.ch";
OCIAttrSet(ses, // session handle
OCI_HTYPE_SESSION, // type of handle being modified
client_id, // attribute's value
strlen(client_id), // size of the attribute's value
OCI_ATTR_CLIENT_IDENTIFIER, // attribute being set
err); // error handle