I am trying to determine the effect of a database move (PDMLink 8.0 M040 with Oracle 10G). We moved our PDMLink database to its own machine away from another box. It had another database that at times, could consume a lot of resources. I want to measure the effects before and after the change. I decided to use the SUMMARY statistics. I look a weeks worth of logs before and after the database move and popped them into Excel. Here's where I need some advice:
1. I took the average duration time in milliseconds and multiplied it by the number of calls to yields a total time for that period.
2. Next I put the data in a pivot table summing this "total time" for each hour and grouping it for each day.
The result is a chart by day that shows activity rising and falling over the course of the day with weekends looking flat. This is what I would expect to get. I am just looking for number for a relative comparison. It does not have to be a metric of exact activity or load, just an analog for it. Does this seem a correct method? Keep in mind, I have to work with data I have, not data I wished I had done earlier.
o Found out that Linux works 25% faster than Itanium with the same amount of ram amount of CPUs .
2. SAN versus local hard drive. Depending on the amount of data, with a larger dataset SAN is far faster.
3. Amount of Oracle sessions, sometimes the oracle sessions just hang due to a long process in Windchill
o This disappears in a restart of oracle
4. Amount of free space in the Oracle tablespaces. It is best practice not to let the tablespaces automatically extent all the time. You can leave it to auto extend, but always have thresholds for freespace on a tablespace.
The charts you have is great with oracle CPU and timeline. You need or have to overlay this with Windchill tomcat and methodservers java processes on the same timeline down to the second/minute. Thus, you can fine the bottlenecks if they are on the Windchill, Tomcat or Oracle. I have to look into a good "silk test" scripts you can run to repeated test with load. A good test is usually above 20 to 40 concurrent users to warm up the system. The more the better to find the bottlenecks.
In the case of #3, it may not be oracle that is the culprit of the performance. This may due to just Java heaps of tomcat and methodservers. This may cause case 4 with large temp tablespaces. FlexPDM and customized queries played the majority of these performance issues.