This issue was finally resolved. And when I say "Resolved" I mean "It works fast now but there are plenty of unanswered questions." First of all - if you are going to install any of the 7.x apps with a remote Oracle database - you should be using the in-row LOB storage option. When we switched everything to in-row the speed increased dramatically on the one server. I have seen no evidence to support any other configuration when using a remote Oracle database. We literally tried everything before going that route because it was counter-intuitive. We had server a, database a, server b, database b. We tried hooking server a to database b and vice versa - nothing pointed to the database being the culprit. We tried using the ar.conf/armonitor.conf from one server on the other and vice versa (server name of course was changed but everything else was the same). We never did get a good answer as to why the cursor sharing was behaving differently on the two identical servers/databases. We had the local Oracle performance expert do extensive analysis and he couldn't find anything different in the databases with one exception: the fast database was storing the LOB's using a LOT less storage space (800mb vs 37kb). The actual Oracle response time for queries was actually BETTER on the database with slow Remedy Incident creation results. Go figure that one.... This is troubling to say the least - the databases, app servers, etc are all identical and the builds were all performed the same way. There should not have been any difference in the results of how they were structured or how they were working. They should have been using about the same space and they simply were not. I suspect we'll never know if the culprit was more database or more Remedy - but we are building a final new server in the set. It will be interesting to see if it works correctly. I'll be issuing an RFE to BMC to ask for an in-row installer option for all apps. Anything this important should not be an after the fact major change to the database.
________________________________ From: William Rentfrow Sent: Thursday, April 03, 2008 12:56 PM To: '[email protected]' Subject: A tale of two servers (Oracle inconsistency with LOB's) It was the best of times, it was the worst of times.... It was the age of fast LOB updates in one instance of Oracle and slow LOB updates in another - yet they are supposed to be identical builds. These servers: ARS 7.01 patch 001 on Solaris logical machines - not using portmapper. Build includes AE, Email engine, IM 7.03. SLM 7.1, CMDB 2.1 (and associated required elements). The servers have the same firewall configuration. The servers have the same database platform, etc. The Veritas file system has the "fast oracle" option set (whatever the option is exactly) on both. Here are portions of the same SQL commands from the the two servers: These lines of code execute in sequence saving an Incident. Notice the difference in gaps between lines 5-6 and 11-12 in these two. The response time is drastically different (approximately 0.16 seconds vs. 1.4956 seconds) . There are a total of 11 different spots where this happens during an incident save and the gap ranges up to nearly 3 seconds in the slow server. This means a total of 8 seconds to save on one server and 46 seconds to do the save on the other. Please note: Table names in Remedy are generated and named dynamically and may not match between systems. Even though T580 and T571 do not have the same names they do have the exact same structure, etc. (Dev) Thu Apr 03 2008 10:23:39.1581 */UPDATE T580 SET C1000005892 = EMPTY_CLOB() WHERE C1 = '000000000001873' Thu Apr 03 2008 10:23:39.1601 */OK Thu Apr 03 2008 10:23:39.1601 */SELECT C1000005892 FROM T580 WHERE C1 = '000000000001873' FOR UPDATE Thu Apr 03 2008 10:23:39.1621 */OK Thu Apr 03 2008 10:23:39.1622 */Set LOB into the above row ... Thu Apr 03 2008 10:23:39.3288 */OK Thu Apr 03 2008 10:23:39.3290 */UPDATE T580 SET C1000005893 = EMPTY_CLOB() WHERE C1 = '000000000001873' Thu Apr 03 2008 10:23:39.3320 */OK Thu Apr 03 2008 10:23:39.3321 */SELECT C1000005893 FROM T580 WHERE C1 = '000000000001873' FOR UPDATE Thu Apr 03 2008 10:23:39.3345 */OK Thu Apr 03 2008 10:23:39.3345 */Set LOB into the above row ... Thu Apr 03 2008 10:23:39.4890 */OK (QA) Mon Mar 31 2008 14:52:18.6192 */UPDATE T571 SET C1000005892 = EMPTY_CLOB() WHERE C1 = '000000000000480' Mon Mar 31 2008 14:52:18.6247 */OK Mon Mar 31 2008 14:52:18.6253 */SELECT C1000005892 FROM T571 WHERE C1 = '000000000000480' FOR UPDATE Mon Mar 31 2008 14:52:18.6280 */OK Mon Mar 31 2008 14:52:18.6286 */Set LOB into the above row ... Mon Mar 31 2008 14:52:20.1242 */OK Mon Mar 31 2008 14:52:20.1251 */UPDATE T571 SET C1000005893 = EMPTY_CLOB() WHERE C1 = '000000000000480' Mon Mar 31 2008 14:52:20.1289 */OK Mon Mar 31 2008 14:52:20.1295 */SELECT C1000005893 FROM T571 WHERE C1 = '000000000000480' FOR UPDATE Mon Mar 31 2008 14:52:20.1324 */OK Mon Mar 31 2008 14:52:20.1330 */Set LOB into the above row ... Mon Mar 31 2008 14:52:21.6201 */OK Anyone have any idea what could be different? William Rentfrow, Principal Consultant [EMAIL PROTECTED] C 701-306-6157 O 952-432-0227 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
