When you are faced with any database related performance issue before you begin troubleshooting it is important to first have some basic ground work done. Below are some key points which should be thought upon before doing actual troubleshooting
1.Identify the performance objective.
What is the measure of acceptable performance?
How many transactions an hour, or seconds, response time will meet the required performance level?
2.Identify the scope of the problem.
What is affected by the slowdown? For example, is the whole instance slow? Is it a particular application, program, specific operation, or a single user?
3.Identify the time frame when the problem occurs.
Is the problem only evident during peak hours?
Does performance deteriorate over the course of the day?
Was the slowdown gradual (over the space of months or weeks) or sudden?
4.Quantify the slowdown.
This helps identify the extent of the problem and also acts as a measure for comparison when deciding whether changes implemented to fix the problem have actually made an improvement. Find a consistently reproducible measure of the response time or job run time. How much worse are the timings than when the program was running well?
5.Identify any changes.
Identify what has changed since performance was acceptable. This may narrow the potential cause quickly.
For example, has the operating system software, hardware, application software, or Oracle Database release been upgraded?
Has more data been loaded into the system, or has the data volume or user population grown?
For any long running queries, below questions can be thought upon:
- Is this a new query or existing code?
- If old/existing query, since when it started going long? Find historical execution time data.
- If old/existing query, In which database instance it works good? This will help in comparison.
- If old/existing query, has more data been loaded into the system, or has the data volume or user population grown?
- If old/existing query, any recent change to query. Mention changes.
- If old/existing query, Was the slowdown gradual (over the space of months or weeks) or sudden?
- If old/existing query, what is good time to finish for this report/query?
- If old/existing query, in which non-production instance it was tested before moving to Production?
[Post Views: 170]
You can connect with me on LinkedIn.
Latest posts by Brijesh Gogia (see all)
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC) - April 1, 2018
- Physical and Logical Block Corruption in Oracle Database - March 10, 2018
- Performance Tuning Basics 15 : AWR Report Analysis - February 6, 2018