Siebel Marketing Guide > Troubleshooting in Siebel Marketing > Other Troubleshooting Issues in Siebel Marketing >

Improving Snapshot Performance

If generating a snapshot takes longer than it should, you can use information in the snapshot log file and monitor the system (using Performance Monitor on NT) to collect information about performance. Performance variables that affect snapshot generation include the following:

The resolution sections (How to resolve) in this topic contain some recommended guidelines to improve performance. Some of these recommendations may improve performance more than others. For more information about Siebel Marketing internal processes that execute when you request snapshot generation, see Understanding Snapshot Generation Internal Processes.

Possible Cause 1

Too much time used by the rcontrol process. Time used by the rcontrol process should be a few seconds to a few minutes and depends on the complexity of the snapshot and the size of attribute tables. This can be caused by using nested loop joins.

How to diagnose 1. In the Marketing snapshot log file, compute the time between the comment line Application Name - Compute Snapshot and Application Name - Control Snapshot. This process should not take more than a few minutes unless there is an internal issue. Contact Siebel Technical Support if this takes too long.

How to resolve 1. Siebel strongly recommends avoiding nested loop joins which may lead to a major performance slow down based on table sizes and indexes. For more information about nested loop joins, see Understanding Joins.

Possible Cause 2

Too much time used by data sorting and caching. The time used should be a few minutes to an hour and depends on the size of cache tables, the size of merge tables, database sorting time, and network speed.

How to diagnose 2. In the marketing snapshot log file, compute the time between the comment line Joining Records and Application Name - Compute Snapshot.

How to resolve 2. Speed up network connectivity between database server and Marketing Server. Make sure that network connectivity is optimized between the data source server and the Marketing Server. Contact your systems administrator for assistance.

Possible Cause 3

Too many records for cache tables. The approximate number of record for each table should be fewer than 1,000,000 records.

How to diagnose 3. In the snapshot log file, note all the cursors that use Cache as the joining method. In the Cursor Summary, note the number of records read from each cache join cursor.

How to resolve 3. Speed up network connectivity between Marketing Server and Siebel file system. Make sure network connectivity is optimized between the Marketing Server and Siebel file system. Contact your systems administrator for assistance.

Possible Cause 4

More memory needed for cached tables. The approximate amount of memory needed for each cached table is less than 300 MB.

How to diagnose 4. For each cache cursor, compute the memory usage. Memory requirements can be estimated by using the bytes per record value from the SQL SELECT statement for the table produced by the application server during a data extraction task. Multiply the bytes per record by the number of records in the table to be cached. The bytes needed for each selected field is determined by the value in the Width field in the Fields View.

How to resolve 4. Avoid caching large tables. You can ignore small cache tables (those with fewer than 10,000 records) unless the number of columns selected is unusually large (more than 20). Caching large tables (over 2,000,000 records or over 500 MB) may impact performance. If possible, change joins from cache to merge for large tables to save memory and potentially speed up processing. For more information about join types, see Understanding Joins

Possible Cause 5

Number of nested loop joins.To avoid performance issues, nested loop joins should be avoided on large tables. For more information about nested loop joins, see Understanding Joins.

How to diagnose 5. In the marketing snapshot log file, identify cursors that use Nested Loop as the join method.

How to resolve 5. Use filters to reduce snapshot size. Depending on the filter criteria, filters may generate SQL WHERE clauses, reducing data shipping between the Siebel database and the Marketing Server. This can also improve snapshot performance. For more information about filters, see Creating Filters and Defining Filter Criteria.

Possible Cause 6

Slow processing of denormalized records. The rate of denormalized records processed should be 10,000 records per second. In an ideal setup, the Marketing Server can process 20,000 to 30,000 records per second.

How to diagnose 6. Check the value in the Records Processed comment line and divide that number by the time difference between the Records Processed comment line and the Joining Records comment line.

How to resolve 6. Pass database hints to speed up sorts. The Hint field can be used to pass hints to SQL select statements that are generated by the Marketing Server during a snapshot build or data synchronization. This field can only be used on Oracle data sources. The hint is applied to the cursor opened on the child table. Typically parallel hints (/*+ parallel(<tablename>, <degree of parallelism> */) are setup for improved performance on data sorts. Hints can be also setup on start point joins. Refer to section 3.19 on how to setup and pass hints in SQL queries. Contact your systems administrator for assistance.

Possible Cause 7

CPU usage during data processing. Usage should be 60-80% on two CPUs.

How to diagnose 7. Monitor CPU usage on the Marketing Server for the rextract and rcompute processes during data processing. The Marketing Server is processing data when the log file contains a Joining Records comment line.

How to resolve 7. Use RDBMS joins to reduce data shipping between database and Marketing Servers. Using RDBMS joins will improve snapshot performance because of the reduction in data transfer. RDBMS joins will only improve performance if there is heavy use of filters and if the filters actually generate SQL WHERE clauses. Contact your systems administrator for assistance.

Possible Cause 8

Memory usage during data processing. Memory usage should be 500-600 MB.

How to diagnose 8. Monitor memory usage on the Marketing Server for the rextract and rcompute processes during data processing. The Marketing Server is processing data when the log file contains a Joining Records comment line.

How to resolve 8. Partition large tables. Partitioning large fact tables will increase data read throughput. This should be considered only if sorting one large table is an issue or it is determined that there is a major bottleneck reading large tables in the rextract process. Contact your systems administrator for assistance.

Possible Cause 9

Average CPU usage by rextract process. Average CPU usage should be 60-80% on 1 CPU.

How to diagnose 9. Monitor CPU usage on the Marketing Server for the rextract process during data processing. The Marketing Server is processing data when the log file contains a Joining Records comment line.

How to resolve 9. Consolidate multiple campaigns into a single snapshot by bundling multiple campaigns into a single snapshot. This may reduce overall processing time for the campaigns because you are generating a single snapshot instead of multiple snapshots.

Possible Cause 10

Average CPU usage of rcompute process. Average usage should be 30-50% of 1 CPU.

How to diagnose 10. Monitor CPU usage on the Marketing Server for the rcompute process during data processing. The Marketing Server is processing data when the log file contains a Joining Records comment line.

How to resolve 10. Contact Siebel Technical Support for assistance.

Possible Cause 11

Data transfer rate between data source server and Marketing Server. The transfer rate should be 8-10 MB per second.

How to diagnose 11. Transfer a relatively large file (around 500MB) from the data source server to the Marketing Server during the time of the day that snapshots are typically run.

How to resolve 11. Contact your systems administrator for assistance.

Possible Cause 12

Data transfer rate between the Marketing Server and the Siebel file system. The Transfer rate should be 8-10 MB per second.

How to diagnose 12. Transfer a relatively large file (around 500MB) from the Marketing Server to the Siebel file system during the time of the day that snapshots are typically run.

How to resolve 12. Contact your systems administrator for assistance.

Possible Cause 13

Too much time used to count the snapshot. The count rate should be 200,000 to 400,000 records per minute.

How to diagnose 13. In the marketing snapshot file, subtract the time stamp between the last log record and the Application Name - Counter Allocate comment line.

How to resolve 13. Contact Siebel Technical Support for assistance.


 Siebel Marketing Guide 
 Published: 23 June 2003