| Siebel CRM Performance Tuning Guide Siebel 2018 E24801-01 |
|
 Previous |
 Next |
View PDF |
| Siebel CRM Performance Tuning Guide Siebel 2018 E24801-01 |
|
Previous |
Next |
View PDF |
Performance troubleshooting is an iterative process. You need to consider performance implications during design and development. Note any changes to potentially troublesome areas, such as MVGs, business component sort and search specifications, joins, extension tables, or indexes. You then test the application to determine bottlenecks, using realistic data volumes and distribution in your test environment. Focus your testing efforts on the slowest, most important, and most highly configured views.
If a performance problem is detected in testing or production, then your next step is to analyze the SQL statements being generated by Siebel Business Applications. This is one of the most useful diagnostic tools available to you for performance analysis.
This topic contains the following information:
"About Specifying SQL Logging and SQL Tagging for Siebel Application Object Manager Components"
"Troubleshooting Poor Performing SQL at the Database Level Using Workload Tagging"
This topic is part of "Analyzing Generated SQL for Performance Issues".
SQL logging and SQL tagging are diagnostic tools that log and tag SQL statements generated and executed by Siebel Business Applications. When used in conjunction with SQL logging, SQL tagging can help administrators trace long-running or slow-performing SQL queries back to the user or action that triggered them. You can also use these tools to review generated SQL in a development or test environment after making configuration changes in Siebel Tools.
|
Note: SQL tagging must be used in development or test environments only. It is strongly recommended not to use this diagnostic feature in the production environment, because it generates a large number of log entries, which could impact performance negatively and lead to running out of disk space on the Siebel Server. |
SQL tagging must be used only during development and test. It is strongly recommended not to use this diagnostic feature in production environments, because it generates a large number of entries into logs and might impact performance negatively and run out of disk space on the Siebel Server
SQL logging controls the level of SQL logging detail in a log file for an object manager-based component. To set SQL logging for a component, set the Object Manager SQL Log (alias ObjMgrSqlLog) event to an appropriate value. For example, specify the event value for a Siebel Application Object Manager component such as Call Center Object Manager (ENU).
SQL tagging controls whether or not SELECT statements are tagged for an object manager-based component. SQL tagging also controls whether or not to log additional information, such as the name of a business component. SQL tagging is primarily used during the development phase. To set SQL tagging for a component, set the OM SQL Tagging (alias ObjMgrSqlTag) event to an appropriate value. By default, SQL tagging is disabled.
|
Caution: SQL tagging changes the format and content of the SQL SELECT statement by adding a bind, which inhibits the use of Oracle stored outlines, if they exist. |
With SQL tagging, data that can be logged includes the server component name, server name, task ID, user ID, flow ID (with Siebel ARM ID), business object name, business component name, and view name. Which elements are logged depends on the level set for the event.
For instructions on how to use SQL tagging and logging to log and tag generated SQL statements at the server component level, see Siebel System Monitoring and Diagnostics Guide.
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 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 applications running on 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 the following:
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.
This topic contains the following information:
"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"
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. The log levels are listed in Table 12-1.
Table 12-1 Log Levels for Enabling and Disabling Siebel Workload Tagging
| Log Level | Description |
|---|---|
|
0 |
Workload tagging is disabled. |
|
1 |
Workload tagging is disabled. This is the default setting. |
|
2 |
Workload tagging is enabled only for |
|
3 |
Workload tagging is enabled only for |
|
4 |
Workload tagging is enabled for |
|
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 methods to enable and disable workload tagging:
Before enabling (or disabling) workload tagging, make sure that 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.
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
Navigate to the Administration - Server Configuration screen, then the Components view.
Select the appropriate Siebel Application Object Manager for which you want to enable workload tagging. For example, if the application that you are using is Siebel Call Center for U.S. English, then select Call Center Object Manager (ENU).
Click the Events subview, and then select the OCI SQL Tagging event type.
Set the Log Level as described in Table 12-1.
After workload tagging is enabled, database administrators can use Oracle Enterprise Manager to diagnose and troubleshoot the problematic generated SQL.
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
Make sure that the Siebel Application Object Manager server component for your Siebel application is running.
Start the srvrmgr program.
For information about starting the srvrmgr program, see Siebel System Administration Guide.
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 12-1).
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 U.S. 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
In this command, 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 about how to use Oracle Enterprise Manager for workload tagging, see "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
Make sure that workload tagging is enabled.
Log in to Oracle Enterprise Manager.
Navigate to the Database Control Top Activity page.
From the Top Sessions section, select the Top Sessions view, and then drill down on the Session ID that you want to troubleshoot.
Click the General tab.
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
This topic is part of "Analyzing Generated SQL for Performance Issues".
Optionally, after making configuration changes in Siebel Tools, you can spool the SQL that is generated by the Siebel application during runtime. You do this to troubleshoot configuration-related performance issues.
To spool the generated SQL into a trace file, start the Siebel application in the Siebel Developer Web Client (connecting to the Siebel database) using the command-line option /s sql_trace_file. For more information about installing and running the Siebel Developer Web Client, see the Siebel Installation Guide for the operating system you are using.
The SQL trace file contains all of the unique SQL statements generated during the current session, and identifies the amount of time spent processing each one. The trace file can be opened in a text editor for examination after the session has ended. The SQL trace file, which is simply a text file holding the spooled SQL from the session, is overwritten during every new session.
You can specify the /s sql_trace_file option by modifying properties for the Start menu item or desktop shortcut from which the Siebel application is invoked. The following example shows a command line for spooling generated SQL from Siebel Call Center using the Siebel Developer Web Client:
"D:\Siebel\Client\bin\siebel.exe /c D:\Siebel\Client\bin\enu\uagent.cfg /s siebel_sql.txt"
If you do not specify a path, then the SQL trace file is created in the Siebel client root bin directory, such as D:\Siebel\Client\bin.
You can programmatically start and stop SQL spooling though the Siebel Object Interfaces by using the TraceOn and TraceOff methods on the Application object. For more information about these methods, see Siebel Object Interfaces Reference.
This topic is part of "Analyzing Generated SQL for Performance Issues".
As described, you can generate SQL trace files related to your configuration changes, such as for a particular view you have configured. Analyze the contents of the SQL trace file to identify any possible performance issues.
As you look through the SQL trace file, be aware of factors such as:
The number and complexity of SQL statements.
Execution times for SQL statements. This is the SQL execution time plus the time it takes to return rows. It does not include time for client-side processing.
Selection criteria in the WHERE clauses, indicating search specifications.
Sorting criteria in the ORDER BY clauses, indicating sort specifications. (In general, it is better for a query to first filter data using WHERE clauses, in order to reduce the volume of data to be then sorted. Applying sorting criteria that match users' needs reduces the likelihood of users performing their own sort operations, which would require additional system resources.)
The use of joins.
|
Note: If the same SQL statement is executed repeatedly, the Siebel application displays the entire statement for the first query. For each subsequent iteration of the same query, only the bind variables are displayed. You can recognize a query that is repeated by the specific set of bind variables it uses. |
SQL statements are displayed for all queries, including housekeeping queries. These are queries that are necessary for system operation, such as looking up the user's login to obtain responsibilities, and determining today's alarms in the calendar. You will also see queries to the S_LST_OF_VAL table to populate picklists. Queries that populate views are also present in the SQL trace file, and are easily distinguishable based on the tables they access.
This topic is part of "Analyzing Generated SQL for Performance Issues".
If you identify a problematic query in the SQL trace file, then you can obtain more information about it by using the database query tool provided with the RDBMS, such as SQL*Plus for Oracle Database.
Copy and paste the SQL statement from the trace file into the database query tool, execute the query against the Siebel database, then generate a query plan. A query plan is a detailed reporting of various statistics about the query you executed. For an example of generating a query plan against the local database, see "Example of Obtaining Query Plan".
Use query plans to check:
The use of indexes
The use of temporary tables
The use of sequential table scans
Finally, compare your results with a standard application (that is, not custom-configured) in order to identify any potentially slow queries. You can resolve many performance issues either by modifying search specifications or sort specifications, or by creating new indexes on the base table.
|
Caution: Only specially trained Oracle personnel can modify existing Siebel indexes. This restriction is enforced so that performance in other modules (such as Siebel EIM) is not adversely affected by any index modifications that you make to improve query performance through the user interface. For more information, see "Managing Database Indexes in Sorting and Searching". |
Consider any potential performance implications before modifying search specification and sort specification properties for a business component. By spooling out the SQL into trace files, you can analyze which indexes are likely to be used when your application queries the business component through each applet.
Run your query plans against datasets that are comparable to the production dataset. For example, you will not obtain useful results analyzing the performance of a query against a 30-record test dataset when the production database has 200,000 records.
You might find it useful to prioritize the views to examine, as follows:
First priority. Views that are known to have the biggest performance bottlenecks.
Second priority. Views that are accessed most frequently.
Third priority. Views that are the most highly configured (as compared to the standard Siebel application).
Comparison with the standard Siebel application provides you with a benchmark for evaluation. It is often very useful to obtain a trace file from the standard Siebel application, following a preselected route through the views. Then you obtain a separate trace file from the custom-configured application, following the same route as closely as possible. The two trace files are compared, noting differences in the bullet items listed previously.
Bind variables are the values that determine which records are brought back. The RDBMS substitutes the value of a bind variable into an SQL statement when the same SQL statement is being reused, generally in place of each occurrence of a question mark or series of question marks. For example, a business object bind variable is used in an S_APP_QUERY statement because the purpose of this statement is to open the business object.
Watch for the following indications of potential problems:
Unnecessary fields are being accessed, especially ones not exposed in the user interface and not needed for calculated fields, nor used for passing values to detail records.
Unnecessary joins are occurring, particularly to tables that are not being accessed.
Unnecessary multiple joins are being made to the same table. This can indicate duplicate join or Multi Value Link (MVL) object definitions, or joins using the same foreign key.
Multiple short queries similar to the following:
...FROM
SIEBEL.S_ADDR_PER T1
When a short query appears many times, this generally indicates that an MVG without a primary join is being accessed by a list applet. The system is running a secondary query for each master record to obtain its detail records. The secondary queries are the short queries appearing in the log file. This is usually your best diagnostic indicator of the need for a primary join.
When a short query appears only once, it indicates the same situation, but accessed in a form applet. In either case, the cure is a primary join, as explained in "Using Primary ID Fields to Improve Performance".
This topic is part of "Analyzing Generated SQL for Performance Issues".
The following procedure shows an example of obtaining a query plan when running against a local database using the Siebel Mobile Web Client. It is assumed that customers will obtain query plans using the RDBMS for their enterprise database, such as Oracle Database, IBM DB2, or Microsoft SQL Server.
To obtain a query plan for an SQL statement in your trace file
Log in to the local database, as described in Siebel Remote and Replication Manager Administration Guide.
In order to analyze an SQL statement from the SQL trace file, copy the SQL statement and paste it into the database query tool you are using.
Replace bind variable references with the corresponding bind variable values.
Execute the query.
The query runs against the local database. The Statistics pane provides analysis information.
This topic is part of "Analyzing Generated SQL for Performance Issues".
The database that underlies Siebel Business Applications can be queried to obtain information on a read-only basis.
|
Caution: Update queries must never be directly performed on the Siebel database. All data manipulation and restructuring must be performed through Siebel Tools or through the Siebel application. |
