Oracle Database 21c性能调优2日速成指南

需积分: 10 132 浏览量更新于2024-07-09 收藏 2.88MB PDF 举报

《Oracle® Database 2天性能调优指南》(2Day+PerformanceTuningGuide, 21c 版本 F32092-01)是一份全面而深入的文档，发布于2020年11月。该指南由Oracle公司及其关联机构创作，专为数据库管理员、开发人员和其他IT专业人士设计，旨在帮助他们在短短两天内提升Oracle数据库21c的性能。版权信息表明，这份资料受2007年和2020年的版权保护，并受到相关知识产权法律的严格控制。文档涵盖了广泛的性能调优主题，包括但不限于以下关键知识点： 1. **性能分析基础**：介绍了如何识别和诊断数据库性能瓶颈，如SQL查询优化、索引管理、存储结构等。 2. **内存优化**：讲解了如何有效地管理和分配SGA (System Global Area) 和PGA (Program Global Area)，以提高响应速度和并发处理能力。 3. **数据仓库与OLAP**：针对大型数据仓库环境中的查询优化和维度表设计提供了策略。 4. **并行与分布式处理**：阐述了如何利用Oracle的并行服务器技术和分布式数据库特性来提升性能。 5. **存储技术**：讨论了各种存储选项（如RAC、InnoDB、闪存缓存等）的选择和优化。 6. **实时性能监控**：指导读者如何使用性能视图、警报系统以及诊断工具来实时监控和解决问题。 7. **硬件与操作系统集成**：强调了与服务器硬件、操作系统和网络设备的协同工作，以实现最佳性能。 8. **故障排除与恢复**：包含了应对常见问题和故障的实用技巧，以及如何设计高效的备份和恢复策略。 9. **最佳实践与案例研究**：分享了实际场景中的成功案例，以及避免常见陷阱的最佳做法。 10. **法规遵从性和安全性**：在性能优化的同时，强调了数据安全和合规性的考虑，确保数据库操作符合法规要求。通过这份2天性能调优指南，读者可以快速掌握核心调优技巧，以提升Oracle Database 21c的运行效率和用户体验。然而，必须遵守许可协议的限制，并尊重知识产权，未经许可不得擅自复制或传播文档内容。

Introduction

As an Oracle database administrator (DBA), you are responsible for the performance

of your Oracle database. Tuning a database to reach a desirable performance level

may be a daunting task, especially for DBAs who are new to Oracle Database. Oracle

Database 2 Day + Performance Tuning Guide is a quick start guide that describes how

to perform day-to-day database performance tuning tasks using features provided by

Oracle Diagnostics Pack, Oracle Tuning Pack, and Oracle Enterprise Manager Cloud

Control (Cloud Control).

This chapter contains the following sections:

• About This Guide

• Common Oracle DBA Tasks

• Tools for Tuning the Database

• Accessing the Database Home Page

About This Guide

Before using this guide, you must do the following:

• Be familiar with Oracle Database administration. See Oracle Database

Administrator's Guide for more information.

• Obtain the necessary products and tools described in "Tools for Tuning the

Database".

Oracle Database 2 Day + Performance Tuning Guide is task-oriented. The objective is

to describe why and when tuning tasks need to be performed.

This guide is not an exhaustive discussion of all Oracle Database concepts. For that

type of information, see Oracle Database Concepts.

This guide does not describe basic Oracle Database administrative tasks. For that

type of information, see Oracle Database Administrator's Guide.

The primary interface used in this guide is the Enterprise Manager Cloud Control

console. This guide is not an exhaustive discussion of all Oracle Database

performance tuning features. It does not cover available application programming

interfaces (APIs) that provide comparable tuning options to those presented in this

guide. For this type of information, see Oracle Database Performance Tuning Guide

and Oracle Database SQL Tuning Guide.

Common Oracle DBA Tasks

As an Oracle DBA, you can expect to be involved in the following tasks:

• Installing Oracle software

• Creating Oracle databases

1-1

• Upgrading the database 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

• Monitoring proactively the condition of the database and taking preventive or

corrective actions, as required

• Monitoring and tuning database performance

This 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 19c, Version 19.1 (Enterprise Edition)

Oracle Database 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 Cloud Control

The primary tool to manage the database is Enterprise Manager Cloud Control

(Cloud Control), a web-based interface. After you install the Oracle software,

create or upgrade a database, and configure the network, you can use Cloud

Control to manage the database. In addition, Cloud Control 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 Tuning Pack

Oracle Tuning Pack automates the database application tuning process, thereby

significantly lowering database management costs while enhancing performance

and reliability. Key features of Oracle 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

Chapter 1

Tools for Tuning the Database

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. 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 it might span the system.

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

from the system users and the people in charge of 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 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 by collecting, processing, and maintaining performance statistics

for database problem detection and self-tuning purposes.

2-1

剩余196页未读，继续阅读

juddy10000

粉丝: 1
资源: 14

Oracle Database 21c性能调优2日速成指南

database-2-day-performance-tuning-guide.pdf

2 day+performance tuning guide

Oracle® Database 2 Day + Performance Tuning Guide

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

MySQL Performance Tuning Student Guide

oracle performance tuning

Apress.Expert.Oracle.Database.Architecture.2nd.Edition

oracle参考书2

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

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

最新资源