当前位置:
文档之家› Oracle 9i 数据库性能调优技术-les01
Oracle 9i 数据库性能调优技术-les01
• •
Bad cursor management Bad relational design
– Unnecessary table joins performed – Usually a result of trying to build an object interface to relational storage
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
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-15
Copyright © Oracle Corporation, 2002. All rights reserved.
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-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
• •
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
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
Overview of Oracle9i Database Performance Tuning
Copyright © Oracle Corporation, 2002. All rights respleting 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-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-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.
1-2 Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Questions
•
Who tunes?
– – – – Application designers Application developers Database administrators System administrators