Look at your sql logs. Do you see a long delay between the time the select is issued and the results returned? If so, that would indicate to me that the SQL is the cause of the slowness. Start there, then cross the next bridge when you get to it.
Axton Grams On Thu, May 17, 2012 at 10:32 AM, L G Robinson <[email protected]> wrote: > ** Hi Terry, > > That is a good suggestion. I have done an execution plan for the SQL in > question, but I have not done a before & after for the same SQL. > > Thanks. > Larry > > On Thu, May 17, 2012 at 11:21 AM, Terry Bootsma > <[email protected]>wrote: > >> ** >> 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 <[email protected]> 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"_ >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> > > _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"
