Skip to content

How to define a Performance Issue

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: 143]

Read Offline: PDF Download

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

Be First to Comment

Leave a Reply

Required fields are marked *