- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1-14
Copyright © Oracle Corporation, 2002. All rights reserved.
Summary
In this lesson, you should have learned how to: • Create a good initial design • Define a tuning methodology • Perform production tuning • Establish quantifiable goals • List tuning problems • Decide between performance and safety
1-8
Copyright © Oracle Corporation, 2002. All rights reserved.
Collect a Baseline Set of Statistics
A baseline set of statistics is used to: • Provide a set of statistics collected when the system was operating within the bounds set • Create a hypothesis about what has changed on the system
1-5
Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Goals
Tuning goals are usually specified in terms of: • Reducing or eliminating waits • Accessing the least number of blocks • Caching blocks in memory • Minimizing response time • Increasing throughput • Increasing load capabilities • Decreasing recovery time • Instance hit percentages
1-6
Copyright © Oracle Corporation, 2002. All rights reserved.
Common Performance Problems
•
Bad session management
– Limits scalability to a point that cannot be exceeded – Makes the system one or two orders of magnitude slower than it should be
1-9
Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Steps for a Production Database
1. 2. 3. 4. 5. 6.
Define the problem. Examine the host system and Oracle statistics. Consider some common performance errors. Build a conceptual model. Implement and measure the change. Check that the bottleneck has been resolved.
1-10
Copyright © Oracle Corporation, 2002. All rights reserved.
Database Server Tuning Methodology
• • • •
Check alert log and trace files for errors. Check the 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-15
Copyright © Oracle Corporation, 2002. All rights reserved.
• •
Bad cursor management Bad relational design
– Unnecessary table joins performed – Usually a result of trying to build an object interface to relational storage
1-13
Copyright © Oracle Corporation, 2002. All rights reserved.
Performance Versus Safety Trade-Offs
Factors that affect performance: • Multiple control files • Multiple redo log members in a group • Frequent checkpointing • Backing up datafiles • Performing archiving • Block check numbers • Number of concurrent users and transactions
1-12
Copyright © Oracle Corporation, 2002. All rights reserved.
Database Server Tuning Methodology
Tune response time: • Analyze system performance in terms of work done (CPU or service time) versus time spent waiting for work (wait time). • Determine which component consumes the greatest amount of the time. • Drill down to tune that component if appropriate.
• •
What to tune? How much tuning?
1-3
Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Phases
Tuning can be divided into different phases: • Application design and programming • Database configuration • Adding a new application to an existing database • Troubleshooting and tuning
Overview of Oracle9i Database Performance Tuning
Copyright © Oracle Corporation, 2002. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following: • Define the roles associated with the database tuning process • Describe the dependencies between tuning in different development phases • Describe service level agreements • Identify tuning goals • Identify common tuning problems • Employ tuning activities during development and production • Balance performance and safety trade-offs
1-2 Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Questions
•
Who tunes?
– – – – Application designers Application developers Database administrators System administrators
1-7
Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Steps During Development
• • • • • •
Tune the design Tune the源自文库application Tune memory Tune I/O Tune contention Tune the operating system
