Performance Tuning Methodology

1
Performance Tuning Methodology

• Approach to Attacking Oracle Performance Problems

2
Objectives

• At the end of this module the student will
  understand the following tasks and concepts.
• Determining if there is a performance problem
• Understand how to find performance problems
• Know what tools are available for finding
  performance problems and begin to know how to use
  them
• Understand how to begin to solve performance
  problems
• Learn what affects system performance and what
  you should look for when configuring your system

3
Oracle Performance Fundamentals

• Determine if there is a problem
• Finding Performance Problems
• Solving Performance Problems

4
Consult the Customer

• Ask the user community how the system seems
• This can be a good indication of a problem
• This can often be unreliable information
• Log user complaints
• The log can be used to determine trends
• The log can be used to document problem queries
• The Log can be used to validate fixes

5
Regular Monitoring

• Regular monitoring can be used to determine long
  term resource usage
• Perform long-term monitoring
• Analyze long-term logs on a regular basis
• Instantaneous monitoring has limited use
• Determine your metrics and monitor them regularly

6
Monitoring and Alerting

• Take advantage of third party tools.
• How well these tools server you depend on how
  well you have configured them for your
  installation.
• Alerts should be set.
• Rules should be set.

7
Performance Tuning Methodology

• How do you tune?
• What methods do you use?

8
Pick a Methodology

• Develop a methodology for Tuning
• Stick with it
• Set Goals
• Keep Records!!

9
Step Method

• Determine Problem
• Formulate Solution / Test
• Anticipate Outcome of Solution or Test
• Implement
• Analyze Result

10
Determine Problem

• Is there a performance problem?
• What kind of problem is it?
• Software
• Hardware
• CPU
• Memory
• I/O

11
Formulate Solution or Test

• Develop a test plan
• One or more solutions
• Prioritize Solutions
• Document
• Create repeatable tests

12
Anticipate Outcome

• Each test should generate a result
• What does it mean?
• Formulate result scenarios

13
Implement

• Try the test or solution
• Test off-line if possible
• Simulate load using load testing software
• Document

14
Analyze Result

• What happened?
• What did it mean?
• Conclusions

15
Tuning Approach

• Look for the obvious
• Tune Application
• Tune Access Paths
• Tune Memory
• Tune I/O
• Tune Resource Contention

16
Using CPU Time as a Metric

• CPU time is a good metric
• Maximum capacity is 100
• Achieving max is not a good thing
• CPU time can be easily monitored

17
An Example of CPU Monitoring
18
Using User Count as a Metric

• Oracle user and/or process count can easily be
  obtained
• Obtained from vsession
• Distinct Users
• SELECT COUNT(DISTINCT(USERNAME)) FROM VSESSION
• Processes
• SELECT COUNT() FROM VSESSION

19
An Example of Monitoring User Count
20
Using Response Time as a Metric

• Excellent representation of system response time
• Tells you what the users are experiencing
• Can be used to validate service level agreement

21
An Example of Monitoring Response Time
22
Statistics Guidelines

• What are your reasons for gathering statistics?
• How can you best achieve those results?
• Types of statistics
• General system performance
• Specific Application or task evaluation
• Sizing
• Capacity Planning

23
General Statistics

• Run statistics for long periods of time
• Daily
• Start in the morning
• End at night
• Look for resource contention
• Look for events
• Look for max usage

24
Specific Application Statistics

• Run statistics during specific phases of the
  application
• Run for short periods of time
• Time the statistics gathering
• Look for specific resource usage

25
Finding Performance Problems

• Perfmon
• Oracle for Windows NT Performance Monitor
• Statspack
• Automatic Workload Repository Reports
• SQL Trace
• Explain Plan
• Third party Tools
• Instrumented Applications

26
Perfmon

• Windows Performance Monitor
• CPU statistics
• I/O statistics
• Memory statistics

27
Oracle Counters for Windows Performance Monitor

• Monitor Database Objects
• CPU Usage
• Buffer Cache
• Redo Log buffer
• Data Dictionary Cache
• Library Cache
• Data file I/O

28
Statspack

• Standard Performance Package
• Similar to older UTLBSTAT/UTLESTAT package
• Install
• _at_?\rdbms\admin\spcreate
• Run
• SQLgt CONNECT perfstat/perfstat
• SQLgt EXECUTE statspack.snap

29
Statspack

• Create the report with
• _at_spreport
• Tell it which snapshots to use.
• A report is created.

30
Automatic Workload Repository Features

• Statspack on Steroids (10g only)
• Collects stats every 60 minutes
• Kept for a week then purged
• A collection of performance stats
• A new background process MMON
• Resides in new sysaux tablespace

31
Automatic Workload Repository Reports

• Two reports provided by Oracle
• awrrpt.sql
• awrrpti.sql
• Similar to Statspack report
• Reports can be generated with scripts or through
  a GUI interface
• Optional HTML or plain text formats

32
AWR Reports
33
SQL Trace

• Used to trace sessions within Oracle.
• Look for resource utilization and rows
• This is done for you in many third party tools.

34
Explain Plan

• Decomposes the execution plan.
• Shows you the objects used.
• Shows you cost of plan.

35
Third Party Tools

• There are a number of excellent third party tools
• Veritas
• BMC
• Quest
• Others

36
Instrumented Applications

• Additions to applications or stored procedures to
  log statistics
• Start time
• End time
• Parameters
• Duration
• Other
• Extremely valuable in determining system
  characteristics
• Extremely valuable for determining the affect of
  changes

37
Instrumented Applications
38
Solving Performance Problems

• Tuning SQL Statements
• Adding Memory
• Adding More Disk Drives
• Reconfiguring the SAN
• Reconfiguring RAID

39
Tuning SQL Statements

• Critical to overall Oracle performance
• Statements should be tuned to reduce excess
  unnecessary processing
• Excessive I/Os
• Excessive processing
• Excessive memory usage

40
SQL Tuning Tips

• Use indexes wisely
• Can be used to reduce I/Os
• Will speed up query performance
• Will reduce INSERT, UPDATE, DELETE performance
• Must be used correctly

41
SQL Tuning Tips

• Join types involve tradeoffs
• Nested Loops Joins
• Lots of I/O
• Less CPU
• Hash Joins
• Fewest I/Os
• High CPU usage
• Parallelize better
• Merge Joins
• Large Sorts and memory usage
• High CPU usage

42
Adding Memory

• Always a good idea
• More memory can be used for Buffer Cache thus
  reducing I/Os
• More cache does not necessarily help
• Can be controlled via KEEP andRecycle Buffer Pools

43
Adding More Disk Drives

• Will help in systems that are I/O bound
• Add more drives in order to reduce the IOPS per
  disk drive
• Keep IOPS per disk drive within prescribed limits
• Dont overdrive specific drives
• Monitor on a regular basis

44
Reconfiguring the SAN

• Adding more LUNS
• Is very painful in a cluster
• Must be done carefully
• Backup everything before starting
• Allocate sufficient time

45
Reconfiguring RAID Type

• All data is lost
• Very time consuming
• Backup all data before beginning

46
System Configuration

• Configuring the system properly is critical.
• A poorly configured system cannot be affectively
  tuned.
• Once it is done, it is hard to fix.

47
System Configuration

• Choosing the Right Hardware
• CPUs
• Disks
• Memory
• Database Design

48
CPU Performance Factors

• Speed of CPUs
• Clock Speed
• Pipelining
• CPU Cache
• Scalability
• Performance

49
CPU Scalability

• What Affects Scalability?
• Software
• Oracle Scales Extremely Well
• Hardware
• CPU Caches
• CPU/Memory Bus Bandwidth

50
Memory

• Speed of Memory Access
• Memory Bus Bandwidth
• Amount of Memory

51
Oracle Memory

• DB Block Buffers
• Shared Pool
• Sort Area
• Hash Area

52
Oracle Memory

• OLTP
• DB Block Buffers
• Shared Pool
• DSS
• Shared Pool (parallel query)
• Sort Area
• Hash Area

53
DB Block Buffers

• Used to cache frequently used data
• Amount (MB) DB_BLOCK_BUFFERS DB_BLOCK_SIZE
• Most important for OLTP
• Try to get cache hit ratio gt 90

54
Shared Pool

• Used for
• Data Dictionary
• Library Cache
• Parallel Query Buffers
• Make sure that you have enough

55
Sort Area

• Use Oracle Counters (Perfmon)
• Look for
• Sorts (disk)
• Number of sorts done on disk
• Sorts (memory)
• Number of sorts all in memory
• Sorts (rows)
• Total number of rows sorted

56
Hash Area

• Amount of memory used for hash joins
• More memory less temporary space better
  performance
• Sorry no statistics

57
Memory Swapping

• Dont exceed available system memory!
• Causes swapping to disk
• Kills performance
• Reduce your Oracle memory usage to fit the SGA,
  PGA, and user processes in main memory

58
I/O Considerations

• Database size
• How much space do you need?
• Performance
• How many disks do you need?
• Fault Tolerance
• Importance
• Performance

59
Other I/O considerations

• Manual method
• Adjust PCTFREE and PCTUSED
• Check for Freelist contention
• Check VWAITSTAT for contention on DATA BLOCKS
• Check VSYSTEM_EVENT for BUFFER BUSY WAITS
• Add more freelists
• In Oracle 10g, use Automatic Segment Space
  Management

60
Oracle I/O Enhancing Features

• Multiblock Read
• Read larger chunks
• Range Partitioning
• Reduce the amount of data you access
• Parallel Query Option
• Keep the CPUs Busy
• Block Size

61
Review

• Using Explain Plan, you have determined that some
  of the most problematic queries on your system
  are using Nested Loop Joins. I/O speed is
  average to slow on your SAN. Meantime, your
  multi-processor server seems to be using only one
  CPU, with the others idle. What approaches could
  you use to improve performance?
• Users are complaining that everything is slow,
  particularly your companys home grown ERP
  application, which also runs on the biggest
  database. You have complained for some time now
  that the ERP code is poorly written. In
  addition, your SAN disks are slow with no
  controller cache. The database server has four
  fast XEON chips, but you only have 4GB of RAM.
  Given the information you have now, where do you
  think you should spend time and money?
• Get faster disks with large controller cache
• Increase RAM on the database server to 8GB
• Rewrite and optimize the ERP code
• Add more CPUs to the database server

62
Summary

• First determine if a problem exists
• Dont rule out anything
• Application
• Software
• RDBMS
• Hardware
• Monitor regularly
• Tune SQL Statements to reduce resource usage