Turn on your SQL logging and try running the SQL that is generated in Toad (just select statements, not updates or inserts.) In the past I've found that there can be table scans that are major performance hits that way. The resolution ends up being adding more indexes or changing the criteria Set Fields workflow uses. If you find a SQL statement that takes too long to run, pass it on to your DBA and they should be able to do more troubleshooting to see why it is doing so and what can be done about it.
Shawn Pierson -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of ZHANG, ERIC L Sent: Wednesday, August 08, 2007 11:26 PM To: arslist@ARSLIST.ORG Subject: URGENT - ITSM 7 performance issue - help needed I post this earlier today but don't see it on the list. I am trying again. I apologize if you already saw this. -----Original Message----- From: Eric Zhang [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 12:21 PM To: [EMAIL PROTECTED] Cc: ZHANG, ERIC L Subject: URGENT - ITSM 7 performance issue - help needed Hello, all. We have been struggling with ITSM 7 performance since it went live on 8/1. The performance is getting worse and worse for all the operations: search, create, and update. At the begging, it's the creating incident that took more that 40 seconds to complete. Now it's all the operations, primarily on Incident. The incident ticket update can take up to 3 minutes now. We opened a critical ticket to BMC support but so far we haven't received a solution that improves the performance. Our configurations: ARS 7.0.01 no patch on Solaris 9 Database is Oracle 10gR2 on another Solaris 9 box Midtier is on Wintel 2003 IIS server (Only for requesters) ITSM (including Incident and Change) 7.0.02 patch 3 I have been sending all kinds of loggings to BMC. The first suggestion from BMC was to set Oracle-Cursor-Sharing to FORCES. That didn't help. The other suggestion was to change a Veritas setting ("mincache=direct") on the database server. But our DBA, UNIX. and Storage people all rejected the idea because 1) that fix is for bulk data import 2) they had very experienced with that setting, which produced high IO waits and worse overall daily performance. Thanks in advance for your help. Eric Zhang Sr. IT Consultant Entergy Services, Inc. (501) 377-5815 ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" Private and confidential as detailed <a href="http://www.sug.com/disclaimers/default.htm#Mail">here</a>. If you cannot access hyperlink, please e-mail sender. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"