When first starting to work on a performance problem there is usually a request made to send in some combination of wt & db properties files, an Oracle report and Method Server logs. The support engineer will review the data looking for problems or potential problems. What does that review entail? Some variation of the below is often what occurs first what happens happens as the next depends on what is found.
Property files:
Of the hundreds of properties between the wt and db properties files, there are only a small number which really matter for performance. Using the Windchill Configuration Assistant (installed by default in R10 and later systems see chapter 1 in the article documentation links) is good way to get almost all of the server side properties set correctly. This tool will make machine specific recomendations for the settings of key performance properties. The Windchill Client Inspector does someting similar for the client side.
- In the wt.properties the key properties for performance relate to the JVM start parameters.
- We look for the wt.method.maxHeap to generally be the 2G range or more and default or close to JVM settings for the other wt.manager.cmd.MethodServer.* properties. Non-standard settings may be ok depending on what they are, but more unusual isn’t always better.
- A quick look at the number of method servers being started to make sure the machines memory isn't being exceeded
2. Surprisingly the db.properties file contains many properties which are key for performance.
- The first property checked is the wt.maxDbConnections setting for foreground method servers a setting of 10 or 15 is usually fine if there are lot of custom queues, a background server may need more than 15, but generally no more than 50.
- Db connections are important for performance because everything which happens within Windchill depends on multiple rounds trips to the database. When there aren’t enough connections available and operations need to wait for access to get to the database the method server appears ‘hung’ to users. There are different reasons why there may not be enough DB connections, and adding more isn’t always the best course of action when it happens, but only using the min and max default settings of 5 db connections will very likely cause stability problems.
3. The next db.properties to check to ensure they are at their defaults are:
- wt.pom.maxIdleStatementCaches (0)
- wt.pom.rowPrefetchCount (20)
- wt.pom.statementCacheSize (default will vary but no more than 100)
- Increasing any these properties from their defaults will cause ALOT more memory to be used per in use db connection.
4. The last settings to review in the db.properties file are the query limits. Query limits are a double edged sword. On one hand they prevent unbounded sets of results from the database from overwhelming the Method Server memory, but on the other hand some (usually a very small number of operations) may need a large result to complete but won't because they run into the query limit, and that’s the rub. You need to set the limit low enough to prevent out of memory problems, but high enough things that need to complete are able too. To further complicate things, there are three query limits, two are for searches and the other is for all other queries. See the link(authenticated) for more informaiton on these limits. The wt.pom.queryLimit is by far the most important parameter to set this property not being set is the number one cause of method server outages it is also the only property the Windchill configuration Assistant doesn’t set which I think it should.
Next is a review of Oracle (or SQLServer).
See the article link(authenticated) for a more in depth discussion about tuning Oracle using the Oracle Gather Info Script.
In a review of the gather info report, we are trying to get a sense of what the biggest problems are in the system; is there enough SGA what are the longest system wait events and what is the slowest SQL? A significant percentage of Windchill performance problems can be improved or resolved by making changes to Oracle. What changes are needed will depend on the specifics of the system but usually the top 3-5 long running or resource intensive SQL statements are identified and indexes recommended, increasing the SGA is also a commonly recomended change. A regular review of the database performance by a DBA is something many systems could be benefit from.
Lastly is a look at the Method Server log files.
Have you had to look through 50 or 100 MethodServer log files, which are cumulatively hundreds of megabytes in size? It can be time consuming…. The questions to answer are, which log files contain the problems, when did those problems occur and if possible what is the root cause that led to the problem? I usually start by using a text editor to search the directory holding the log files, looking for ‘wt.method.MethodContextMonitor.stats.summary’ and then look for lines which exhibit something unusual, see the article(authenticated) for details on what to look for. What happens next depends on what is found, but opening a Technical Support case will be the best course of action.
It's been my observation that performance recomendations come in two varieties. The first is the best practice type. This usually involves some a series of recomendations which are well intentioned but havn't been evaluated for the specifics of the system or circumstances of the system involved. The second way, and the way performance TSE's work, is to to gather and evaluate a series of data, diagnose and understand the problem and then take corrective actions. This approach can take longer, and require more data but it works everytime and importantly minimizes the risk to the system involved.
