Transcript EDU_D16262_01

Performance Tuning Methodology:
A Review
Copyright © Oracle Corporation, 2002. All rights reserved.
Course Structure
DSI405: Instance Tuning is structured to give a greater
understanding of Oracle internal structures that affect
database performance:
• Lesson 1: Performance Tuning Methodology:
A Review
• Lesson 2: Enqueue Structures
• Lesson 3: Understanding Lock Contention
• Lesson 4: Latch Internals
• Lesson 5: Memory Management
• Lesson 6: The Buffer Cache
• Lesson 7: The Library Cache
• Lesson 8: Redo and Archiving
1-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Lesson Objective
After completing this lesson, you should be able to
review the prescribed methodology for analyzing and
resolving performance-related problems.
1-3
Copyright © Oracle Corporation, 2002. All rights reserved.
Concepts
•
Response time = Service time + Wait time.
– Response time: Total time that is taken to complete
a task
– Service time: Amount of time that is consumed by
the process itself
– Wait time: Time that is taken waiting for service
•
•
1-4
A system is no faster than its worst bottleneck.
Queuing properties are cumulative.
Copyright © Oracle Corporation, 2002. All rights reserved.
Determining the Worst Bottleneck
•
•
•
•
1-5
Start with a symptom.
Determine the bottlenecked component.
View the overall stack as a unit.
Consider its response time as a whole.
Copyright © Oracle Corporation, 2002. All rights reserved.
Understanding Workload Characteristics
Client application
processing time
Network delay
Middle-tier
processing
time
Network delay
Database server
processing
time
Response time = Service time + Wait time
•
•
•
•
1-6
Calculate the service and wait times.
Determine which is the largest.
Focus on bottlenecks in that area.
Consider mixed/heterogeneous workloads.
Copyright © Oracle Corporation, 2002. All rights reserved.
A Simple Methodology
1.
2.
3.
4.
5.
6.
1-7
State the problem.
Find the problem stack.
Drill down and find the worst bottleneck.
Fix it.
Test against the baseline.
Repeat until you hit your goal.
Copyright © Oracle Corporation, 2002. All rights reserved.
1-8
Copyright © Oracle Corporation, 2002. All rights reserved.
State the Problem
•
Gather historical data on past interventions:
– What TARs have been opened?
– What bugs have been filed?
– Understand the work that has been done
to date:
What changes have been implemented?
•
•
1-9
Understand the business impact to both Oracle
and the customer.
Understand the expectations from the customer’s
perspective.
Copyright © Oracle Corporation, 2002. All rights reserved.
1-10
Copyright © Oracle Corporation, 2002. All rights reserved.
State the Problem
•
Define the problem clearly with the customer:
– What does the customer consider to be the
problem?
– Are there multiple problems involved?
– For each problem that is identified, ask questions
that help to isolate the source of the performance
problem.
1-11
Copyright © Oracle Corporation, 2002. All rights reserved.
1-12
Copyright © Oracle Corporation, 2002. All rights reserved.
State the Problem
•
Determine Service Level Agreements (SLA):
– What are the expected transaction rates?
– What are the expected completion rates?
•
1-13
Establish a definition of success.
Copyright © Oracle Corporation, 2002. All rights reserved.
1-14
Copyright © Oracle Corporation, 2002. All rights reserved.
Find the Problem Stack
•
Understand the business process and transaction
mix:
–
–
–
–
•
1-15
What is the application’s purpose?
What is the transaction mix of the system?
What is the transaction profile?
What technology stacks are involved?
Investigate other possible solutions.
Copyright © Oracle Corporation, 2002. All rights reserved.
1-16
Copyright © Oracle Corporation, 2002. All rights reserved.
Find the Problem Stack
•
Define the environment:
– Validate that versions are compatible.
Any issues across products, versions, or thirdparty products?
– Is the configuration supported?
– Is there an audit log of applied patches?
•
Perform “health check” test:
– Identify the component that does not perform as
expected.
1-17
Copyright © Oracle Corporation, 2002. All rights reserved.
1-18
Copyright © Oracle Corporation, 2002. All rights reserved.
Drill Down and Find the Worst Bottleneck
•
Devise a more elaborate test case:
–
–
–
–
•
•
1-19
Does it clearly represent the problem?
Does it run long enough to be measurable?
Does it complete in a reasonable time?
Is it repeatable?
If run on a test system, does the test case
accurately reflect the production environment?
Otherwise, create and run a new test that satisfies
all these questions.
Copyright © Oracle Corporation, 2002. All rights reserved.
Drill Down and Find the Worst Bottleneck
•
Analyze each technology stack to identify the
worst bottleneck:
– If no stack clearly stands out, then default to the
database stack.
•
Data-gathering tool is specific to the stack.
– Database: Statspack/Enterprise Manager
– Network: ping/netstat/tracert
•
•
1-20
Use a realistic sample period.
Gathered data should represent actual activity
during problem performance period.
Copyright © Oracle Corporation, 2002. All rights reserved.
Fix It
•
•
Apply appropriate remedy.
Action will depend on cause:
– Human error
Educate the customer.
– Hardware Failure
Report the issue.
Upgrade the component.
– Oracle Code issue
Log a bug.
1-21
Copyright © Oracle Corporation, 2002. All rights reserved.
Test Against the Baseline
•
•
Review the results of the tuning changes.
Compare with the baseline or SLA:
If the test case performance meets the target, then
deal with the next problem or set of problems.
1-22
Copyright © Oracle Corporation, 2002. All rights reserved.
Repeat Until You Hit Your Goal
•
Has desired result been achieved?
– If it has not, then return to the step “Find the
Problem Stack.”
– If tuning the largest bottleneck does not return any
performance benefit, then it is likely to expose
another bottleneck.
•
Reassess each stack to determine whether to:
– Continue tuning it, or
– Refocus the tuning effort on another stack
1-23
Copyright © Oracle Corporation, 2002. All rights reserved.
Detecting Performance Problems
Checking Different Components
Operating System Stack
Database Server Stack
Processes
Parameter
file
spin
OS
resources
CPU
idle/busy
Oracle
data files
Network
Stack
Memory
swapping/free
Disk
I/O
contention
1-24
Net8
files
Oracle SGA
Trace files
Copyright © Oracle Corporation, 2002. All rights reserved.
Alert log
Database Server Stack
Select the database server stack when:
• The diagnostic questions indicate that the problem
is in the Oracle Server
• The operating system stack points to the database
server stack as the primary resource consumer
• The problem was not clearly related to any stack
(in this case, the database server stack is selected
by default)
1-25
Copyright © Oracle Corporation, 2002. All rights reserved.
Database Server Tuning
Gather information:
• Check alert log and trace files for errors.
• Check parameter file for any diagnostic or
inappropriate parameter setting.
• Check memory, I/O, and CPU usage. Identify
processes with resource usage anomalies.
• Identify and tune SQL statements that are heavy
consumers of CPU or I/O.
1-26
Copyright © Oracle Corporation, 2002. All rights reserved.
Summary
In this lesson, you should have learned about:
• The main steps in the tuning loop:
– Define the test and find the interacting stacks.
– Tune the largest bottleneck.
– Review results and assess changes.
•
1-27
Tuning considerations for the database server
Copyright © Oracle Corporation, 2002. All rights reserved.
1-28
Copyright © Oracle Corporation, 2002. All rights reserved.