finding a performance bottleneck

DineshKDubey

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

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

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 --

Unknown

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

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.