Hi Larry.... Sounds strange...
I have seen SQL queries degrade in performance over time depending on the query execution plan, the amount of change to the data, and the statistics that are utilized by the query engine, but it doesn't describe why re-starting arsystem fixes the problem. However, here is a suggestion, 1. Regenerate your db statistics. Your dba will know how to do this. 2. Pick some sql that your app is generating and is a source of your concern. Run it natively via the appropriate sql tool. Turn on the query execution plan and statistics output for later comparison and save the results. 3. When the system starts to slow down, redo 2 above and compare output. if they are the same (or close to it), then it is conclusively not the DB This won't solve the problem,.but it will help you isolate it... Terry Sent from my mobile device.. L G Robinson <n...@ncsu.edu> wrote: ** Hi Folks, Me again... still struggling with this Mid-tier performance issue. ColumnIT (our support provider) has escalated the issue to BMC and I would appreciate a "second opinion" on the information I have received from BMC back line engineering support. I would like to know if you believe the explanation provided makes sense from a technical standpoint. I consider many of you to be quite knowledgable about the inner working of the AR System and it's interactions with the underlying DB, much more so than myself. Background: BMC says that the performance issue is the result of poor SQL initiated by workflow that we have created. Specifically, they contend that there are SQL calls that are resulting in table scans of the table in question. I disagree because my log analysis (thanks Misi) does not show any such SQL. But that is not the part I want your opinion about. This is their explanation: ==> We have verified the SQL statements and SQL queries from X-calls form are using Like operator which causes oracle to go for a full table scan instead of using indexes which introduces delays. Given their explanation, I asked the following followup question: - If the performance problem is the result of "SQL queries which are getting fired on that form are taking too long and not using indexes which is resulting in delays and performance issues", why is it that the problem is not apparent after the AR system is started and only appears after the system has been up and running for a period of time? My expectation would be that performance issues that were the result of inefficient SQL would be apparent all of the time. Please explain how the inefficient SQL results in a gradual degradation in performance over time. This is the BMC response. Does this make sense to you? ==> After the AR server is restarted data caching is done while AR server is restarted which takes less time however when data increases in the form over time so resource requirements will also increase as well as queries will change based on the amount data fetched and what parameters are being passed and any limitations we have implemented for fetching number of records. This does not strike me as the type of response one would expect from back line engineering staff. Thanks for your thoughts. Larry _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
