oracldatabaseperformancetuningguide11gr2.pdf资源-CSDN文库

需积分: 10 93 浏览量 2021-02-02 16:26:37 上传评论收藏 10.86MB PDF 举报

### Oracle Database性能调优指南11g Release 2 (11.2)关键知识点解析 #### 一、文档概述本文档《Oracle Database性能调优指南11g Release 2 (11.2)》（以下简称“指南”）是Oracle官方发布的关于其11g Release 2版本数据库性能优化的专业指南。该文档主要针对Oracle数据库管理员和开发人员，旨在帮助他们了解并掌握如何优化Oracle 11g R2数据库的性能。 #### 二、文档结构与内容概览文档由多个章节组成，每个章节都围绕特定主题展开讨论，并提供详尽的操作指导和最佳实践建议。以下是对文档内容的一个大致概括： 1. **性能调优基础**：介绍了性能调优的基本概念、方法论以及工具，为读者打下坚实的理论基础。 2. **性能监控**：详细介绍了如何使用Oracle提供的各种工具和技术来监控数据库性能，包括AWR（自动工作负载库）、ASH（活动会话历史）等。 3. **SQL语句优化**：探讨了如何编写高效SQL查询，包括索引使用策略、统计信息收集、执行计划分析等方面。 4. **内存管理**：重点讲解了SGA（共享全局区）和PGA（程序全局区）的配置与优化技巧。 5. **I/O优化**：针对磁盘I/O和网络I/O提供了具体的优化方案，如使用ASM（自动存储管理）、调整缓存大小等。 6. **并发控制**：深入分析了锁定机制、事务处理及等待事件，提出了一系列减少竞争的方法。 7. **实例参数调整**：列举了常用的初始化参数及其对性能的影响，并给出了推荐值设置。 8. **应用层调优**：针对应用程序层面的问题提出了改进措施，比如减少往返次数、合理使用连接池等。 #### 三、重要知识点详解 ##### 1. 性能调优方法论 - **目标设定**：明确性能调优的目标，例如响应时间缩短多少、吞吐量提高到何种程度。 - **问题定位**：通过监控工具收集数据，识别性能瓶颈所在。 - **方案设计**：根据问题类型选择合适的优化手段。 - **效果评估**：实施优化后进行测试，验证是否达到预期效果。 ##### 2. SQL语句优化 - **索引使用**：合理创建和维护索引，避免全表扫描。 - **统计信息收集**：定期更新统计信息，确保CBO（基于成本的优化器）做出正确决策。 - **执行计划分析**：利用EXPLAIN PLAN命令查看执行计划，分析是否存在不必要的排序或哈希操作。 - **使用提示**：在必要时使用提示来指导优化器行为。 ##### 3. 内存管理 - **SGA配置**：根据系统资源情况合理分配SGA各部分大小，如DB_CACHE_SIZE、SHARED_POOL_SIZE等。 - **PGA管理**：通过PGA_AGGREGATE_TARGET参数动态调整PGA大小。 - **自动内存管理**：启用自动内存管理模式，让数据库自动管理内存资源。 ##### 4. I/O优化 - **ASM使用**：采用ASM作为存储解决方案，实现高性能的磁盘管理。 - **缓存调整**：适当增大缓冲区缓存，减少物理读取次数。 - **网络配置**：优化网络配置，减少网络延迟。 ##### 5. 并发控制 - **锁定策略**：合理设置锁定级别，避免过度锁定导致的性能下降。 - **事务处理**：优化事务设计，减少长时间持有锁的情况。 - **等待事件分析**：利用V$SESSION_WAIT视图等工具分析等待事件，找到性能瓶颈。 ##### 6. 实例参数调整 - **初始化参数**：根据实际情况调整初始化参数值，如MEMORY_MAX_TARGET、CPU_COUNT等。 - **动态参数更改**：利用ALTER SYSTEM命令动态调整某些参数，无需重启实例。 ##### 7. 应用层调优 - **连接池使用**：合理配置连接池，减少连接建立和释放的时间消耗。 - **批量处理**：尽量将多个操作合并成一个批次执行，减少网络传输次数。 - **异步处理**：对于耗时较长的操作，考虑使用异步方式处理，提高用户体验。 #### 四、总结《Oracle Database性能调优指南11g Release 2 (11.2)》是一份非常全面且实用的手册，不仅涵盖了Oracle数据库性能调优的各个方面，还提供了大量实际案例和最佳实践。通过对上述知识点的学习与实践，数据库管理员和开发人员可以有效地提升Oracle 11g R2数据库的运行效率，满足日益增长的业务需求。

资源推荐

资源详情

资源评论

Oracle® Database

Performance Tuning Guide

11g Release 2 (11.2)

E41573-04

June 2014

Oracle Database Performance Tuning Guide, 11g Release 2 (11.2)

E41573-04

Primary Authors: Immanuel Chan, Lance Ashdown

Contributors: Aditya Agrawal, Hermann Baer, Vladimir Barriere, Mehul Bastawala, Eric Belden, Pete

Belknap, Supiti Buranawatanachoke, Sunil Chakkappen, Maria Colgan, Benoit Dageville, Dinesh Das, Karl

Dias, Kurt Engeleiter, Marcus Fallen, Mike Feng, Leonidas Galanis, Ray Glasstone, Prabhaker Gongloor,

Kiran Goyal, Cecilia Grant, Connie Dialeris Green, Shivani Gupta, Karl Haas, Bill Hodak, Andrew

Holdsworth, Hakan Jacobsson, Shantanu Joshi, Ameet Kini, Sergey Koltakov, Vivekanada Kolla, Paul Lane,

Sue K. Lee, Herve Lejeune, Ilya Listvinsky, Bryn Llewellyn, George Lumpkin, Mughees Minhas, Gary Ngai,

Mark Ramacher, Yair Sarig, Uri Shaft, Vishwanath Sreeraman, Vinay Srihari, Randy Urbano, Amir Valiani,

Venkateshwaran Venkataramani, Yujun Wang, Graham Wood, Khaled Yagoub, Mohamed Zait, Mohamed

Ziauddin

This software and related documentation are provided under a license agreement containing restrictions on

use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your

license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,

transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse

engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is

prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If

you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it

on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software,

any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users

are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and

agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and

adaptation of the programs, including any operating system, integrated software, any programs installed on

the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to

the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management

applications. It is not developed or intended for use in any inherently dangerous applications, including

applications that may create a risk of personal injury. If you use this software or hardware in dangerous

applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other

measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages

caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of

their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks

are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,

Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced

Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products,

and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly

disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle

Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your

access to or use of third-party content, products, or services.

iii

Contents

Preface ............................................................................................................................................................... xv

Audience..................................................................................................................................................... xv

Documentation Accessibility................................................................................................................... xv

Related Documents ................................................................................................................................... xv

Conventions ............................................................................................................................................... xvi

What's New in Oracle Database Performance Tuning Guide? ...................................... xvii

Oracle Database 11g Release 2 (11.2.0.4) New Features in Oracle Database Performance............ xvii

Oracle Database 11g Release 2 (11.2.0.2) New Features in Oracle Database Performance............ xvii

Oracle Database 11g Release 2 (11.2.0.1) New Features in Oracle Database Performance........... xviii

Part I Performance Tuning

1 Performance Tuning Overview

Introduction to Performance Tuning.................................................................................................... 1-1

Performance Planning....................................................................................................................... 1-1

Instance Tuning.................................................................................................................................. 1-1

SQL Tuning......................................................................................................................................... 1-4

Introduction to Performance Tuning Features and Tools ................................................................ 1-4

Automatic Performance Tuning Features ...................................................................................... 1-5

Additional Oracle Database Tools................................................................................................... 1-6

Part II Performance Planning

2 Designing and Developing for Performance

Oracle Methodology ................................................................................................................................ 2-1

Understanding Investment Options..................................................................................................... 2-1

Understanding Scalability...................................................................................................................... 2-2

What is Scalability? ............................................................................................................................ 2-2

System Scalability............................................................................................................................... 2-3

Factors Preventing Scalability .......................................................................................................... 2-4

System Architecture................................................................................................................................. 2-5

Hardware and Software Components ............................................................................................ 2-5

Configuring the Right System Architecture for Your Requirements......................................... 2-7

Application Design Principles.............................................................................................................. 2-9

Simplicity In Application Design.................................................................................................. 2-10

Data Modeling................................................................................................................................. 2-10

Table and Index Design.................................................................................................................. 2-10

Using Views ..................................................................................................................................... 2-12

SQL Execution Efficiency............................................................................................................... 2-13

Implementing the Application ..................................................................................................... 2-14

Trends in Application Development............................................................................................ 2-16

Workload Testing, Modeling, and Implementation ...................................................................... 2-16

Sizing Data ....................................................................................................................................... 2-17

Estimating Workloads.................................................................................................................... 2-17

Application Modeling .................................................................................................................... 2-18

Testing, Debugging, and Validating a Design............................................................................ 2-18

Deploying New Applications ............................................................................................................. 2-19

Rollout Strategies ............................................................................................................................ 2-19

Performance Checklist.................................................................................................................... 2-20

3 Performance Improvement Methods

The Oracle Performance Improvement Method ................................................................................ 3-1

Steps in The Oracle Performance Improvement Method............................................................. 3-2

A Sample Decision Process for Performance Conceptual Modeling.......................................... 3-3

Top Ten Mistakes Found in Oracle Systems.................................................................................. 3-4

Emergency Performance Methods ........................................................................................................ 3-6

Steps in the Emergency Performance Method............................................................................... 3-6

Part III Optimizing Instance Performance

4 Configuring a Database for Performance

Performance Considerations for Initial Instance Configuration .................................................... 4-1

Initialization Parameters ................................................................................................................... 4-1

Configuring Undo Space................................................................................................................... 4-3

Sizing Redo Log Files ........................................................................................................................ 4-3

Creating Subsequent Tablespaces.................................................................................................... 4-4

Creating and Maintaining Tables for Optimal Performance .......................................................... 4-5

Table Compression ............................................................................................................................ 4-5

Reclaiming Unused Space................................................................................................................. 4-6

Indexing Data ..................................................................................................................................... 4-7

Performance Considerations for Shared Servers ............................................................................... 4-7

Identifying Contention Using the Dispatcher-Specific Views .................................................... 4-8

Identifying Contention for Shared Servers..................................................................................... 4-9

5 Automatic Performance Statistics

Overview of Data Gathering.................................................................................................................. 5-1

Database Statistics.............................................................................................................................. 5-2

Operating System Statistics .............................................................................................................. 5-4

Interpreting Statistics......................................................................................................................... 5-7

Overview of the Automatic Workload Repository............................................................................ 5-8

Snapshots............................................................................................................................................. 5-9

Baselines .............................................................................................................................................. 5-9

Adaptive Thresholds ...................................................................................................................... 5-10

Space Consumption........................................................................................................................ 5-12

Managing the Automatic Workload Repository ............................................................................. 5-12

Managing Snapshots....................................................................................................................... 5-13

Managing Baselines ........................................................................................................................ 5-14

Managing Baseline Templates....................................................................................................... 5-17

Transporting Automatic Workload Repository Data................................................................ 5-19

Using Automatic Workload Repository Views .......................................................................... 5-21

Generating Automatic Workload Repository Reports .............................................................. 5-22

Generating Automatic Workload Repository Compare Periods Reports .............................. 5-28

Generating Active Session History Reports................................................................................ 5-34

Using Active Session History Reports ......................................................................................... 5-38

6 Automatic Performance Diagnostics

Overview of the Automatic Database Diagnostic Monitor ............................................................. 6-1

ADDM Analysis ................................................................................................................................. 6-2

Using ADDM with Oracle Real Application Clusters.................................................................. 6-3

ADDM Analysis Results ................................................................................................................... 6-4

Reviewing ADDM Analysis Results: Example.............................................................................. 6-5

Setting Up ADDM ................................................................................................................................... 6-5

Diagnosing Database Performance Problems with ADDM............................................................ 6-6

Running ADDM in Database Mode................................................................................................ 6-7

Running ADDM in Instance Mode.................................................................................................. 6-7

Running ADDM in Partial Mode..................................................................................................... 6-8

Displaying an ADDM Report........................................................................................................... 6-8

Views with ADDM Information........................................................................................................... 6-9

7 Configuring and Using Memory

Understanding Memory Allocation Issues ......................................................................................... 7-1

Oracle Memory Caches ..................................................................................................................... 7-2

Automatic Memory Management ................................................................................................... 7-2

Automatic Shared Memory Management...................................................................................... 7-2

Dynamically Changing Cache Sizes................................................................................................ 7-3

Application Considerations.............................................................................................................. 7-5

Operating System Memory Use....................................................................................................... 7-5

Iteration During Configuration........................................................................................................ 7-6

Configuring and Using the Buffer Cache............................................................................................ 7-6

Using the Buffer Cache Effectively.................................................................................................. 7-7

Sizing the Buffer Cache ..................................................................................................................... 7-7

Interpreting and Using the Buffer Cache Advisory Statistics .................................................. 7-10

Considering Multiple Buffer Pools............................................................................................... 7-11

Buffer Pool Data in V$DB_CACHE_ADVICE ............................................................................ 7-13

Buffer Pool Hit Ratios..................................................................................................................... 7-13

剩余559页未读，继续阅读

评论收藏

内容反馈

impay

粉丝: 0
资源: 4

oracl database performance tuning guide 11gr2.pdf

最新资源