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 <tboot...@objectpath.com>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 <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"_ > _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"
