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

需积分: 9 189 浏览量更新于2024-07-25 收藏 2.12MB PDF 举报

Oracle Database 2Day+Performance Tuning Guide, 11g Release 2 (11.2) 是一份详尽的官方文档，由Oracle公司发布于2011年11月。该指南专注于Oracle数据库性能调优，主要作者包括David McDermid、Lance Ashdown和Immanuel Chan，而Sushil Kumar也做出了贡献。其他多位专家，如Pete Belknap、Supiti Buranawatanachoke等人，共同撰写了这份技术密集型的资料，旨在帮助用户优化他们的Oracle 11g R2系统性能。文档的核心内容涵盖了广泛的数据库性能调优主题，包括但不限于SQL查询优化、索引设计、内存管理、存储结构、I/O策略、实例管理、会话监控和故障排查等方面。通过深入剖析理论和实践技巧，它旨在帮助数据库管理员提升系统的响应速度、降低延迟，从而实现更高的并发处理能力和整体性能。此外，由于版权保护和许可协议的要求，用户在使用这份文档时必须遵守相关规定，例如限制在授权范围内使用、复制、翻译或传播，并且未经明确许可不得修改、许可或分发。这份文档是Oracle数据库管理员、性能分析师以及高级技术人员不可或缺的学习参考资料，它提供了一套完整的体系，使读者能够根据具体环境定制并实施有效的性能优化策略。阅读这份文档，用户可以了解到如何进行性能基准测试、识别性能瓶颈、执行调整操作，并学习如何使用Oracle的工具如SQL Tuning Advisor、Automatic Workload Repository（AWR）以及Enterprise Manager等来监控和改进数据库性能。对于追求卓越数据库性能的专业人士来说，Oracle Database 2Day+Performance Tuning Guide, 11g Release 2 是一个宝贵的学习和实践平台。

Tools for Tuning the Database

1-2 Oracle Database 2 Day + Performance Tuning Guide

■ Creating an Oracle database

■ Upgrading the database and software to new releases

■ Starting up and shutting down the database

■ Managing the storage structures of the database

■ Managing user accounts and security

■ Managing schema objects, such as tables, indexes, and views

■ Making database backups and performing database recovery, when necessary

■ Proactively monitoring the condition of the database and taking preventive or

corrective actions, as required

■ Monitoring and tuning database performance

Oracle Database 2 Day + Performance Tuning Guide describes how to accomplish the last

two tasks in the preceding list.

Tools for Tuning the Database

The intent of this guide is to allow you to quickly and efficiently tune and optimize the

performance of Oracle Database.

To achieve the goals of this guide, you must acquire the following products, tools,

features, and utilities:

■ Oracle Database 11g Enterprise Edition

Oracle Database 11g Enterprise Edition offers enterprise-class performance,

scalability and reliability on clustered and single-server configurations. It includes

many performance features that are used in this guide.

■ Oracle Enterprise Manager

The primary tool to manage the database is Enterprise Manager, a Web-based

interface. After you install the Oracle software, create or upgrade a database, and

configure the network, you can use Enterprise Manager to manage the database.

In addition, Enterprise Manager provides an interface for performance advisors

and for database utilities, such as SQL*Loader and Recovery Manager (RMAN).

■ Oracle Diagnostics Pack

Oracle Diagnostics Pack offers a complete, cost-effective, and easy-to-use solution

to manage the performance of Oracle Database environments by providing unique

features, such as automatic identification of performance bottlenecks, guided

problem resolution, and comprehensive system monitoring. Key features of Oracle

Diagnostics Pack used in this guide include Automatic Workload Repository

(AWR), Automatic Database Diagnostic Monitor (ADDM), and Active Session

History (ASH).

■ Oracle Database Tuning Pack

Oracle Database Tuning Pack automates the database application tuning process,

thereby significantly lowering database management costs while enhancing

performance and reliability. Key features of Oracle Database Tuning Pack that are

used in this guide include the following:

– SQL Tuning Advisor

This feature enables you to submit one or more SQL statements as input and

receive output in the form of specific advice or recommendations for how to

Oracle Database Performance Method 2-1

2 Oracle Database Performance Method

Performance improvement is an iterative process. Removing the first bottleneck (a

point where resource contention is highest) may 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 toward

ensuring that your changes improve performance.

Typically, performance problems result from a lack of throughput (the amount of work

that can be completed in a specified time), unacceptable user or job response time (the

time to complete a specified workload), or both. The problem might be localized to

specific application modules or span the system.

Before looking at database or operating system statistics, it is crucial to get feedback

from the system users and the people paying for the application. This feedback makes

it easier to set performance goals. Improved performance can be measured in terms of

business goals rather than system statistics.

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

deemed impractical. Because this process is iterative, some investigations may have

little impact on system performance. It takes time and experience to accurately

pinpoint critical bottlenecks quickly. Automatic Database Diagnostic Monitor (ADDM)

implements the Oracle performance method and analyzes statistics to provide

automatic diagnosis of major performance problems. Because ADDM can significantly

shorten the time required to improve the performance of a system, 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.

A cumulative statistic is a count such as the number of block reads. 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. Automatic Workload Repository (AWR) automates database statistics

Gathering Database Statistics Using the Automatic Workload Repository

2-2 Oracle Database 2 Day + Performance Tuning Guide

gathering by collecting, processing, and maintaining performance statistics for

database problem detection and self-tuning purposes.

By default, the database gathers statistics every hour and creates an AWR snapshot,

which is a set of data for a specific time that is used for performance comparisons. The

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

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

can be displayed in both reports and views.

The following initialization parameters are relevant for AWR:

■ STATISTICS_LEVEL

Set this parameter to TYPICAL (default) or ALL to enable statistics gathering by

AWR. Setting STATISTICS_LEVEL to BASIC disables many database features,

including AWR, and is not recommended. To learn more about this initialization

parameter, see Oracle Database Reference.

■ CONTROL_MANAGEMENT_PACK_ACCESS

Set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC to enable automatic

database diagnostic monitoring. Setting CONTROL_MANAGEMENT_PACK_ACCESS

to NONE disables many database features, including ADDM, and is strongly

discouraged. To learn more about this initialization parameter, see Oracle Database

Reference.

The database statistics collected and processed by AWR include:

■ Time Model Statistics

■ Wait Event Statistics

■ Session and System Statistics

■ Active Session History Statistics

■ High-Load SQL Statistics

Time Model Statistics

Time model statistics measure the time spent in the database by operation type. The

most important time model statistic is database time (DB time). Database time

represents the total time spent in database calls by foreground sessions, 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

A session is a logical entity in the database instance memory that represents the state

of a current user login to a database. Database time is calculated by aggregating the

CPU time and wait time of all active sessions (sessions that are not idle). For any

database request, the CPU time is the sum of the time spent working on the request,

while the wait time is the sum of all the waits for various database instance resources.

DB time includes only time spent on client processes and does not include time spent

on background processes such as PMON.

剩余175页未读，继续阅读

santizhou

粉丝: 0
资源: 2

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

HTTP/1.1协议详解

CAT24WC系列串行E2PROM技术规格

矩阵分解的FTN干扰消除新算法：高效低复杂度

MyEclipse6JavaEEDev_PDF.rar

基于FPGA的分数间隔预测判决反馈均衡器的设计与实现.pdf

R-REC-P.311-16-201609-I!!PDF-E.

PDFEdit.exe

半不变量法概率潮流_基于半不变量法的概率潮流计算_概率潮流

matlabMATLAB和Octave的稳态潮流仿真和优化.zip

2023年大学生电子设计竞赛题目列表(本科).rar

最新资源