Skip to content

How to define a Performance Issue

Published August 3, 2016 By Brijesh Gogia

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?

 

Brijesh Gogia
Latest posts by Brijesh Gogia (see all)
Print 🖨
Post Views: 58

Related posts:

  1. Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
  2. Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
  3. SYSADMIN login performance issue in Oracle EBS R12
  4. Performance issues with function based indexes
  5. Key Reasons for Database Performance Issues
  6. Performance Tuning Tips for GoldenGate
  7. Performance Tuning Basics 1 : Selectivity and Cardinality
  8. Performance Tuning Basics 2 : Parsing
  9. Performance Tuning Basics 3 : Parent and Child Cursors
  10. Performance Tuning Basics 4 : Bind Variables

Categories:TUNING Tuning Basics

2 Comments

  1. Annadakant Mishra Annadakant Mishra

    What is the difference between Performance tuning and trouble shooting .Which topics are coming under each part.

    October 3, 2018 Reply
  2. Annadakant Mishra Annadakant Mishra

    Is the process of defining to do performance tuning same irrespective of Oracle version,level of patch sets, OS ,OS components and hardware set up.

    October 3, 2018 Reply
Leave a Reply