Problem Description:
If you, as the Harvest Administrator, receive a call or email from an end user that SCM performance is slow, then this document attempts to provide some common sense steps to help you identify the problem area and the root cause of the problem.
Steps to advocate a solution:
The purpose of this document is to provide steps that you can use to help identify the source of the performance problem.
There are many variables to consider in relation to SCM performance. Since a database, network, server, and client application all must work in concert in order to process SCM transactions such as check out / check in in a timely fashion. Each facet must be analyzed so that possible sources of the problem can be ruled in or out. Once an area has been identified that is causing the disruption then appropriate analysis need to be further applied in order to narrow down the root cause. For example, analysis has revealed that the problem appears to stem from the client machine so then immediate focus would be on the client environment settings to try to determine if anything recently has changed such as a possible corporate enterprise software change has been recently pushed to the client or it may be possible that the user is running a concurrent application that is using high CPU cycles on the machine. These possibilities have been exposed in other customer issues as the root cause, so if performance has been nominal and now it is performing poorly then most likely something recently within the environment has changed to affect this condition and this should be taken into account with your analysis.
When an end user reports an SCM performance problem, you must gather as much information about the incident as possible:
- What SCM client application was being used? Workbench? Visual Studio? Eclipse? Commandline utility such as hco?
- What is the client SCM version? does it match the server patch level?
- Did this work ok before? if so, what has changed recently?
- Is the problem persistent or intermittent?
- Is this problem affecting all the users or just one or a few?
- What type of activity was the end user performing when the problem happened?
- Did the user encounter any error message? If so please provide a screen shot of that message.
- Use hdbgctrl commandline utility on the broker machine to monitor and log the client's processing (R12.1 and above).
Network:
- Can the user ping the Broker (or Agent if Agent problem) from his client machine? Can Broker (or Agent) machine ping the user's machine?
- Is direct connect port range implemented?
- What is results of the "telnet <broker> <port>" command from the user's machine?
- Are the proper ports opened on the Firewall?
- Can the broker ping the database machine (if remote from the broker) and vice versa?
Server:
- Is broker (bkrd) service running and the minimum number of hservers and rtserver running?
- Did restarting the broker clear up the problem?
- Do all of the hserver processes connect to the database successfully?
- Does increasing the number of minimum hservers correct the problem?
- If using a direct connect port range, does increasing the number of ports and hservers have a positive affect?
Database:
- If your database is on a remote machine, has the database been restarted to clear up the problem?
- Did the database generate any problem alerts?
- It has been proven in many cases that rebuilding the Harvest table indexes and locking Harvest temp table statistics have provided markedly performance improvements since it is a know fact that table indexes can deteriorate over time when records are deleted are added. Attached is the R12.5 List of Temp tables document.
If all servers and database systems appear to be online and the network and firewall configurations appear to be nominal then the problem of performance may be SQL related.
The Harvest broker has the capability to generate a set of HBroker and HServer log files that can record all SCM transactions down to the SQL statements that are applied to the database and the returned results. The amount of detail about SCM transactions to be recorded in the log files can be adjusted to meet your needs. This can be done in either of two different ways. The old method involves configuring the Broker's HServer.arg file to add the -logging=n (n=1 to 4, generally level 4 is used to provide SQL statement logging) line and then restarting the broker for the log level to take affect. This manual effort to generate logs can be accomplished at all Harvest release versions. The downside is that the Broker must be restarted and this could adversely affect production. Beginning with R12.1 SP1 and above, a new command line utility has been provided called "hdbgctrl" that can be used to adjust the logging level and generate the HBroker and HServer logs at the appropriate log level without requiring a restart of the Broker. Bear in mind that the use of hdbgctrl will override any current log level settings set in the HServer.arg file. In this manner, if a current set of HBroker and HServer logs are being generated, then the use of hdbgctrl will cause those current logs to stop logging and a new set of HBroker and HServer logs will begin.
Details on usage and examples of the hdbgctrl utility can be found here in the release Command Line Reference Guide and also: Knowledge Document TEC567011
Once the HBroker and HServer logs have been generated for the user's SCM performance problem, the Harvest administrator can use the HLogBrowser utility to review and analyze the logs to narrow down the information in the logs that may exhibit latent timing.
The HLogBrowser is a windows 32bit application designed in Visual Studio IDE display style that provides the features that allow the investigator to navigate SCM transaction activity from HBroker to HServer logs as well as to run transaction and SQL statement latency analysis reports. Keep in mind that the Harvest HBroker to HServer log is a one-to-many relationship. One HBroker logs can reference one or more HServer logs. This is because in Harvest, the Broker manages a pool of HServers. The Broker initially receives the client SCM transaction request and then checks the HServer pool to see if any HServer processes are free and then passes the transaction to the HServer for it to process with the database. The HServer then returns the transaction results directly to the client machine and then reports to the Broker that it is free for the next transaction.
Once a latency report has been generated, if a certain type of SQL statement stands out as taking more time than expected, the DBA for your Harvest database can conduct some analysis to further isolate the problem. For example, the DBA can perform an Explain plan against the SQL statement or a cost analysis may reveal more information. Sometimes it is expedient for the DBA to periodically drop and rebuild the Harvest table indexes, and in the use of Oracle, to lock Harvest temp tables from gathering optimizer statistics.
The HLogBrowser is a "Field Developed Utility (FDU)" and as such, provided as is. New features and enhancements may be added from time to time and, as such, the version of this utility will increment appropriately (ie V1.01 or 1.02, etc) when the updated utility is published for release.
HLogBrowser 4.15c Enhancements:
The STATS feature provides a good report for either the client or hserver perspective of all SCM activites performed
during the logging period. This feature also has been enhanced to provide a performance in elapsed time column
for all the activity reports. You will have to set "Actions->Options->Enable elapsed time calculations in STATS"
checkbox to turn on the elapsed time column feature. It is turned off by default because this feature can take a lot of
time to complete since it is resource heavy with the processing it has to do for each line item. I employ background
worker threads to accomplish this process.
The HLogBrowser User's Guide PDF file will be installed to the install location of the tool.
ADDENDUM 24 OCT 2016 - Modified the installer to work with windows 10 (32 or 64bit).
ADDENDUM 28 JUN 2017 - Corrected external search process was unable to open all logs due to file locks, added new Transaction Integrity check (TIC) process.