Troubleshooting Siebel EIM Performance

Before troubleshooting performance issues that are specific to Siebel EIM, verify that there are no performance bottlenecks on the Siebel Server computer or on the network. This topic contains the following information:

• "Optimizing SQL for Siebel EIM"

• "Using the USE INDEX HINTS and USE ESSENTIAL INDEX HINTS Parameters"

• "Using USE INDEX HINTS and USE ESSENTIAL INDEX HINTS: Example"

• "Using USE INDEX HINTS and USE ESSENTIAL INDEX HINTS: Criteria for Passing Indexes to the Database"

• "Using the SQLPROFILE Parameter"

• "Additional Indexes on Siebel EIM Tables"

• "Creating Proper Statistics on Siebel EIM Tables"

• "Dropping Indexes in Initial Runs of Siebel EIM"

• "Controlling the Size of Batches for Siebel EIM"

• "Controlling the Number of Records in Siebel EIM Tables"

• "Using the USING SYNONYMS Parameter with Siebel EIM"

• "Using the NUM_IFTABLE_LOAD_CUTOFF Extended Parameter with Siebel EIM"

• "Disabling Transaction Logging for Siebel EIM"

• "Disabling Database Triggers for Siebel EIM"

• "Running Siebel EIM Tasks in Parallel"

Optimizing SQL for Siebel EIM

This topic is part of "Troubleshooting Siebel EIM Performance".

During this process, you need to be able to run several similar batches. If you do not have enough data with which to experiment, then you might need to back up and restore the database between runs, so that you can continue processing the same batch.

First, run a Siebel EIM job with the following flag settings: Error flag = 1, SQL flag = 8, and Trace flag = 3. This will produce a log file that contains SQL statements and shows how long each statement took. Identify SQL statements that are taking too long (on a run of 5000 rows in a batch, look for statements that took longer than one minute). These are the statements that you want to concentrate on, and it is recommended that you consult an experienced database administrator at this point. The process of optimizing the SQL for Siebel EIM involves the following:

• Use the respective database vendor's utility or a third-party utility to analyze the long-running SQL statements.

• Based on the review of the data access paths, review the SQL statements for proper index usage. There might be cases where an index is not used at all or the most efficient index is not being chosen. This can require a thorough analysis.

• Based on this analysis, use a systematic approach to tuning these long-running statements. Perform one change at a time and measure the results of each change by comparing them to the initial benchmarks. For example, you might find that dropping a particular index to improve the performance of one long-running statement might negatively impact the performance of other SQL statements.

  Base the decision of whether to drop the index on the impact to the overall process, as opposed to the individual long-running SQL statement. For this reason, it is important that one change be implemented at a time in order to measure the impact of the change.

• After repetitively going through and optimizing each long-running SQL statement, the focus can be shifted to other tuning measures, such as increasing the number of records processed in the EIM table at a time and the running of parallel Siebel EIM tasks.

Using the USE INDEX HINTS and USE ESSENTIAL INDEX HINTS Parameters

This topic is part of "Troubleshooting Siebel EIM Performance".

Perform testing with the IFB file parameters USE INDEX HINTS and USE ESSENTIAL INDEX HINTS, trying both settings (TRUE and FALSE). The default value for USE INDEX HINTS is FALSE. The default value for USE ESSENTIAL INDEX HINTS is TRUE.

Note: If your configuration file has more than one process section, then you must specify USE INDEX HINTS within each one.

If these parameters are set to FALSE, then Siebel EIM does not generate hints during processing. By setting the value to FALSE, you can realize performance gains if the TRUE setting means that hints are being generated that direct the database optimizer to use less than optimal indexes. Siebel EIM processing must be tested with both the TRUE and FALSE settings to determine which one provides better performance for each of the respective Siebel EIM jobs.

Note: The USE INDEX HINTS parameter is applicable only for Oracle Database. The USE ESSENTIAL INDEX HINTS parameter is applicable only for Oracle Database and Microsoft SQL Server.

These two parameters work for different queries, so you have to enable both to get all of the index hints on Oracle Database.

Further information is provided as follows:

• "Using USE INDEX HINTS and USE ESSENTIAL INDEX HINTS: Example"

• "Using USE INDEX HINTS and USE ESSENTIAL INDEX HINTS: Criteria for Passing Indexes to the Database"

Using USE INDEX HINTS and USE ESSENTIAL INDEX HINTS: Example

This topic is part of "Troubleshooting Siebel EIM Performance".

See also:

• "Using the USE INDEX HINTS and USE ESSENTIAL INDEX HINTS Parameters"

• "Using USE INDEX HINTS and USE ESSENTIAL INDEX HINTS: Criteria for Passing Indexes to the Database"

The example in Table 10-2 illustrates the results achieved for an SQL statement with index hints and without index hints. This example was performed on Microsoft SQL Server.

Table 10-2 Example Results for SQL Statement With and Without Index Hints

SQL User Name	CPU	Reads	Writes	Duration	Connection ID	SPID
SADMIN	549625	38844200	141321	626235	516980	9

UPDATE dbo.S_ASSET5_FN_IF
SET T_APPLDCVRG__RID =
(SELECT MIN(BT.ROW_ID)
FROM dbo.S_APPLD_CVRG BT (INDEX = S_APPLD_CVRG_U2)
WHERE (BT.COVERAGE_CD = IT.CVRG_COVERAGE_CD AND
BT.TYPE = IT.CVRG_TYPE AND
BT.ASSET_ID = IT.T_APPLDCVRG_ASSETI AND
(BT.ASSET_CON_ID = IT.T_APPLDCVRG_ASSETC OR
(BT.ASSET_CON_ID IS NULL AND IT.T_APPLDCVRG_ASSETC IS NULL)) AND
(BT.INSITEM_ID = IT.T_APPLDCVRG_INSITE OR
(BT.INSITEM_ID IS NULL AND IT.T_APPLDCVRG_INSITE IS NULL))))
FROM dbo.S_ASSET5_FN_IF IT
WHERE (CVRG_COVERAGE_CD IS NOT NULL AND
CVRG_TYPE IS NOT NULL AND
T_APPLDCVRG_ASSETI IS NOT NULL AND
IF_ROW_BATCH_NUM = 10710001 AND
IF_ROW_STAT_NUM = 0 AND
T_APPLDCVRG__STA = 0)
SET STATISTICS PROFILE ON
GO
SET STATISTICS IO ON
GO
select
(SELECT MIN(BT.ROW_ID)
FROM dbo.S_APPLD_CVRG BT (INDEX = S_APPLD_CVRG_U2)
WHERE (BT.COVERAGE_CD = IT.CVRG_COVERAGE_CD AND
BT.TYPE = IT.CVRG_TYPE AND
BT.ASSET_ID = IT.T_APPLDCVRG_ASSETI AND
(BT.ASSET_CON_ID = IT.T_APPLDCVRG_ASSETC OR
(BT.ASSET_CON_ID IS NULL AND IT.T_APPLDCVRG_ASSETC IS NULL)) AND
(BT.INSITEM_ID = IT.T_APPLDCVRG_INSITE OR
(BT.INSITEM_ID IS NULL AND IT.T_APPLDCVRG_INSITE IS NULL))))
FROM dbo.S_ASSET5_FN_IF IT
WHERE (CVRG_COVERAGE_CD IS NOT NULL AND
CVRG_TYPE IS NOT NULL AND
T_APPLDCVRG_ASSETI IS NOT NULL AND
IF_ROW_BATCH_NUM = 10710001 AND
IF_ROW_STAT_NUM = 0 AND
T_APPLDCVRG__STA = 0)

With Hints

Table 'S_APPLD_CVRG'. Scan count 1, logical reads 394774, physical reads 0, read-ahead reads 280810.

Table 'S_ASSET5_FN_IF'. Scan count 1, logical reads 366, physical reads 0, read-ahead reads 0.

Without Hints

Table 'S_APPLD_CVRG'. Scan count 1268, logical reads 10203, physical reads 697, read-ahead reads 0.

Table 'S_ASSET5_FN_IF'. Scan count 1, logical reads 366, physical reads 0, read-ahead reads 0.

Using USE INDEX HINTS and USE ESSENTIAL INDEX HINTS: Criteria for Passing Indexes to the Database

This topic is part of "Troubleshooting Siebel EIM Performance". It explains how Siebel EIM determines which indexes to include on the hint clause passed to the database when using the USE INDEX HINTS and USE ESSENTIAL INDEX HINTS parameters.

See also:

• "Using the USE INDEX HINTS and USE ESSENTIAL INDEX HINTS Parameters"

• "Using USE INDEX HINTS and USE ESSENTIAL INDEX HINTS: Example"

When determining which indexes to pass on to the database as index hints, Siebel EIM takes the following steps:

1. Before generating a query, Siebel EIM makes a list of columns for which it has determined that an index is needed.

2. Siebel EIM then checks all of the indexes in the repository to find the index with the most matching columns.

Siebel EIM uses the following selection criteria in choosing indexes:

• Unique indexes have priority over nonunique indexes.

• Required columns have priority over nonrequired columns.

If a new index is created and it is declared in the repository, then there is a chance that Siebel EIM will choose it and pass it to the database on a hint.

Using the SQLPROFILE Parameter

This topic is part of "Troubleshooting Siebel EIM Performance".

The inclusion of the SQLPROFILE parameter greatly simplifies the task of identifying the most time-intensive SQL statements. By inserting the following statement in the header section of the IFB file, the most time-intensive SQL statements will be placed in the file:

SQLPROFILE = c:\temp\eimsql.sql

Below is an example of the file eimsql.sql.

Start of the file: list of most time-intensive queries:

EIM: Integration Manager v8.2.2.3 [23021] LANG_INDEPENDENT SQL profile dump (pid 9096).*******************************************************************************Top 100 SQL statements (of 24564) by total time:Batch Step Pass Total Rows Per Row What-------- -------- -------- -------- -------- -------- ------------------------1000 4 106 124.07 0 virtual NULL key1003 4 706 101.68 0 virtual NULL key1002 4 506 93.15 0 virtual NULL key1000 2 101 89.36 10000 0.01 default/fixed column

…list of queries continuesStatistics by step and by pass:

*******************************************************************************Statements per step by total time:Step Stmts Total Min Max Avg %-------- -------- -------- -------- -------- -------- --------9 3405 14727.44 0.00 75.80 4.33 58.104 12011 2854.16 0.01 124.07 0.24 11.262 454 2165.35 0.50 89.36 4.77 8.54

...list of statistics continues...

SQL Statements

*************************************************************************
batch 1000, step 4, pass 106: "virtual NULL key":(total time 2:04m (124s), no rows affected)UPDATE SIEBEL.EIM_CONTACTSET T_CONTACT_BU_ID = ?WHERE (CON_BI IS NULL ANDT_CONTACT_BU_ID IS NULL ANDIF_ROW_BATCH_NUM = ? ANDIF_ROW_STAT_NUM = 0 ANDT_CONTACT__STA = 0)

...list of SQL statements continues...

Additional Indexes on Siebel EIM Tables

This topic is part of "Troubleshooting Siebel EIM Performance".

An examination of the data access path will assist you in determining whether additional indexes are necessary to improve the performance of the long-running SQL. In particular, look for table scans and large index range scans. In the following example, after evaluating the inner loop of the nested select, it was recommended to add an index on all T2 columns.

Inner loop:

(SELECT MIN(ROW_ID)
FROM siebel.EIM_ACCOUNT T2
WHERE (T2.T_ADDR_ORG__EXS = 'Y' AND
T2.T_ADDR_ORG__RID = T1.T_ADDR_ORG__RID AND
T2.IF_ROW_BATCH_NUM = 105 AND
T2.IF_ROW_STAT_NUM = 0 AND
T2.T_ADDR_ORG__STA = 0))

The index was created to consist of T2 columns used in the WHERE clause with ROW_ID at the end of the index. This influenced the database optimizer to choose this index for index-only access. Since the query wants the minimum (ROW_ID), the very first qualifying page in the index will also contain the lowest value.

Caution: All EIM indexes must start with IF_ROW_BATCH_NUM, or else serious performance and contention issues are unavoidable.

Adding Indexes to Improve Performance of S_ORG_EXT Table

The S_ORG_EXT table has indexes on many columns, but not all columns. If you have a large number of records (such as several million accounts) in S_ORG_EXT, then you might get a performance improvement in deleting and merging by adding an index to one or more of the following columns:

PR_BL_OU_ID
PR_PAY_OU_ID
PR_PRTNR_TYPE_ID
PR_SHIP_OU_ID

You can make subqueries to base tables that access only indexes. Performance is enhanced because all related records are physically collocated, and because index leaf pages contain many more records per page than wider base table pages. Before implementing any additional indexes, first discuss this with qualified support personnel.

Creating Proper Statistics on Siebel EIM Tables

This topic is part of "Troubleshooting Siebel EIM Performance".

On IBM DB2, you can use the IFB file parameter UPDATE STATISTICS to control whether Siebel EIM dynamically updates the statistics of EIM tables. The default setting is TRUE. This parameter can be used to create a set of statistics on the EIM tables that you can save and then reapply to subsequent runs. After you have determined this optimal set of statistics, you can turn off the UPDATE STATISTICS parameter in the IFB file (UPDATE STATISTICS = FALSE), thereby saving time during the Siebel EIM runs.

Note: It is recommended to manage statistics manually. Also note that using UPDATE STATISTICS in IFB executes RUNSTATS in SHRLEVEL REFERENCE mode, which causes exclusive locks and can prevent parallel EIM execution.

To determine the optimal set of statistics, you need to run several test batches and RUNSTATS commands with different options to see what produces the best results.

Before and after each test, execute the db2look utility in mimic mode to save the statistics from the database system catalogs. For example, if you are testing Siebel EIM runs using EIM_CONTACT1 in database SIEBELDB, then the following command generates UPDATE STATISTICS commands in the file EIM_CONTACT1_mim.sql:

db2look -m -a -d SIEBELDB -t EIM_CONTACT1 -o EIM_CONTACT1_mim.sql

The file EIM_CONTACT1_mim.sql contains SQL UPDATE statements to update database system catalog tables with the saved statistics. You can experiment with running test Siebel EIM batches after inserting the RUNSTATS commands provided in "IBM DB2 Options". After you find the set of statistics that works best, you can apply that particular mim.sql file to the database. Between runs, save statistics with db2look.

Note: The db2look utility runs on IBM DB2 for Linux, Unix, and Windows (most of the IBM DB2 references in this guide are to this product). On IBM DB2 for z/OS, you can use the Optimization Service Center (OSC) utility instead. For more information about using Siebel EIM with IBM DB2 for z/OS, see "IBM DB2 for z/OS and Siebel EIM".

IBM DB2 Options

The syntax for IBM DB2 commands provides more options, as follows:

• shrlevel change

• allow write access

• allow read access

The clauses allow read access and shrlevel change provide the greatest concurrency.

Dropping Indexes in Initial Runs of Siebel EIM

This topic is part of "Troubleshooting Siebel EIM Performance".

Typically, the Siebel EIM initial load is a very database-intensive process. Each row that is inserted into the base tables requires modifications on the data page and the index pages of all of the affected indexes. However, most of these indexes are never used during a Siebel EIM run. Index maintenance is very time-consuming for most database managers and must be avoided as much as possible.

Therefore, the goal is to identify any indexes that are unnecessary for Siebel EIM and that can be dropped for the durations of the Siebel EIM run. You can create these indexes later in batch mode by using parallel execution strategies available for the respective database platform. Using this approach can save a significant amount of time.

Note: Under normal operations, using parallel execution strategies is not recommended.

• Target Table Indexing Strategy. For a target base table (such as S_ORG_EXT), you only need to use the Primary Index (Px, for example P1) and the Unique Indexes (Ux, for example U1), and then drop the remaining indexes for the duration of the Siebel EIM import. Past experience has determined that the Fx and Mx indexes can be dropped after an extensive SQL analysis of sample Siebel EIM runs.

• Nontarget Table Indexing Strategy. For child tables (such as S_ADDR_ORG), you only need to use the Primary Index (Px), the Unique Indexes (Ux), and the Foreign Key Indexes (needed for setting primary foreign keys in the parent table). Past experience has determined that the Fx and Mx indexes can be dropped after an extensive SQL analysis of sample Siebel EIM runs.

  
  

  Note: Always perform testing when dropping indexes (or adding indexes) to make sure that the expected results are achieved.

  
  

Controlling the Size of Batches for Siebel EIM

This topic is part of "Troubleshooting Siebel EIM Performance".

After tuning the long-running SQL statements, further tests can be run to determine the optimal batch size for each entity to be processed. The correct batch size varies and is influenced by the amount of buffer cache available. Optimal batch ranges have been observed to range anywhere between 500 and 15,000 rows. Run several tests with different batch sizes to determine the size that provides the best rate of Siebel EIM transactions per second. Using the setting Trace Flag = 1 while running Siebel EIM helps in this task because you are then able to see how long each step takes and how many rows were processed by the Siebel EIM process.

Note: Also monitor this throughput rate when determining degradation in parallel runs of Siebel EIM.

Recommended Number of Rows for a Single Batch

For an initial load, you can use 30,000 rows for a large batch. For ongoing loads, you can use 20,000 rows for a large batch. Do not exceed 100,000 rows in a large batch.

Furthermore, for Microsoft SQL Server and Oracle Database environments, limit the number of records in the EIM tables to those that are being processed. For example, if you have determined that the optimal batch size for your implementation is 19,000 rows per batch and you are going to be running eight parallel Siebel EIM processes, then you must have 152,000 rows in the EIM table. Under no circumstances can you have more than 250,000 rows in any single EIM table because this reduces performance. The restrictions mentioned in the preceding example do not apply to IBM DB2 environments. As long as an index is being used to access the EIM tables, the numbers of rows in the EIM tables does not matter in DB2 environments.

Caution: For all supported RDBMS platforms, if indexes are added to EIM tables with any column other than IF_ROW_BATCH_NUM in the first position, then parallel Siebel EIM operations performed on that table will likely fail with index contention issues.

Note: The number of rows that you can load in a single batch can vary depending on your physical computer setup and on which table is being loaded. To reduce demands on resources and improve performance, generally try to vary batch sizes to determine the optimal size for each entity to be processed. In some cases, a smaller batch size can improve performance. But for simpler tables such as S_ASSET, you might find that loads perform better at higher batch sizes than for more complex tables such as S_CONTACT.

Controlling the Number of Records in Siebel EIM Tables

This topic is part of "Troubleshooting Siebel EIM Performance".

Determine the number of records that can reside at one time in an EIM table while still maintaining an acceptable throughput rate during Siebel EIM processing. One observed effect of increasing the number of records in an EIM table is reduced performance of Siebel EIM jobs. This is often caused by object fragmentation or full table scans and large index range scans.

Note: In an IBM DB2 environment, EIM table size is not an important factor that impacts performance, because it is relatively easy to correct table scans and nonmatching index scans. So, a large number of records in an EIM table is not likely to reduce performance in a DB2 environment.

After addressing any object fragmentation and after the long-running SQL statements have been tuned, it is likely that you can increase the number of records that can reside in the EIM tables during Siebel EIM processing. When loading millions of records, this can result in a significant time savings because it reduces the number of times that the EIM table needs to be staged with a new data set.

When performing large data loads (millions of records) it is recommended that you perform initial load tests with fewer records in the EIM table. For example, while identifying and tuning the long-running SQL, you might start with approximately 50,000 records. After tuning efforts are complete, run additional tests, while gradually increasing the number of records. For example, you can incrementally increase the number of records to 100,000, then 200,000, and so on, until you have determined the optimal number of records to load.

Using the USING SYNONYMS Parameter with Siebel EIM

This topic is part of "Troubleshooting Siebel EIM Performance".

The USING SYNONYMS parameter controls the queries of account synonyms during import processing. This parameter is also related to the S_ORG_SYN table. When set to FALSE, this parameter saves processing time because queries that look up synonyms are not used. The default setting is TRUE. Set this parameter to FALSE only when account synonyms are not needed.

Using the NUM_IFTABLE_LOAD_CUTOFF Extended Parameter with Siebel EIM

This topic is part of "Troubleshooting Siebel EIM Performance".

Setting the NUM_IFTABLE_LOAD_CUTOFF extended parameter to a positive value will reduce the amount of time taken by Siebel EIM to load repository information. This is because when you set this parameter to a positive value, only information for the required EIM tables is loaded. For more information about this parameter, see Siebel Enterprise Integration Manager Administration Guide.

Note: While this parameter is especially important for merge processes, it can also be used for any of the other types of processes.

Here is an example of using this parameter while running on Microsoft Windows from the server command line mode:

run task for comp eim server siebserver with config=account2.ifb, ExtendedParams="NUM_IFTABLE_LOAD_CUTOFF=1", traceflags=1

Disabling Transaction Logging for Siebel EIM

This topic is part of "Troubleshooting Siebel EIM Performance".

Typically, you set the system preference that disables transaction logging only for the initial data load. This setting (in the Administration - Siebel Remote screen, and Remote System Preferences view) is a check box labeled Enable Transaction Logging. This setting specifies whether or not the Siebel application logs transactions for the purpose of routing data to Siebel Mobile Web Clients.

By default, transaction logging is enabled. If there are no Siebel Mobile Web Clients, then you can disable this system preference. If you have Siebel Mobile Web Clients, then you must enable this system preference in order to route transactions to the Siebel Mobile Web Clients. However, during initial data loads, you can disable this system preference to reduce transaction activity to the Siebel docking tables. After the initial loads are complete, enable transaction logging again.

Note: For incremental data loads, transaction logging must be enabled if there are mobile clients. If this setting is changed for incremental data loads, then you will need to perform a reextract of all of the mobile clients.

Disabling Database Triggers for Siebel EIM

This topic is part of "Troubleshooting Siebel EIM Performance".

Disabling database triggers, by removing them through the Administration - Server screens, can also help improve the throughput rate. This can be done by running the Generate Triggers server task with both the REMOVE and EXEC parameters set to TRUE. Be aware that components such as Workflow Policies and Assignment Manager will not function for the new or updated data. Also, remember to reapply the triggers after completing the Siebel EIM load.

Running Siebel EIM Tasks in Parallel

This topic is part of "Troubleshooting Siebel EIM Performance".

Running Siebel EIM tasks in parallel is the last strategy that you might want to adopt in order to increase the Siebel EIM throughput rate. In other words, do not try this until all long-running SQL statements have been tuned, the optimal batch size has been determined, the optimal number of records to be processed at a time in the EIM table has been determined, and the database has been appropriately tuned. Before running tasks in parallel, check the value of the Maximum Tasks parameter. This parameter specifies the maximum number of running tasks that can be run at a time for a service. For more information about this parameter, see Siebel System Administration Guide.

Note: UPDATE STATISTICS must be set to FALSE in the IFB file when running parallel Siebel EIM tasks on IBM DB2 for z/OS. Failure to do so can cause Siebel EIM tasks and executing RUNSTATS to take a longer time to complete. Also, when running parallel Siebel EIM tasks, deadlock and timeout will occur if UPDATE STATISTICS is set to TRUE in the IFB file.