We ran into these issues awhile back given some of our tables have millions of rows, so we created a simple "search capture" form and "search protection" dialog form.
These could help if uses are performing searching in forms or reports, if they are searching via ODBC then you might consider limiting use to a permission group or specific datetime range in the AR.CFG The "search capture" is a form with some AL's that grab fields from a form being searched or the advanced search (add the search bar field to the form), if it is a crystal report you can decode the search with Set fields ($PROCESS$ Application-Map-Ids-To-Names $ReportFormName$ $Search$) The "search protection" recognizes specific search fields and/or datetime field ranges. Example of one scenario: if STRSTR(SUBSTR($Search Bar$,STRSTR($Search Bar$, "'Create Date'") + 13), "'Create Date'") > 40 then a daterange is used (although there are other permutations to check) - find the time between the range or some other action If the query doesn't meet criteria we direct the user to a separate dialog form with selectable search criteria and limited date ranges. Hope this helps Pat Dill Enterprise Tools Team Roosevelt Commons, Floor 5 206-545-3217 Office 206-931-3006 Cell -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Stonequist Sent: Tuesday, January 02, 2007 2:34 PM To: arslist@ARSLIST.ORG Subject: Performance Issues caused by User Queries - Remedy 6.3/Oracle 10.0.2 Greetings List!! We have an issue in which some users (we know not who) are running queries which are affecting the performance of our Remedy system, causing significant delays, issues, and even complete system lock-ups for all our Users. We have three goals that we would like to accomplish: 1) Identify the user(s) and what they are doing 2) Prevent future performance degradation of this nature 3) Stop offending queries in progress should prevention be not possible to bypassed in some manner, using a method other than rebooting our database (as this brings all our users down, which is what we are trying to remedy) We are interested in any possible solutions or paths to accomplish one or more of these goals. We spoke with our Remedy Partner that provides our support, but unfortunately, their best resolution was using User/API/SQL Logging Remedy-side. Unfortunately, for this to be successful, we would need to know before the offending query is run, or have it always running (something our SA is against due to system resource concerns) Our SA provided me the following information concerning our configuration, which may be helpful (I apologize that I don't know what it all means): Distributed Server Remedy 6.3.0 patch 018 Oracle 10.0.2 Monolithic We look forward to and appreciate any assistance available. Thanks!!!! Shawn Stonequist EMNS, Inc. ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
