【11g Performance 】性能调优的概述

最新推荐文章于 2024-02-29 14:23:41 发布

回到未来望过去

最新推荐文章于 2024-02-29 14:23:41 发布

阅读量336

点赞数

分类专栏： oracle

oracle 专栏收录该内容

186 篇文章 7 订阅

订阅专栏

1 Performance Tuning Overview

本章介绍性能调优，并包含以下部分:

1.1 Introduction to Performance Tuning

本指南提供了关于调优Oracle数据库性能的信息。本指南讨论的主题包括:

1.1.1 Performance Planning

在继续本指南的其他部分之前，您应该完成第二部分“性能计划”。基于多年的设计和性能经验，Oracle设计了一种性能方法。本部分描述可以显著提高系统性能的活动，并包含以下主题:

1.1.2 Instance Tuning

Part III, "Optimizing Instance Performance" discusses the factors involved in the tuning and optimizing of an Oracle database instance.

在考虑实例调优时，在数据库的初始设计中要注意避免可能导致性能问题的瓶颈。此外，你必须考虑:

为数据库结构分配内存
确定数据库不同部分的I/O需求
优化操作系统以获得数据库的最佳性能

安装和配置数据库实例之后，必须在数据库运行时监视它，以检查与性能相关的问题

1.1.2.1 Performance Principles

性能调优需要与系统初始配置不同的方法(尽管是相关的)。配置系统涉及以有序的方式分配资源，以便初始系统配置能够正常工作。
调优是通过确定最重要的瓶颈并做出适当的更改来减少或消除瓶颈的影响来驱动的。通常，调优是被动执行的，要么在系统处于预生产阶段，要么在系统运行之后。

1.1.2.2 Baselines

最有效的调优方法是建立一个性能基准，如果出现性能问题，可以使用它进行比较。大多数数据库管理员(dba)都非常了解他们的系统，并且能够很容易地确定高峰使用周期。例如，高峰期可以在上午10点到下午12点之间，也可以在下午1点半到3点之间。这可能包括一个批处理窗口，从午夜12点到早上6点。

重要的是确定站点的这些高峰时期，并安装一个监视工具来收集这些高负载时期的性能数据。最优情况下，应该从应用程序在QA周期的初始测试阶段开始配置数据收集。否则，这应该在系统首次投入生产时配置。

Ideally, baseline data gathered should include the following:

Application statistics (transaction volumes, response time)
Database statistics
Operating system statistics
Disk I/O statistics
Network statistics

在自动工作负载存储库中，基线是由一系列快照标识的，这些快照被保存下来，以便将来进行比较。 See "Overview of the Automatic Workload Repository".

1.1.2.3 The Symptoms and the Problems

性能调优的一个常见陷阱是将问题的症状误认为实际问题本身。重要的是要认识到，许多性能统计数据显示症状，而识别症状并不是实现补救措施的足够数据。例如:

Slow physical I/O

通常，这是由配置不良的磁盘造成的。然而，这也可能是由于调优不佳的SQL在这些磁盘上发出大量不必要的物理I/O造成的。
Latch contention

通过重新配置实例，闩锁争用很少是可调的。相反，锁存争用通常通过应用程序更改来解决。
Excessive CPU usage

过多的CPU使用通常意味着系统上几乎没有空闲CPU。这可能是由于系统大小不够、SQL语句未调优或应用程序效率低下造成的。

1.1.2.4 When to Tune

有两种不同类型的调优:

1.1.2.4.1

主动监控

Proactive monitoring usually occurs on a regularly scheduled interval, where several performance statistics are examined to identify whether the system behavior and resource usage has changed. Proactive monitoring can also be considered as proactive tuning.

Usually, monitoring does not result in configuration changes to the system, unless the monitoring exposes a serious problem that is developing. In some situations, experienced performance engineers can identify potential problems through statistics alone, although accompanying performance degradation is usual.

Experimenting with or tweaking a system when there is no apparent performance degradation as a proactive action can be a dangerous activity, resulting in unnecessary performance drops. Tweaking a system should be considered reactive tuning, and the steps for reactive tuning should be followed.

Monitoring is usually part of a larger capacity planning exercise, where resource consumption is examined to see changes in the way the application is being used, and the way the application is using the database and host resources.

1.1.2.4.2

消除瓶颈

Tuning usually implies fixing a performance problem. However, tuning should be part of the life cycle of an application—through the analysis, design, coding, production, and maintenance stages. Often, the tuning phase is left until the database is in production. At this time, tuning becomes a reactive process, where the most important bottleneck is identified and fixed.

Usually, the purpose for tuning is to reduce resource consumption or to reduce the elapsed time for an operation to complete. Either way, the goal is to improve the effective use of a particular resource. In general, performance problems are caused by the overuse of a particular resource. The overused resource is the bottleneck in the system. There are several distinct phases in identifying the bottleneck and the potential fixes. These are discussed in the sections that follow.

Remember that the different forms of contention are symptoms that can be fixed by making changes in the following places:

Changes in the application, or the way the application is used
Changes in Oracle
Changes in the host hardware configuration

Often, the most effective way of resolving a bottleneck is to change the application.

1.1.3 SQL Tuning

Part IV, "Optimizing SQL Statements" of this guide discusses the process of tuning and optimizing SQL statements.

许多应用程序程序员认为SQL是一种消息传递语言，因为发出查询并返回数据。然而，客户端工具常常生成低效的SQL语句。因此，对数据库SQL处理引擎有很好的理解是编写最佳SQL的必要条件。对于高事务处理系统尤其如此。

通常，OLTP应用程序发出的SQL语句一次操作相对较少的行。如果索引可以指向所需的准确行，那么Oracle数据库可以构造一个精确的计划，以便通过最短的可能路径高效地访问这些行。在决策支持系统(DSS)环境中，选择性不那么重要，因为它们通常访问表的大部分行。在这种情况下，全表扫描是常见的，甚至不使用索引。本书主要关注oltp类型的应用程序。有关决策支持系统和混合环境的详细信息, see the Oracle Database Data Warehousing Guide.

1.1.3.1 Query Optimizer and Execution Plans

当在Oracle数据库上执行SQL语句时，查询优化器在考虑与引用的对象和查询中指定的条件相关的许多因素后，确定最有效的执行计划。这种确定是处理任何SQL语句的一个重要步骤，会极大地影响执行时间。

在评估过程中，查询优化器检查收集到的系统统计信息，以确定最佳数据访问路径和其他考虑事项。您可以使用插入到SQL语句中的提示覆盖查询优化器的执行计划。

1.2 Introduction to Performance Tuning Features and Tools

有效的数据收集和分析对于识别和纠正性能问题至关重要。Oracle Database提供了几个工具，允许性能工程师收集关于数据库性能的信息。除了收集数据，Oracle数据库还提供了一些工具来监视性能、诊断问题和调优应用程序

Oracle数据库的采集和监控功能主要是自动的，由Oracle后台进程管理。要启用自动统计信息收集和自动性能特性，必须将STATISTICS_LEVEL初始化参数设置为TYPICAL或ALL。您可以使用Oracle Enterprise Manager或api和视图管理和显示收集和调优工具的输出。为了便于使用并利用其众多的自动化监视和诊断工具，建议使用Oracle Enterprise Manager数据库控件。

1.2.1 Automatic Performance Tuning Features

The Oracle Database automatic performance tuning features include:

Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. See "Overview of the Automatic Workload Repository".
Automatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with the Oracle database. See "Overview of the Automatic Database Diagnostic Monitor".
SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL statements without modifying any statements. See "Tuning Reactively with SQL Tuning Advisor".
SQLAccess Advisor provides advice on materialized views, indexes, and materialized view logs. See "Automatic SQL Tuning Features"and "Overview of SQL Access Advisor" for information about SQLAccess Advisor.
End-to-End Application tracing identifies excessive workloads on the system by specific user, service, or application component. See "End-to-End Application Tracing".
Server-generated alerts automatically provide notifications when impending problems are detected. See Oracle Database Administrator's Guide to learn how to monitor the operation of the database with server-generated alerts.
Additional advisors that can be launched from Oracle Enterprise Manager, such as memory advisors to optimize memory for an instance. The memory advisors are commonly used when automatic memory management is not set up for the database. Other advisors are used to optimize mean time to recovery (MTTR), shrinking of segments, and undo tablespace settings. To learn about the advisors available with Oracle Enterprise Manager, see Oracle Database 2 Day + Performance Tuning Guide.
The Database Performance page in Oracle Enterprise Manager displays host, instance service time, and throughput information for real time monitoring and diagnosis. The page can be set to refresh automatically in selected intervals or manually. To learn about the Database Performance page, see Oracle Database 2 Day + Performance Tuning Guide.

1.2.2 Additional Oracle Database Tools

本节描述用于确定性能问题的其他Oracle数据库工具。

1.2.2.1 V$ Performance Views

V$视图是所有Oracle数据库性能调优工具使用的性能信息源。V$视图基于实例启动时初始化的内存结构。内存结构和表示它们的视图由Oracle数据库在实例的生命周期中自动维护. See Chapter 10, "Instance Tuning Using Performance Views" for information diagnosing tuning problems using the V$ performance views.