Oracle数据库2天性能优化指南

5星 · 超过95%的资源 | 下载需积分: 10 | PDF格式 | 2.52MB | 更新于2024-07-29 | 56 浏览量 | 举报

"Oracle® Database 2 Day + Performance Tuning Guide 是Oracle官方发布的一本针对数据库性能优化的手册，适用于10g Release 2 (10.2) 版本。本书旨在提供简单、自动且逐步的性能调优方法，利用Oracle Diagnostics Pack、Oracle Database Tuning Pack以及Oracle Enterprise Manager等工具进行数据库性能优化。该指南由Immanuel Chan为主要作者，并有多位贡献者共同编写。" 在Oracle数据库的性能调优领域，这本书涵盖了以下几个关键知识点： 1. **Oracle Diagnostics Pack**：这是一个强大的工具集，用于识别和诊断Oracle数据库的性能问题。它包括了Automatic Workload Repository (AWR)报告，用于分析数据库的性能变化，以及ASH (Active Session History)数据，用于深入理解数据库的活动情况。 2. **Oracle Database Tuning Pack**：这个工具包提供了多种性能优化工具，如SQL Tuning Advisor，它能分析SQL语句并提出改进建议，还有DBMS_XPLAN，用于展示查询执行计划，帮助理解查询性能。 3. **Oracle Enterprise Manager**：这是Oracle数据库管理的主要界面，包含了大量的性能监控和管理功能，如实时监控、性能警告、性能诊断和调优建议。 4. **性能调优步骤**：书中详细介绍了性能调优的步骤，包括如何收集性能数据、分析瓶颈、优化SQL语句、调整数据库参数、监控数据库状态等。 5. **性能指标与监控**：学习如何解读重要的性能指标，如redo log写入速度、buffer cache命中率、I/O等待时间等，以及如何使用这些信息来定位性能问题。 6. **数据库架构优化**：包括表空间和数据文件的管理、索引优化、分区策略选择等，这些都是影响数据库性能的关键因素。 7. **资源管理**：了解如何通过ResourceManager分配系统资源，如CPU和内存，以确保高优先级的工作负载得到满足。 8. **SQL优化**：深入理解SQL执行过程，学习如何通过重写SQL、使用绑定变量、优化连接方式等方式提升SQL性能。 9. **数据库配置与参数调优**：理解Oracle数据库的各种初始化参数，学习如何根据工作负载调整参数以提升性能。 10. **数据库安全性与性能**：讨论安全设置对性能的影响，如何在保障安全的同时优化性能。通过这本指南，读者将能够掌握Oracle数据库性能调优的基础知识和实践技巧，有效提升数据库系统的运行效率。

Tools for Tuning the Database

1-4 Oracle Database 2 Day + Performance Tuning Guide

Oracle Database Performance Method 2-1

Oracle Database Performance Method

Performance improvement is an iterative process. Removing the first bottleneck might

not lead to performance improvement immediately, because another bottleneck might

be revealed that has an even greater performance impact on the system. For this

reason, the Oracle performance method is iterative. Accurately diagnosing the

performance problem is the first step towards ensuring that the changes you make to

the system will result in improved performance.

Performance problems generally result from a lack of throughput, unacceptable user

or job response time, or both. The problem might be localized to specific application

modules, or it might span the entire system. Before looking at any database or

operating system statistics, it is crucial to get feedback from the most important

components of the system: the users of the system and the people ultimately paying

for the application. Getting feedback from users makes determining the performance

goal easier, and improved performance can be measured in terms of real business

goals, rather than system statistics.

The Oracle performance method can be applied until performance goals are met or

deemed impractical. This process is iterative, and it is likely that some investigations

will be made that have little impact on the performance of the system. It takes time

and experience to accurately pinpoint critical bottlenecks in a timely manner. The

Automatic Database Diagnostic Monitor (ADDM) implements the Oracle performance

method and analyzes statistics to provide automatic diagnosis of major performance

problems. Using ADDM can significantly shorten the time required to improve the

performance of a system, and it is the method used in this guide.

This chapter discusses the Oracle Database performance method and contains the

following sections:

■ Gathering Database Statistics Using the Automatic Workload Repository

■ Using the Oracle Performance Method

■ Common Performance Problems Found in Oracle Databases

Gathering Database Statistics Using the Automatic Workload Repository

Database statistics provide information about the type of load on the database and the

internal and external resources used by the database. To accurately diagnose

performance problems with the database using ADDM, statistics must be available.

Oracle Database generates many types of cumulative statistics for the system, sessions,

and individual SQL statements. Oracle Database also tracks cumulative statistics about

segments and services. The Automatic Workload Repository (AWR) automates

database statistics gathering by collecting, processing, and maintaining performance

statistics for database problem detection and self-tuning purposes. By default, this

Gathering Database Statistics Using the Automatic Workload Repository

2-2 Oracle Database 2 Day + Performance Tuning Guide

process is repeated every hour, and the result is called an AWR snapshot. The delta

values captured by the snapshot represent the changes for each statistic over the time

period. Statistics gathered by the AWR are queried from memory. The gathered data

can be displayed in both reports and views.

Gathering database statistics using the AWR is enabled by default and is controlled by

the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL

parameter should be set to TYPICAL or ALL to enable statistics gathering by the AWR.

The default setting is TYPICAL. Setting the STATISTICS_LEVEL parameter to BASIC

disables many Oracle Database features, including the AWR, and is not recommended.

For information about the STATISTICS_LEVEL initialization parameter, see Oracle

Database Reference.

The database statistics collected and processed by AWR include:

■ Time Model S tatistics

■ Wait E vent Statistics

■ Session and System Statistics

■ Active Session History Statistics

■ High-Load SQL Statistics

Time Model Statistics

Time model statistics are statistics that measure the time spent in the database by

operation type. The most important time model statistic is database time, or DB time.

Database time represents the total time spent in database calls, and is an indicator of

the total instance workload. As shown in Figure 2–1, database time makes up a portion

of an application's overall user response time.

Figure 2–1 DB Time in Overall User Response Time

Database time is calculated by aggregating the CPU time and wait time of all user

sessions not waiting for idle wait events (nonidle user sessions). For example, a user

session may involve an online transaction made at a bookseller's Web site consisting of

the following actions, as shown in Figure 2–2:

Figure 2–2 DB Time in User Transaction

Query for novels by author

The user performs a search for novels by a particular author. This causes the

application to perform a database query for novels by the author.

Gathering Database Statistics Using the Automatic Workload Repository

Oracle Database Performance Method 2-3

Browse results of query

The user browses the list of novels by the author that are returned and accesses

additional details, such as user reviews and inventory status, about the novels.

This causes the application to perform additional database queries.

3. Add item to cart

After browsing details on the novels, the user decides to add one of the novels to

the shopping cart. This causes the application to make a database call to update

the shopping cart.

4. Checkout

The user completes the transaction by checking out using the address and

payment information previously saved at the bookseller's Web site from a

previous purchase. This causes the application to perform various database

operations to retrieve the user's information, add a new order, update the

inventory, and generate an E-mail confirmation.

For each of these actions, the user makes a request to the database, as represented by

the down arrow in Figure 2–2 on page 2-2. The CPU time spent by the database

processing the request and the wait time spent waiting for the database are considered

DB time, as represented by the shaded areas. Once the request is completed, the results

are returned to the user, as represented by the up arrow. The space between the up and

down arrows represents the total user response time for processing the request, which

contains other components besides DB time, as illustrated in Figure 2–1 on page 2-2.

The objective of database tuning is to reduce the time that users spend performing

actions on the database, or reducing database time. By doing so, the overall response

time of user transactions on the application can be improved.

Wait Event Statistics

Wait events are statistics that are incremented by a session to indicate that it had to

wait for an event to complete before being able to continue processing. When a session

has to wait while processing a user request, the AWR records the wait using one of a

set of predefined wait events that are then grouped into wait classes. Wait event data

reveals various symptoms of problems that might be impacting performance, such as

latch contention, buffer contention, and I/O contention.

Session and System Statistics

A large number of cumulative database statistics are available on a system and session

level. Some of these statistics are collected by the AWR.

Active Session History Statistics

The Active Session History (ASH) statistics are samples of session activity in the

database. Active sessions are sampled every second, and are stored in a circular buffer

in the system global area (SGA). Any session that is connected to the database and

using CPU, or is waiting for an event that does not belong to the idle wait class, is

considered an active session. By capturing only active sessions, a manageable set of

Oracle数据库2天性能优化指南

Oracle 11g性能调优指南（E10822-04）PDF文档

11g Oracle Database 2天性能调优指南（11.2版）详解

Oracle 11g 2 Day性能调优指南概览

Oracle Database 2 Day + Performance Tuning Guide 11g Release 2 (

2 day+performance tuning guide

Oracle Database 2 Day + Data Warehousing Guide 11g -130.pdf

Oracle® Database Performance Tuning Guide

Oracle Database Performance Tuning Guide 12c

Oracle Database Performance Tuning Guide 11gR2

Oracle Database Performance Tuning Guide, 10g Release 2

最新资源