Skip to content

Performance Tuning Basics 11 : Steps to analyze a performance problem

Published December 7, 2017 By Brijesh Gogia

A well-planned methodology is the key to success in performance tuning. Without a proper diagnosis of the issue any troubleshooting step taken will further delay the resolution. Example, a DBA can not just blindly start tracing/tuning a SQL/Sessions when all symptoms on careful analysis points towards issues with DIsk/Memory/Network etc.

Also remember that most common performance gains are attained through SQL/Code/application tuning and increasing hardware resources should always be your last resort. Developers play an important role in writing applications with effective SQL statements so it is important that DBA and Developers should work together when facing a database/application issue.

When DBAs are reported a performance issue, first step is to get as much information as possible that is related to the issue. You can ask below example preliminary questions to users/developer to collect the first level of information..

 1. What operations/program are executed?
 2. Is it Oracle seeded or custom program?
 3. How much time it used to take earlier?
 4. Is the run time increased over time or you are seeing sudden increase in run time?
 5. Was there any recent code change/migration?
 6. Is it always slow or for certain time of the day only?
 7. Is it slow for all parameters or for some specific parameters?
 8. How much data is getting processed?
 9. What is the frequency of job execution? Was there any change in frequency?
 10. Does the problem happens on both their test and production systems?

 

Asking above kind of questions will help you in deciding what part of system you should target.

  • Target the whole system
  • Target a single session
  • Target a single SQL statement

 

Your goal should be to answer below three questions:

Where is time spent?
You need to identify where the time is getting consumed. Is it the code or network latency or disk I/O bottlenecks or the CPU/Memory crunch

How is time spent?
You need to find out how the time is spent and how much time is spend in each layer – database, application, network, disk etc .

How to reduce the time spent?
Finally, based on above information see where the major chunk of time is spent and how to reduce it.

 

High level steps for System level performance issues

  • Use vmstat top/prstat to identify system wide CPU/Memory consumption.
  • Use iostat to verify if disks are the bottleneck
  • Use netstat/tnsping etc to verify if network is issue.
  • Verify if any other resource intensive processes are running on server.
  • Verify filesystem space.
  • Check alert logs, application logs, traces etc.
  • Check database locks
  • Generate AWR reports to see what is eating up resources.
  • Check if increasing application/database memory/redo/undo/temp/SGA will help.

 

High level steps for Session level performance issues

  • Find the Top SQLs executing under session.
  • Apply SQL optimization techniques on top SQLs.
  • Verify locking at session level
  • Generating AWR/ASH for that duration may help in providing useful information.

 

High level steps for SQL level performance issues

  • Apply trace, Generate TKPROF file and analyze for top resource consuming SQL.
  • Avoid full table scans on large tables
  • Possible indexes on columns contained in the WHERE clause
  • Use AWR/ASH reports to get collective information
  • Also use SQLTRPT/SQLT
  • Verify is statistics are current
  • Verify if indexes are valid and are adequate.
  • Verify is parallelism/materialized views/Baselines/SQL Profiles will help
  • Monitor V$SESSION_LONGOPS to detect long running operations
  • Decide on using Hints if those are helping.
  • Table partitioning can be thought of as an option based on kind and size of tables.

 

This is one of the post in Performance Tuning Fundamentals Series. Click on below links to read more posts from the series:

 

Brijesh Gogia
Latest posts by Brijesh Gogia (see all)
Print 🖨
Post Views: 1,595

Related posts:

  1. Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
  2. Performance Tuning Basics 2 : Parsing
  3. Performance Tuning Basics 10 : Histograms
  4. Performance Tuning Basics 1 : Selectivity and Cardinality
  5. Performance Tuning Basics 3 : Parent and Child Cursors
  6. Performance Tuning Basics 4 : Bind Variables
  7. Performance Tuning Basics 9 : Optimizer Mode
  8. Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
  9. Performance Tuning Basics 12 : Dynamic Performance Views
  10. Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF

Categories:DATABASE Database Basics Performance Tuning Series TUNING Tuning Basics

2 Comments

  1. Om D Om D

    Please share the next topics on PT if you have?

    February 9, 2018 Reply
    • Brijesh Gogia Brijesh Gogia

      Yes, I will be publishing more posts on PT in coming weeks.

      February 12, 2018 Reply
Leave a Reply