Troubleshooting Transaction Router Performance Issues

Symptoms or Error Messages with Transaction Router Performance Issues

Symptoms of a Transaction Router performance issue might include:

Mobile or connected users working on a regional node report that they're not seeing up-to-date data.
There are numerous .dx files in the SIEBEL_ROOT\Siebsrvr\Docking\txnproc folder.
Increased memory usage of the Transaction Router process could eventually cause it to error.

Causes of Transaction Router Performance Issues

Here are three main causes of clear Transaction Router performance problems:

Visibility-related transactions
These are most commonly created by position changes made in the UI or EIM. They can also be caused by running Generate Reporting Relationships.
Docking rules and data distribution
Slow running queries

Diagnostic Steps for Transaction Router Performance Issues

Monitoring Performance

The first step in diagnosing a Transaction Router performance issue is to identify the severity of the problem. Decide if transactions are just being routed slowly, or if mobile users and regional nodes are receiving no transactions at all.

To monitor Transaction Router performance, run the following SQL statement every 30 minutes on the Siebel server. This SQL statement provides, most importantly, the number of outstanding transactions to be routed to each node, along with the time when a particular node was last routed to:

SELECT NAME NODE, LAST_TXN_NUM, A.MAX_TXN_ID - LR.LAST_TXN_NUM TXNSTOROUTE, TO_CHAR (LR.LAST_UPD, 'DD-MON-YY HH24:MI:SS') LASTROUTED
FROM S_NODE N, S_DOCK_STATUS LR,
(SELECT MAX(TXN_ID) MAX_TXN_ID FROM S_DOCK_TXN_LOG) A
WHERE N.EFF_END_DT IS NULL
AND LR.NODE_ID = N.ROW_ID AND LR.TYPE = 'ROUTE' AND
LR.LOCAL_FLG = 'Y' AND LR.LAST_TXN_NUM != -1
ORDER BY LR.LAST_TXN_NUM;
For MS SQL Server and DB2
SELECT NAME NODE, LAST_TXN_NUM,
A.MAX_TXN_ID - LR.LAST_TXN_NUM TXNSTOROUTE,
LR.LAST_UPD LASTROUTED
FROM S_NODE N,
S_DOCK_STATUS LR,
(SELECT MAX(TXN_ID) MAX_TXN_ID FROM S_DOCK_TXN_LOG) A
WHERE N.EFF_END_DT IS NULL
AND LR.NODE_ID = N.ROW_ID AND LR.TYPE = 'ROUTE' AND LR.LOCAL_FLG = 'Y' AND LR.LAST_TXN_NUM != -1
ORDER BY LR.LAST_TXN_NUM

Review the results returned by the SQL statement, along with all the Transaction Router log files. In Siebel applications version 6.x, 7.x and 8.x, the log files are TxnRoute*.log. In Siebel applications version 5.x, they're TxnRoute*.trc.

Examples of the Diagnostic Information in Transaction Router Performance Issues

Here are examples of the diagnostic information that can be gathered to help in identifying the causes listed above:

Visibility-Related Transactions

If the cause is visibility-related transactions, the Transaction Router log files usually won't contain any errors. Mobile users will be receiving transactions, but most likely at a reduced rate, for example:

2002-02-07 15:44:48 Client: DALEYJ, Read 49 files (102448-102496), Wrote 3 files (71905-71907)
2002-02-07 15:44:48 Txns Read: 16505, Opers Read: 18290, Vis Opers Written: 532
2002-02-07 15:44:48 Found 5994 Vis Event Opers, Found 4188 Vis Rel Event Opers
2002-02-07 16:13:55 2002-02-07 16:13:55 Client: COWANC, Read 0 files (102448-102447)
This log file shows this particular Transaction Router task processing 49 .dx files (00102448.dx to 00102496.dx) in the SIEBEL_ROOT\Siebsrvr\Docking\txnproc folder for one node named DALEYJ. It took nearly 30 minutes to write three .dx files to this node's \outbox directory. Note that there several Visibility Related Event Operations (4188) that cause the Transaction Router to perform additional visibility checks against the database and can subsequently cause additional data downloads to the node.

The next step is to check the contents of the .dx files being read by the Transaction Router using txnutl. In the above example, you'd check files 00102448.dx to 00102496.dx. For more instructions on using this utility, see Document KB562098. Once the .dx files have been translated with txnutl, check the contents for insert, update, and delete transactions on the following tables:

S_NODE_REL
S_NODE_EMP
S_POSTN_RPT_REL
S_POSTN
S_EMP_POSTN
S_SHARED_EMP
S_ORG_INT

Transactions on these tables can significantly affect Transaction Router performance -- this is expected behavior. Note that transactions on other tables might cause the Transaction Router to perform additional visibility checks against the database, but these tables are known to have a significant impact on performance.

Docking Rules and Data Distribution

If the cause is docking rules and data distribution, the Transaction Router log files might appear to hang at Iteration 0 where the last entry in the Transaction Router log files details the .dx file and transaction being processed, for example:

2002-02-01 18:12:57 ***** Iter: 0
2002-02-01 18:12:57 TxnProc lroute: file 3321, txn id 7264908

The Transaction Router might then continue to process without errors or might fail with error messages including:

SBL-CSC-00106: Invalid file header expecting Siebel Tall/Skinny File
SBL-CSC-00120: Not enough space in buffer cache
SBL-CSC-00200: Cannot acquire spinlock hashWrite
SBL-CSC-00301: Invalid value 0 for argument pVisDataInfo->createLatch

These error messages indicate that that the shared visibility database visdata.dbf is corrupt. If there are multiple Transaction Router tasks running on the Siebel server, all the log files should be reviewed for the above errors, because it's most likely that only one of them will contain the above error, while the others will contain the following:

SBL-CSC-00213: Invalid visibility database. Shutdown and restart all routers to rebuild.

If these above symptoms are seen, it's likely that mobile users and regional nodes will be receiving very few or no transactions. Docking rules and data distribution are the most likely cause. To confirm this, gather the following:

Start a single Transaction Router task and have a DBA perform an RDBMS trace on the Transaction Router task
Run the visrule script for the appropriate version of Siebel against the server database, following the directions below appropriate to your version of Siebel applications.

For Siebel 7 and 8: For Siebel 7 and 8 you can use a utility, visrulediag, to generate these scripts automatically. To generate a script using this utility, open a command prompt and navigate to the SIEBEL_ROOT\SiebSrvr\bin directory. Run siebenv set to set the environment parameters and issue the following command:

>visrulediag /U username /P password /C datasource /D tableowner /M G

The /M G specifies to generate a SQL script. By default the threshold value /T will be 1000. At this point the utility will prompt for the dock objects. For this troubleshooting step, enter: >all.

This will generate the script to a file called visrulediag.sql in the SIEBEL_ROOT\SiebSrvr\bin directory. Run visrulediag.sql against the server database and send the results to Technical Support for review.

Slow Running Queries

If the cause is slow running queries, the Transaction Router log files usually won't contain any errors and mobile users will be receiving transactions, but most likely at a reduced rate, for example:

2001-12-11 16:03:17 Client: MENZIEJ2, Read 0 files (18354-18353), Wrote 1 files (7426-7426)
2001-12-11 16:03:17 Txns Read: 24, Opers Read: 1047, Vis Opers Written: 3
2001-12-11 16:03:17 Found 0 Vis Event Opers, Found 0 Vis Rel Event Opers
2001-12-11 16:08:18 Client: COLLIEJ2, Read 0 files (18354-18353), Wrote 1 files (5750-5750)
This shows this particular Transaction Router task processing two .dx files (0018354.dx to 0018353.dx) in the SIEBEL_ROOT\Siebsrvr\Docking\txnproc folder, for one node named MENZIEJ2. It took over 5 minutes to write one .dx file to this node's \outbox directory. Note there are no Visibility Related Event Operations, so this indicates that there might be a performance issue with a query being executed by the Transaction Router.

Queries can run slowly for many reasons including missing or corrupt indexes, table fragmentation, insufficient resources, and underlying RDBMS problems.

To investigate further, start a single Transaction Router task and have a DBA perform an RDBMS trace on the Transaction Router task. Review the RDBMS trace to decide if there are any long running queries that could be impacting the Transaction Router's performance, for example:

Be suspicious of long running queries that return few rows instead of long running queries that return many rows.
If there are several long running queries, be suspicious if all these queries involve a common table.

Solutions to Transaction Router Performance Issues

To improve Transaction Router performance, there are some initial steps that you should take:

First, check that the paging file size on all Siebel server machines is 1.5 times the amount of physical RAM available. Also, monitor the CPU and memory usage on the Siebel server and database server machines for high utilization of either resource.
Second, set the following Component parameters at the Transaction Router Component level:
Id Db Size: 409600
Node Division Factor: X
Id Db Size specifies the size of the shared visibility database. The maximum is 409600. Don't increase the size of Id Db Size above 409600. X equals the number of Transaction Router tasks that will be running on that application server. The first Transaction Router task should be started with the additional parameter Id Db Recreate: TRUE.
This will recreate visdata.dbf with the increased size above. It can improve Transaction Router performance. Later Transaction Router tasks can be started after the defaults have been set at the component level.
Important: Starting in Siebel Version 8.x, both Transaction Processor and Transaction Router use the shared visibility database visdata.dbf, so make sure that the Id Db Size parameter is the same for both components

If the above steps don't help improve Transaction Router performance, the following options are available for each of the identified causes:

Visibility-Related Transactions: If the cause of the Transaction Router performance problem is visibility-related transactions, then there are two possible solutions:
- Re-extract all mobile users and regional nodes.
- Allow the Transaction Router server tasks to continue processing until the backlog is cleared. The backlog of transactions to route to each node will most likely increase until the Transaction Router has processed all the visibility-related transactions, and then the backlog will reduce more quickly. Starting more Transaction Router tasks will also increase performance, but be careful not to overload the Siebel server or database server machine by starting too many Transaction Router tasks.
Docking Rules and Data Distribution: If the cause of the Transaction Router performance problem appears to be docking-rule related, log a Service Request with Siebel Technical Support and send the following diagnostic information in for review:
- RDBMS trace of the Transaction Router task
- Transaction Router log file
- .dx files the Transaction Router is processing from the SIEBEL_ROOT\siebsrvr\Docking\txnproc directory
- The results of the visrule script
In Siebel applications version 6.x, there were a few specific examples of docking rules causing Transaction Router performance problems:
- In Siebel 2000, the Product Dock Object was changed to be of type Limited visibility rather than Enterprise. This has caused some customers to find Transaction Router performance issues particularly if there are numerous Assets associated with Products. Siebel Systems has recommended in some instances that the Product Dock Object should be set to Enterprise visible, which means that all Products will be downloaded to mobile users and regional nodes. In other instances, specific docking rules have been deactivated. The appropriate action depends on a particular customer requirements and data distribution.
- Transactions that update the table S_SRC, which is part of the Source Dock Object, are incorrectly treated as a visibility-related events, causing the Transaction Router to perform unnecessary visibility checks.
- Other docking rules have caused Transaction Router performance problems caused by data distribution, including rules for the Order and Organization Dock Objects.
Slow Running Queries: If the cause of the Transaction Router appears to be a poorly performing query, then work with the DBA to see if there are any obvious underlying RDBMS reasons:
- Check that all the indexes are present and valid on the tables involved in the poorly performing queries. Check the relevant version of the Siebel Data Model to decide which indexes should be present.
- Check that the tables and indexes involved in the poorly performing queries aren't heavily fragmented.

Important Notes Around Solutions to Transaction Router Performance Issues

Here are some notes related to the solutions listed for resolving Transaction Router Performance Issues:

The causes aren't an exhaustive list of potential Transaction Router performance issues.
The causes aren't mutually exclusive. If you diagnose one cause, you should continue to investigate to ensure there are no more issues.
You should also try to eliminate any other potential environment issues including operating system and network problems.
Siebel strongly recommends that customers fully benchmark the Transaction Router performance with realistic data quantities before going into production with a new release or making significant changes in the amount or type of data to be routed to mobile users and regional nodes.
For example, in production, you might plan to have 1,000 mobile clients and to regularly load 500,000 accounts via EIM, followed by running Batch Assignment. Fully test this scenario out in a test environment before rolling it out to your production environment. Similar testing should be carried out in a test environment when changing the Organizational hierarchy to avoid meeting a Transaction Router performance issue in your production environment.