Siebel Performance Tuning Guide > Tuning Customer Configurations > Analyzing Generated SQL for Performance Issues >

Troubleshooting Poor Performing SQL at the Database Level Using Workload Tagging

This topic is part of Analyzing Generated SQL for Performance Issues.

Siebel workload tagging is a diagnostic tool that tags SQL statements generated and executed by Siebel Business Applications running on an Oracle database. This tool helps database administrators trace and troubleshoot poor performing SQL or excessive workload at the database level.

NOTE:  Siebel workload tagging is available only for Siebel Business Applications running on an Oracle database.

When workload tagging is enabled, specified Siebel Application Object Manager-generated SQL statements (SELECT, INSERT, UPDATE, DELETE) are tagged with workload tagging attributes. The values for these attributes include:

• CLIENT_IDENTIFIER: component_name,servername,taskID,userID,FlowID:SarmID
• ACTION: View Name
• MODULE: Business Component Name

NOTE:  The length of the previous attribute values is limited to 64 bytes for CLIENT_IDENTIFIER, 32 bytes for ACTION, and 48 bytes for MODULE. Truncation occurs when the length exceeds these limitations.

When poor performing SQL statements are suspected, the database administrator can use this tagging information to trace details about the generated SQL and the user that initiated it. Database administrators can then use Oracle Enterprise Manager to find the component task and log detail for more in-depth analysis of the performance issue. Alternatively, you can use SQL Plus or similar tools to query the Oracle Database V$SESSION view to look up the CLIENT_IDENTIFIER, ACTION, and MODULE attributes by using SQL_ID of the SQL statement.

About Enabling and Disabling Workload Tagging

By default, workload tagging is disabled. You choose which SQL statements you want enabled for workload tagging by setting the log level of the OCI SQL Tagging event type for the applicable Siebel Application Object Manager server component as described in Table 11.

NOTE:  The OCI SQL Tagging event type is available to all object manager-based components for supported languages, such as Call Center Object Manager (ENU), Sales Object Manager (DEU), eService Object Manager (FRA), and so on.

You can use either of the following ways to enable and disable workload tagging:

• Enabling and Disabling Workload Tagging Using the Siebel Application
• Enabling and Disabling Workload Tagging Using the Siebel Server Manager

Requirements for Enabling and Disabling Workload Tagging

Before enabling (or disabling) workload tagging, make sure the following requirements are met:

• Siebel Server is up and running.
• Oracle Enterprise Manager is configured for the database server. For information about Oracle Enterprise Manager, see the documentation on Oracle Technology Network at

  http://www.oracle.com/technetwork/indexes/documentation/index.html

Enabling and Disabling Workload Tagging Using the Siebel Application

Use the following procedure to set the log level of the OCI SQL Tagging event type for the applicable Siebel Application Object Manager server component using the Siebel application GUI.

To enable and disable workload tagging using the Siebel application

1. Navigate to the Administration - Server Configuration screen, then the Components view.
2. Select the appropriate Siebel Application Object Manager for which you want to enable workload tagging. For example, if the application you are using is Siebel Call Center for English, then select Call Center Object Manager (ENU).
3. Click the Events subview, and then select the OCI SQL Tagging event type.
4. Set the Log Level as described in Table 11.

After workload tagging is enabled, database administrators can use Oracle Enterprise Manager to diagnose and troubleshoot the problematic generated SQL.

Related Topics

Enabling and Disabling Workload Tagging Using the Siebel Application

Enabling and Disabling Workload Tagging Using the Siebel Server Manager

Using Workload Tagging to Troubleshoot Poor Performing SQL at the Database Level

Enabling and Disabling Workload Tagging Using the Siebel Server Manager

Use the Siebel Server Manager (srvrmgr program) to set the OCI SQL Tagging event type for the applicable Siebel Application Object Manager server component by way of the command-line interface. For more information about Siebel Server Manager, see Siebel System Administration Guide.

To enable and disable workload tagging using the Siebel Server Manager

1. Make sure the Siebel Application Object Manager server component for your Siebel application is running.
2. Start the srvrmgr program.

   For information on starting the srvrmgr program, see Siebel System Administration Guide.

3. Do one of the following:
   • To enable workload tagging, run the following command:

   change evtloglvl OCISqlTag = loglevel for comp appobjmgr_lang

   where:

   • loglevel determines the types of SQL statements that are tagged (see Table 11).
   • appobjmgr is the Siebel Application Object Manager server component for your application, and lang is the three-letter identifier for the language specific to your environment.

     For example, the following command enables workload tagging only for SELECT statements for Siebel Call Center for English:

   change evtloglvl OCISqlTag = 2 for comp sccobjmgr_enu

   • To disable workload tagging, run the following command:

   change evtloglvl OCISqlTag = 0 for comp appobjmgr_lang

   where:

   • appobjmgr is the Siebel Application Object Manager server component for your application, and lang is the three-letter identifier for the language specific to your environment.

After workload tagging is enabled, database administrators can then use Oracle Enterprise Manager to diagnose and troubleshoot problematic generated SQL. For information on how to use Oracle Enterprise Manager for workload tagging, see Using Workload Tagging to Troubleshoot Poor Performing SQL at the Database Level.

Using Workload Tagging to Troubleshoot Poor Performing SQL at the Database Level

Use the following procedure to diagnose and troubleshoot poor performing SQL at the database level using Oracle Enterprise Manager. For more information about Oracle Enterprise Manager, see the documentation on Oracle Technology Network.

To use workload tagging to troubleshoot poor performing SQL at the database level

1. Make sure workload tagging is enabled.
2. Log in to Oracle Enterprise Manager.
3. Navigate to the Database Control Top Activity page.
4. From the Top Sessions section, select the Top Sessions view, and then drill down on the Session ID you want to troubleshoot.
5. Click the General tab.
6. In the Client section, note the values in the Current Client ID field.

   The format for this field is:

   component_name,servername,taskID,userID,FlowID:SarmID

   where:

   • component_name is the server component alias, for example SCCObjMgr_enu.
   • servername is the name of the Siebel Server from which the SQL originated.
   • taskID is the task ID of the user who generated the query.
   • userID is the login name of the user who generated the query.
   • FlowID is the flow ID of the task.
   • SarmID is the SARM ID of the task.

     For example:

   SCCObjMgr_enu,server1,10485770,SADMIN,000008814d7f1fe8:163

7. In the Application section, drill down on the following fields for more details:
   • Current Module is the business component associated with the SQL.
   • Current Action is the view name associated with the SQL.