Trang chủ » Công Nghệ Thông Tin » Hệ thống thông tin quản lý

16 trang

57 lượt xem

Overview of Oracle9i Database Performance Tuning

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 developmentand production Balance performance and safety trade-offs

trinh02

Overview of Oracle9i Database Performance Tuning

1

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

Identify tuning goals Identify common tuning problems

different development phases • Describe service level agreements • • • Employ tuning activities during development

and production

1-2

• Balance performance and safety trade-offs

Tuning Questions

– Application designers – Application developers – Database administrators – System administrators

• Who tunes?

1-3

• What to tune? • How much tuning?

1-4

Tuning Phases

1-5

Tuning can be divided into different phases: • Application design and programming • Database configuration • Adding a new application to an existing database • Troubleshooting and tuning

Tuning Goals

1-6

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

Common Performance Problems

– Limits scalability to a point that cannot be

• Bad session management

exceeded

– Makes the system one or two orders of magnitude

slower than it should be

– Unnecessary table joins performed – Usually a result of trying to build an object interface

• Bad cursor management • Bad relational design

to relational storage

1-7

Tuning Steps During Development

1-8

• • • • • • Tune the design Tune the application Tune memory Tune I/O Tune contention Tune the operating system

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

1-9

the system

Tuning Steps for a Production Database

Implement and measure the change.

1-10

1. Define the problem. 2. Examine the host system and Oracle statistics. 3. Consider some common performance errors. 4. Build a conceptual model. 5. 6. Check that the bottleneck has been resolved.

1-11

Database Server Tuning Methodology

• Check alert log and trace files for errors. • Check the parameter file for any diagnostic or

inappropriate parameter setting.

•

1-12

• 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.

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.

1-13

• Drill down to tune that component if appropriate.

Performance Versus Safety Trade-Offs

Frequent checkpointing

1-14

Factors that affect performance: • Multiple control files • Multiple redo log members in a group • • Backing up datafiles • Performing archiving • Block check numbers • Number of concurrent users and transactions

Summary

List tuning problems

1-15

In this lesson, you should have learned how to: • Create a good initial design • Define a tuning methodology • Perform production tuning • Establish quantifiable goals • • Decide between performance and safety

1-16