For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
What are some of the ways to analyze the performance of a database?
Is there any techniques and what are some of the parameters to consider?
Thank you.
3643808 wrote:What are some of the ways to analyze the performance of a database?Is there any techniques and what are some of the parameters to consider?Thank you.
3643808 wrote:
Performance should be measured from the point at which it matters, this is usually going to be the response time for the end use in an application.
You should be able to see where the time is taken in DB calls from a specific end user call by using dbms_application_info calls to instrument your application. Combine this with extended sql tracing for accurate and precise information about where the time is going, this is what you’d need to tune if there is a problem at the DB call level.
Quite often you will be told you need to use AWR reports but that suffers from the problem of scoping - it has no idea what is important to your actual business processes. It can be good for seeing very general trends like overall your database is doing more physical IO but whether that really matters depends on if your critical processes spend more time doing physical IO, which you can tell with extended sql trace (and not having to pay for the diagnostics pack licence).
-edit
Here‘s my beginners guide to diagnosing performance problems: https://ctandrewsayer.wordpress.com/2017/03/21/4-easy-lessons-to-enhance-your-performance-diagnostics/amp/ I don’t go over sql trace but I do share some other methods you need to get to grips with
Thank you for your response.
It is indeed helpful.
Another question is it necessary to acquire certification for database analysis?
3643808 wrote:Thank you for your response. It is indeed helpful.Another question is it necessary to acquire certification for database analysis?
It depends how you learn. Some people really benefit from that sort of thing, some people have the time to play around and learn as they go along (using the docs as a basis).
As for whether you can get employed without certification, that depends on the company. It certainly shouldn’t hurt your chances.
I agree with Andrew's reply. You don't "analyze the performance of a database", you look at the performance of the application, which is all that matters to end users. And only if you have a "performance problem" at the application level do you go and look at the full stack of software and hardware that is used to deliver that application. So that includes any middle tiers such as application servers, and hardware tiers such as networks (which can easily limit throughput and end user response time). And only when you have identified where the bottleneck is do you then go and drill down further into that bottleneck. Analyzing anything other than the bottleneck is a waste of time, and won't improve anything.
Fundamentally databases don't do anything themselves, by default. They sit there quietly waiting to receive requests for work to do. Databases ONLY do work on behalf of applications which submit SQL statements to be executed. So the activity you observe on a database is all due to the application software running on top of it and using it. And every application differs, so there is no generic "database performance analysis" you can do. It is always application specific. And again, if there is no "application level performance problem" then there is nothing to "fix" at the database level.
What you can do at the database level is look at "efficiency" - the amount of resources used and work done by the database when executing its SQL workload. But that is not necessarily related to "performance" - how long something takes to complete. You can still have fast executing SQL with inefficient execution plans, and vice versa (slow SQL execution with efficient execution plans).
Start at the top with the application behaviour and then drill down into the layers underneath it - work from the problem down towards the cause. Don't do it the other way around, and try and start at the bottom with the database and work backwards towards the application software. You could easily end up looking in the wrong place.
The 4 major system resources are RAM, CPU, DISK I/O, & NETWORK.
For each of these critical resources which metric at what value indicate that this resource is a system bottleneck?
Oracle database can perform sub-optimally when resource starved due to OS limitation.
First order of business is to determine if root cause of any performance problem at OS level or at database level.
user3643808, to add to what has been posted the answer to your question depends partially on what version/edition of Oracle you have, on if you have the EM Diagnostic Pack license, and most importantly of all if your have complaints or someone has identified tasks the performance of which is not sufficient to meet the business need.
- -
For instance if there are no reported issues and you have the EM Diagnostic Pack license and you have the Enterprise Edition then you can run an AWR report for a prime time period. Upon review if no issues jump out then you may to save this report as a baseline to compare future reports against. On the other hand if you have the Standard Edition you do not have the AWR as an option, but you can set up and run STATSPACK as a free substitute.
When review the AWR remember that there is always a TOP N object. Just because a Wait or Query is near the Top of a list does not mean it is a problem, only that it might be something work looking at.
IMHO -- Mark D Powell --
Sounds like an early symptom of Compulsive Tuning Disorder.
Ed, >> Sounds like an early symptom of Compulsive Tuning Disorder <<
Sounds more like inexperience to me, but it can be hard to tell. Snow everywhere here in Michigan.
HTH -- Mark D Powell --
Mark D Powell wrote:Ed, >> Sounds like an early symptom of Compulsive Tuning Disorder <<- -Sounds more like inexperience to me, but it can be hard to tell. Snow everywhere here in Michigan.- -HTH -- Mark D Powell --
Mark D Powell wrote:
True, but inexperience is often a cause for CTD. It was for me when I contracted a case of it 20-some years ago.
Thank you guys for the contributions. Appreciate it.