Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 390 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
finding a performance bottleneck
Answers
-
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
-
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?
-
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 --
-
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.
-
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.