Well, if you have data piling up somewhere, and you have workflow that scans that data, as the size of the data increases, the time required for the scan will increase. If this is going on, you will see evidence to that effect in the sql logs (increasing time in the sql logs for the same statement execution).
If you don't see sql that is taking a long time to return, then I think you are barking up the wrong tree. People have been known to give bad advice before ;) A couple of seconds is ok; milliseconds and microseconds are great. If you are seeing 2 seconds in the sql logs for a statement to return, and it is taking 60 seconds for the client to respond, the cause is not the sql. If someone issues bad sql, let's say something that takes 120 seconds to return, they will occupy some thread (fast, list, of custom, depending on their connection parameters). If you have 2 threads, and 2 users run a bad sql, each of which takes 120 seconds, every other transaction will be queued until one of those threads is returned to the pool. It's not a matter of performance, per say, but a matter of availability of a thread to handle the work. Either a thread is ready to handle the work and does, or a thread is not available, and all the new work coming in has to wait for a thread to become available. This is why it is a good idea to move certain subsystems to private queues. Things like RE, AREmail, or AIE, which are known to keep threads busy, while running, are good candidates. It keeps the resources available where they are needed (i.e., your users are not fighting for resources against your automated systems). Think of a private queue as a containment vessel for whatever uses that private queue. If it goes haywire and starts doing a bunch of work, it will only affect other connections that share that queue. Axton Grams On Thu, May 17, 2012 at 12:16 PM, L G Robinson <n...@ncsu.edu> wrote: > ** Hi Axton, > > I have Used Misi's excellent RRR|Log analysis tool to analyse 24 hours > worth of combined API and SQL logs. For the entire 24 hour period, I found > a small number of SQL commands that do take several seconds to execute. > However, in all cases, these SQL queries are the result of poorly defined > advanced searches initiated by my users. The great majority of the SQL > commands issued against this table use indexes and complete in well under a > second, usually on the order of 0.00xx seconds. > > But still, I am wondering if longer-running SQL transactions can account > for the gradual degradation of performance over time. I can understand that > the user who issues the poorly-designed advanced search will suffer the > delay resulting from inefficient SQL, but do you believe that it will > affect system performance in general, assuming there are enough threads, > etc? Do you have an opinion on that? > > Thanks for any insights. > Larry > > On Thu, May 17, 2012 at 12:05 PM, Axton <axton.gr...@gmail.com> wrote: > >> ** 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 <n...@ncsu.edu> 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 <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"_ >>>> >>> >>> _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"
