Forum Stats

  • 3,815,192 Users
  • 2,258,976 Discussions
  • 7,892,986 Comments

Discussions

finding a performance bottleneck

13»

Answers

  • DineshKDubey
    DineshKDubey Member Posts: 13
    edited Feb 13, 2017 3:43PM

    I have seen the same scenario, where the query will run fast if you will run outside the storeproc or view. But that will run slow when the application will execute that code/view. 99% of the time I had seen plan flips. If that was a SELECT that dont use gloal temporary tables I generally use SQL Plan management (SPM) to disable the bad plan.

    Thanks

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Feb 13, 2017 3:50PM
    2875283 wrote:I have seen the same scenario, where the query will run fast if you will run outside the storeproc or view. But that will run slow when the application will execute that code/view. 99% of the time I had seen plan flips. If that was a SELECT that dont use gloal temporary tables I generally use SQL Plan management (SPM) to disable the bad plan.Thanks 

    When did average elapsed time of 0.00012 seconds become considered slow?

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Feb 15, 2017 1:20PM

    user571263, I am not an export on what OS statistics should look like but I notice that your IDLE times appear to be about 10X greater than your busy times so I do not see a problem.  And if your administrator says paging is good maybe he or she is correct.

    - -

    I have not looked back but if my memory is correct this thread started with Advisor Output.  I can remember that most of the posted query run times looked good to me.  Something to remember is the output of the Oracle Advisors need to be taken with a grain of salt as the recommendations can be garbage.  Your AWR reports should identify if you actually have a problem(s).  A real problem should be visible in the application.

    - -

    HTH -- Mark D Powell --

    GlenStromGlenStrom
  • Unknown
    edited Feb 15, 2017 1:56PM

    I'm having trouble understanding why you haven't followed up on some of the red flags you are finding?

    This was leading me to assume that the actual queries were not the problem but thought perhaps it was the number of times the queries were being executed. One of them was executed 1,937,344 times in the one hour period.

    So what was the result of following up on that? This is what you first said:

    The database is used by many connected libraries, to check books in and out, query for overdues etc.

    How do you account for 2 million of the same query if it is to check a book in/or or for overdues?

    When I ran the SQL for one report that returned 3 rows of results for the application, I got well over 100,000 rows returned from running it in sqlplus. This prompted me to think perhaps the bottleneck was in the application code, perhaps parsing all this data internally for the desired result? 

    I'm curious - didn't that raise a RED FLAG to you? Did you follow up on that?

    If you have a client that queries EVERYTHING and then filters out what they don't want you have a problem.

    Most filtering should be done by the DB not the client.

    Post the results of the followup analysis for those two items.

  • GlenStrom
    GlenStrom Member Posts: 217 Bronze Badge
    edited Feb 23, 2017 4:23PM

    I posed these questions (and others) to the person whose department purchased (and uses) the app to ask of the vendor, who is also the person that asked the DBA group to look into the performance issues. No answer has been received to date and I am unaware if they were asked or not. I bumped into one of the users a week ago and asked her if things were still slow, she said that the vendor had applied some "hot fixes" to the application code and things were "better".

    So, from my perspective, until I get more information from the questions I asked to be put to the vendor, there's not a lot I can do, until then.   

This discussion has been closed.