优化指南：Oracle 11gR2 数据库性能调优实战

需积分: 10 5 浏览量更新于2024-07-21 1 收藏 8.22MB PDF 举报

"Oracle Database 11gR2 Performance Tuning Cookbook" 是一本面向数据库管理员（DBAs）和开发者的实战指南，旨在帮助他们理解和优化Oracle数据库性能的各个方面。本书聚焦于解决性能问题，从应用设计、开发到实现高性能应用程序，再到部署细节，提供了超过80个针对Oracle Database 11g R2性能优化的实用方案。在Oracle Database 11g R2中，性能调优是确保系统高效运行的关键任务。本书首先介绍了性能调优的重要性，因为即使是最成功的应用也可能面临性能挑战。作者Ciro Fiorillo通过快速节奏的章节，引导读者在问题出现之前采取预防措施，并在遇到问题时能准确诊断和解决。书中的内容涵盖了以下关键知识点： 1. **应用设计与开发**：强调了从一开始就考虑性能的重要性，包括选择正确的数据结构、索引策略、查询优化以及存储过程的编写等。 2. **SQL优化**：探讨了如何分析和优化SQL查询以减少执行时间，包括使用Explain Plan进行性能分析，调整JOIN操作，以及处理复杂子查询。 3. **索引管理**：讲解了创建、监控和调整索引的技巧，以提高查询性能，同时避免过度索引导致的负面影响。 4. **内存配置**：讨论了SGA（System Global Area）和PGA（Program Global Area）的优化，包括设置合适的缓冲区、池大小和内存分配策略。 5. **数据库架构**：涵盖了表分区、表空间管理和物理存储结构优化，以提升数据访问速度和存储效率。 6. **并行执行**：解释了并行查询和并行服务器的原理，以及如何调整并行度以平衡资源消耗和性能提升。 7. **数据库监控与诊断**：介绍了使用AWR（Automatic Workload Repository）、ASH（Active Session History）和其他监控工具来识别性能瓶颈。 8. **数据库实例与进程调优**：深入分析了实例参数调整，如后台进程的配置，以提高数据库响应时间和整体吞吐量。 9. **数据库安全与权限**：虽然主要关注性能，但书中也提到了权限管理和安全性对性能的影响，以及如何避免不必要的性能损耗。 10. **数据库升级与迁移**：探讨了在不同版本之间迁移数据库时如何保持性能稳定，以及升级后的性能调优策略。 11. **性能测试与基准**：教导读者如何建立性能测试环境，制定基准测试，以便在生产环境中实施性能改进时有参考依据。 "Oracle Database 11gR2 Performance Tuning Cookbook" 是一个全面的性能优化资源，无论你是初学者还是经验丰富的DBA，都能从中受益，提升你对Oracle数据库性能调优的理解和实践能力。

Table of Contents

V$SQL_PLAN 495

V$SQLAREA 496

V$STATNAME 496

V$SYSSTAT 497

V$SYSTEM_EVENT 498

V$TEMPFILE 498

V$TEMPSTAT 499

V$WAITSTAT 499

X$BH 500

Appendix B: A Summary of Oracle Packages

Used for Performance Tuning 501

DBMS_ADDM 501

DBMS_ADVISOR 502

DBMS_JOB 502

DBMS_LOB 503

DBMS_MVIEW 503

DBMS_OUTLN 503

DBMS_OUTLN_EDIT 504

DBMS_SHARED_POOL 504

DBMS_SPACE 505

DBMS_SPM 505

DBMS_SQL 505

DBMS_SQLTUNE 506

DBMS_STATS 506

DBMS_UTILITY 507

DBMS_WORKLOAD_REPOSITORY 507

Index 509

www.it-ebooks.info

Preface

To increase the throughput and availability we will try to:

f Increment hit ratios

f Decrease system memory utilization

f Eliminate paging and swapping

f Reduce recovery time, decreasing the Mean Time To Recovery (MTTR)

f Increase load balancing (distributing data les to different disks) to reduce I/O times

f Increase scalability

Before starting a tuning session, we have to dene which are the goals, in terms of SLA, or

dene precise and measurable objectives. So at the end of the tuning process, we will know

if we have reached the expected results. We will work to reduce the workload—so the same

task will consume less resources, allowing other tasks to use those resources—and to

minimize the response time.

In this book, we will nd many recipes that can help us reach these goals. Have a good read!

What this book covers

Chapter 1, Starting with Performance Tuning will show how to set up the example database,

how to adopt a performance tuning process that can help in solving performance problems,

and how to collect and analyze data from Oracle Database using various methods.

Chapter 2, Optimizing Application Design presents the most common application design

issues that prevent an application from running without performance issues. You will see

how to improve database performance by sharing reusable code and by reducing the

number of requests to the database by using various database objects.

Chapter 3, Optimizing Storage Structures will show how to optimize the use of different

database storage structures, presenting the optimal use for tables, clusters, indexes, and

partitioning. You will see how to choose the appropriate structure to improve access time to

your data, also analyzing the possible drawbacks in the operations that modify the data.

Chapter 4, Optimizing SQL Code is focused on SQL code optimization. Throughout the

chapter you will nd many methods to diagnose and solve typical performance problems

caused by poorly written SQL code. You will nd answers on how (and when) to avoid full

table scans, how to use indexes, bulk operations and arrays, join and subquery optimization.

You will also see how to trace SQL activity to diagnose problems.

Chapter 5, Optimizing Sort Operations will show the importance of optimizing sort operations

to achieve better performance even when you don’t see any explicit sort operations in your

SQL code. In this chapter, we will see the difference between in-memory and on-disk sort,

how an index can improve the performance by reducing or avoiding sort operations, how to

perform top-n queries, and how to use aggregate functions, and the use of set operations.

www.it-ebooks.info

Preface

Chapter 6, Optimizing PL/SQL Code will show how to optimize PL/SQL code in stored

procedures, triggers, and user-dened functions. You will see the advantages of using bulk-

collect and array processing, native compilation and function result cache.

Chapter 7, Improving the Oracle Optimizer is focused on how to help the Oracle Optimizer

in choosing the best execution plan using various tools, tricks, and tips, to obtain better

performance. You will see the use of hints, statistics, histograms, stored outlines, adaptive

cursor sharing, SQL tuning sets, and SQL baselines.

Chapter 8, Other Optimizations will show how to use Client Side Result Cache, parallel SQL,

CREATE TABLE AS SELECT, and direct path inserting to optimize performance in both queries

and DML operations. You will also see how to use SQL*Loader and Data Pump to load data

into your Oracle Database.

Chapter 9, Tuning Memory will show how to avoid different memory-related issues, starting

with Operating System paging. You will learn how to properly congure the library cache, the

shared pool, the Program Global Area (PGA), the User Global Area (UGA), and the database

buffer cache.

Chapter 10, Tuning I/O will focus on how to optimize the I/O, learning how to distribute

Oracle les and stripe objects on different disks, what RAID level is better for each type

of database les. The use of asynchronous I/O, checkpoint and redo logs tuning are also

discussed in this chapter.

Chapter 11, Tuning Contention will show how to prevent, detect, and tune contention-related

issues. You will see both lock and latch contention, why they occur, and how to prevent and

solve any issue related to concurrency and contention in your database.

In Appendix A, Dynamic Performance Views you will nd a list of the most used dynamic

performance views; for each view you will nd a brief description and a list of the most useful

elds of the view, to be used as a reference in your daily work.

In Appendix B, A Summary of Oracle Packages Used for Performance Tuning you will nd a brief

summary of Oracle supplied packages useful in order to solve performance-related problems.

What you need for this book

You need an Oracle Database 11gR2 instance available on your system; you can download

Oracle Software from Oracle Technology Network at the following site:

http://www.oracle.com/technetwork/database/enterprise-edition/

downloads/index.html

In Chapter 1 there is a recipe on how to set up the example database to follow the recipes in

this book and to use the code presented.

www.it-ebooks.info

剩余542页未读，继续阅读

vanridin

粉丝: 108
资源: 1187

优化指南：Oracle 11gR2 数据库性能调优实战

Oracle Performance Tuning Guide

Oracle 11G Performance Tuning - sg1

oracle performance tuning guide

linux performance tuning pdf

oracle19c官方文档

学习oracle调优 官方语言翻译一下

Android PPTS (Power and Performance Tuning Suite) 框图

oracle1017

Android PPTS (Power and Performance Tuning Suite) 的组成

oracle IO waits

最新资源

学习oracle调优官方语言翻译一下