|
1 性能优化概述
This chapter provides an introduction
toperformance tuning and contains the following sections:
本章节给出了性能优化的简介，它包含了一下几个部分。
1.Introduction to Performance Tuning
性能优化的介绍
2.Introduction to Performance Tuning
Features and Tools
性能优化工具和特点的介绍
1.1 性能优化简介
This guide provides information on tuning
anOracle Database system for performance. Topics discussed
in this guide include:
这个小节提供了对一个oracle数据库系统进行性能优化的知识。包括一下几个主题
1.Performance Planning
性能规划
2.Instance Tuning
实例优化
3.SQL Tuning
Sql优化
1.1.1性能规划
Before starting on the instance or SQL
tuningsections of this guide, make sure you have read
Part II, "Performance Planning".
在学习本小节的实例和sql优化部分之前，请先阅读II部分：性能规划
Based on years of designing and performanceexperience,
Oracle has designed a performance methodology. This
brief sectionexplains clear and simple activities that
can dramatically improve systemperformance. It discusses
the following topics:
基于多年的设计和性能方面的经验，oracle已经设计了一套关于性能方面的方法体系。本节描述了能够显著提高系统性能的清晰和简单的方案。该方案涉及到一下主题:
1.Understanding Investment Options
理解能够在调优方面起作用的所有因素
2.Understanding Scalability
理解可扩展性
3.System Architecture
系统架构
4.Application Design Principles
应用设计准则
5.Workload Testing, Modeling, and Implementation
负载测试 建立模型，实现
6.Deploying New Applications
部署新的应用
1.1.2实例优化
Part III, "Optimizing Instance
Performance" ofthis guide discusses the factors
involved in the tuning and optimizing of anOracle database
instance.
本节的III部分，“优化实例性能”讨论涉及到oracle数据库实例调优和优化的各种因素。
When considering instance tuning, care
mustbe taken in the initial design of the database system
to avoid bottlenecks thatcould lead to performance problems.
In addition, you need to consider:
当考虑实例优化的时候，数据库系统初始化设计必须要注意防止一些瓶颈，这些瓶颈可能会带来性能方面的问题。此外你需要考虑一下几点：
1.Allocating memory to database structures
给数据库分配内存
2.Determining I/O requirements of different
parts of the database
确定数据库的不同部分的I/O需求
3.Tuning the operating system for optimal
performance of the database
调优操作系统使数据库系能达到最佳
After the database instance has beeninstalled
and configured, you need to monitor the database as
it is running tocheck for performance-related problems.
在安装和配置数据库实例之后，你需要监控数据库，因为它时刻运行着来检查跟性能相关的一些问题。
1.1.2.1性能准则
Performance tuning requires a different,although
related, method to the initial configuration of a system.
Configuringa system involves allocating resources in
an ordered manner so that the initialsystem configuration
is functional.
对于系统的初始化配置来说 性能调优需要一种与之不同的尽管相关的方法。要以有序的方式分配资源来配置一个系统，这样系统的初始化配置才能起作用。
Tuning is driven by identifying the
mostsignificant bottleneck and making the appropriate
changes to reduce oreliminate the effect of that bottleneck.
Usually, tuning is performedreactively, either while
the system is preproduction or after it is live.
通过找到最重要的瓶颈且做一些合适的变更来减少或消除瓶颈的影响来驱动调优的进行。通常来说，调优是需要有反应来验证的，这个系统要么是在试运行中，要么是已经在生产运行了。
1.1.2.2基线
The most effective way to tune is to
have anestablished performance baseline that can be
used for comparison if aperformance issue arises. Most
database administrators (DBAs) know their systemwell
and can easily identify peak usage periods. For example,
the peak periodscould be between 10.00am and 12.00pm
and also between 1.30pm and 3.00pm. Thiscould include
a batch window of 12.00am midnight to 6am.
最有效的调优方式是建立一个性能的基准线，它能够被用来在性能问题出现的时候做比较。大部分的数据库管理员对他们的系统熟知而且能轻易的判断出系统使用的高峰时段。例如：高峰时段可能是上午10：00到下午12:00，也可能是
下午1.30到3.00.这可能包括凌晨12:00到6:00的批处理窗口。
It is important to identify these peakperiods
at the site and install a monitoring tool that gathers
performance datafor those high-load times. Optimally,
data gathering should be configured fromwhen the application
is in its initial trial phase during the QA cycle.Otherwise,
this should be configured when the system is first in
production.
找到这些这些高峰时段和安装一个能够收集高负载时段性能数据的监控工具都是非常重要的。理想情况下，数据采集应该是从应用的质量保证生命周期中的初始化试验阶段开始配置。否则，应该在系统首次应用于生产时配置。
Ideally, baseline data gathered shouldinclude
the following:
理想情况下，基准线数据应该包括以下内容：
1.Application statistics (transaction
volumes, response time)
应用程序统计信息（交易量，响应时间）
2.Database statistics
数据库统计信息
3.Operating system statistics
操作系统统计信息
4.Disk I/O statistics
磁盘 输入输出 统计信息
5.Network statistics
网络统计信息
In the Automatic Workload Repository,baselines
are identified by a range of snapshots that are preserved
for futurecomparisons. See "Overview of the Automatic
Workload Repository".
在自动工作量知识库里面，基线由一系列的快照形成，这些快照会被保存以便日后比较。祥见“自动工作负载库概述”。
1.1.2.3症状和问题
A common pitfall in performance tuning
is tomistake the symptoms of a problem for the actual
problem itself. It isimportant to recognize that many
performance statistics indicate the symptoms,and that
identifying the symptom is not sufficient data to implement
a remedy.For example:
性能调优的一个常见陷阱是错误的把问题的症状（表现）当成是问题的实质了。认识到大量性能统计信息预示着问题的症状是很重要的。当然，同时认识到症状并不能给问题补救提供充分的数据也非常重要。
Slow physical I/O
缓慢的物理输入输出
Generally, this is caused bypoorly-configured
disks. However, it could also be caused by a significantamount
of unnecessary physical I/O on those disks issued by
poorly-tuned SQL.
通常情况下，这是由较次配置的磁盘引起的。然而他也可能由大量的不必要的磁盘物理输入输出引起，而这些输入输出是由垃圾sql导致。
Latch contention
闩锁争用
Rarely is latch contention tunable byreconfiguring
the instance. Rather, latch contention usually is resolvedthrough
application changes.
通过重新配置实例来实现闩锁争用问题可调是很少的，相反闩锁争用通常通过改变应用程序来解决。
Excessive CPU usage
CPU使用率过高
Excessive CPU usage usually means that
thereis little idle CPU on the system. This could be
caused by an inadequately-sizedsystem, by untuned SQL
statements, or by inefficient application programs.
过高的cpu使用率通常意味着系统会有很少空闲的cpu。这可能由系统规模不适当
或未经优化的sql 或者 低效率的应用程序导致。
1.1.2.4何时调优
There are two distinct types of tuning:
两种不同方式的调优
1.Proactive Monitoring
主动监测
2.Bottleneck Elimination
消除瓶颈
1.1.2.4.1主动监测
Proactive monitoring usually occurs
on aregularly scheduled interval, where a number of
performance statistics areexamined to identify whether
the system behavior and resource usage haschanged. Proactive
monitoring can also be considered as proactive tuning.
主动监测通常发生在一个定期的计划间隔，那里，一定数量的性能统计数据会被检测来确定系统的运行和资源利用率是否已经被改变。主动监测也可以被看作是主动调优。
Usually, monitoring does not result
inconfiguration changes to the system, unless the monitoring
exposes a seriousproblem that is developing. In some
situations, experienced performanceengineers can identify
potential problems through statistics alone, althoughaccompanying
performance degradation is usual.
Experimenting with or tweaking a system
whenthere is no apparent performance degradation as
a proactive action can be adangerous activity, resulting
in unnecessary performance drops. Tweaking asystem should
be considered reactive tuning, and the steps for reactive
tuningshould be followed.
Monitoring is usually part of a largercapacity
planning exercise, where resource consumption is examined
to seechanges in the way the application is being used,
and the way the applicationis using the database and
host resources.
通常，监控并不改变系统的配置，除非他暴露出了正在发生的一些严重的问题。在一些情况下，有经验的性能工程师能够只通过统计信息解决潜在的问题，尽管伴随着性能的下降。当没有显著的性能下降时，主动调整和试验系统是危险的，会造成系统不必要的性能下降。调整系统需要考虑反应调谐，而且反应调优的步骤要被跟踪。
监测通常是更大规模的规划工作中的一部分,那部分里面，资源消耗被检测来观测应用被使用的方式和
数据库及 主机资源的改变。
1.1.2.4.2瓶颈消除
Tuning usually implies fixing a performanceproblem.
However, tuning should be part of the life cycle of
an application—through the analysis, design, coding,production,
and maintenance stages. Oftentimes, the tuning phase
is left untilthe system is in production. At this time,
tuning becomes a reactivefire-fighting exercise, where
the most important bottleneck is identified andfixed.
Usually, the purpose for tuning is to
reduceresource consumption or to reduce the elapsed
time for an operation tocomplete. Either way, the goal
is to improve the effective use of a particularresource.
In general, performance problems are caused by the over-use
of aparticular resource. That resource is the bottleneck
in the system. There are anumber of distinct phases
in identifying the bottleneck and the potentialfixes.
These are discussed in the sections that follow.
调优通常意味着修复性能问题。然而调优应该是应用程序整个生命周期的一部分，包括分析，设计，编码，生产，维护阶段。经常的情况是调优阶段会一直保留到到系统生产运行才离开。那个时候调优已经变成响应的工作，大多数重要的瓶颈已经被识别和修复。
通常情况下，调优的目的是为了减少资源消耗或者减少某项操作完成的耗时。无论哪种方式，目的都是为了提高某个特殊资源的有效利用。一般来说，性能问题都是由某个特殊资源过度使用造成的，那个资源正好是系统的瓶颈。辨识出瓶颈和潜在的问题有许多不同的阶段。这些东西将在以下部分被讨论：
Remember that the different forms ofcontention
are symptoms that can be fixed by making changes in
the followingplaces:
请记住竞争的不同形式是一些症状，这些症状能够被在以下地方做些改变来修复、
1.Changes in the application, or the
way the application is used
应用程序的改变，或者应用程序被使用方式的改变
2.Changes in Oracle
oracle的改变
3.Changes in the host hardware configuration
主机硬件配置的改变。
Often, the most effective way of resolving
abottleneck is to change the application.
经常情况下，解决瓶颈最有效地方式是改变一个应用程序。
1.1.3 SQL 优化
Part IV, "Optimizing SQL Statements"
ofthis guide discusses the process of tuning and optimizing
SQL statements.
本节的IV部分 讨论到了调优的过程 和sql的优化。
Many client/server application programmersconsider
SQL a messaging language, because queries are issued
and data isreturned. However, client tools often generate
inefficient SQL statements.Therefore, a good understanding
of the database SQL processing engine isnecessary for
writing optimal SQL. This is especially true for high
transactionprocessing systems.
许多客户端和服务端的应用程序开发者把sql当做是一种消息语言，因为查询发起后就会返回结果数据。然而客户端工具常常会产出一些低效率的sql。因此，对数据库sql处理工作机制有个很好的理解对写出最优的sql是必要的。这对高事物处理系统尤其管用。
Typically, SQL statements issued by
OLTPapplications operate on relatively few rows at a
time. If an index can point tothe exact rows that are
required, then Oracle can construct an accurate plan
toaccess those rows efficiently through the shortest
possible path. In decisionsupport system (DSS) environments,
selectivity is less important, because theyoften access
most of a table's rows. In such situations, full table
scans arecommon, and indexes are not even used. This
book is primarily focussed onOLTP-type applications.
For detailed information on DSS and mixed environments,see
the Oracle Database Data Warehousing Guide.
通常情况下，由联机事务处理系统发起的sql语句在同一时间会操作相对少的行。如果一个索引能够定位它所需要的准确行，那么oracle能够通过可能的最短的路径来建立一个准确的计划来取到那些行。在决策支撑系统环境当中，查询是次要的，因为他们总是要获取表中的大部分行。。在这个情况下全表扫描是普遍的，索引甚至可能都没被用到。这里主要的关注点都在联机事务处理系统。若要获得决策支撑系统和混合环境更详细的信息，请看
oracle数据库数据仓库指南。
1.1.3.1查询优化器和执行计划
When a SQL statement is executed on
an Oracledatabase, the Oracle query optimizer determines
the most efficient executionplan after considering many
factors related to the objects referenced and theconditions
specified in the query. This determination is an important
step inthe processing of any SQL statement and can greatly
affect execution time.
当一个sql语句在oracle数据库上执行的时候，在考虑过涉及到的若干对象的许多相关因素和查询时的一些具体情况后oracle查询优化器会决定哪个是最高效的执行计划。这个决定是任何sql语句执行过程的重要一步，而且对执行时间影响很大。
During the evaluation process, the queryoptimizer
reviews statistics gathered on the system to determine
the best dataaccess path and other considerations. You
can override the execution plan ofthe query optimizer
with hints inserted in SQL statement.
在评估期间，查询优化器会检查系统收集的统计信息来决定最好的取数据途径和其他考虑。你可以在sql语句中插入hint提示来覆盖查询优化器的执行计划。
1.2性能调优特性和工具
Effective data collection and analysis
isessential for identifying and correcting performance
problems. Oracle providesa number of tools that allow
a performance engineer to gather informationregarding
database performance. In addition to gathering data,
Oracle providestools to monitor performance, diagnose
problems, and tune applications.
有效数据的收集分析对找出并改正性能问题是很必要的。就数据库的性能，oracle提供了大量的工具，这些工具允许性能调优工程师收集信息.除了收集数据，oracle提供了一些工具来监测性能，诊断问题和调优应用程序。
The Oracle gathering and monitoring
featuresare mainly automatic, managed by an Oracle background
processes. To enableautomatic statistics collection
and automatic performance features,the STATISTICS_LEVEL
initialization parameter must be setto TYPICAL or ALL.
You can administer and display the output ofthe gathering
and tuning tools with Oracle Enterprise Manager, or
with APIs andviews. For ease of use and to take advantage
of its numerous automated monitoringand diagnostic tools,
Oracle Enterprise Manager Database Control isrecommended.
oracle收集和监控特性主要都是自动的，被oracle后台进程管理。为了使统计收集和性能功能自动化，统计级别这个初始化参数必须被设置为典型或完全。你可以用oracle企业管理器或者API或者视图来展示和管理统计信息收集和调优工具的输出结果。为了更加方便的使用和利用大量的自动监测诊断工具，推荐使用oracle企业管理器数据库控制。
1.2.1自动性能调优特性
The Oracle automatic performance tuningfeatures
include:
oracle 自动性能调优特性包括以下
1.Automatic Workload Repository (AWR)
collects, processes, and maintains performance statistics
for problem detection and self-tuning purposes. See
"Overview of the Automatic Workload Repository".
自动工作信息库收集，加工和维护性能统计信息来检测问题和自我优化。详见自动负载信息库概论。
2.Automatic Database Diagnostic Monitor
(ADDM) analyzes the information collected by the AWR
for possible performance problems with the Oracle database.
See "Automatic Database Diagnostic Monitor".
自动数据库诊断和监测工具针对oracle数据库可能的性能问题分析了AWR收集的信息。详见自动数据库诊断监控。
3.SQL Tuning Advisor allows a quick
and efficient technique for optimizing SQL statements
without modifying any statements. See "SQL Tuning
Advisor".
sql优化建议器 提供了不修改sql语句的一个快速有效的sql优化技术。详见
sql优化建议器。
4.SQLAccess Advisor provides advice
on materialized views, indexes, and materialized view
logs. See "Automatic SQL Tuning Features"
and "Overview of the SQL Access Advisor in the
DBMS_ADVISOR Package" for information on SQLAccess
Advisor.
sql访问建议器 提供了在物化视图，索引，物化视图日志上的一些建议。 请阅读自动sql优化特性和数据库建议包里的sql访问建议器概述。
5.End to End Application tracing identifies
excessive workloads on the system by specific user,
service, or application component. See "End to
End Application Tracing".
端到端的应用程序追踪器能够通过特殊用户服务和应用程序组件来标识出系统上过度负载的地方。详见端到端程序追踪。
6.Server-generated alerts automatically
provide notifications when impending problems are detected.
See Oracle Database Administrator's Guide for information
about monitoring the operation of the database with
server-generated alerts.
当问题逼近的时候，服务器产生的告警日志会自动通知。关于用服务器告警日志监控数据运行的更多信息
详见“oracle数据库管理员指南”。
7.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. For information
about using advisors that are available with Oracle
Enterprise Manager, see Oracle Database 2 Day + Performance
Tuning Guide.
oracle企业级管理器能够提供额外的建议参考信息，例如内存建议为实例优化内存。当没有为数据库设置自动内存管理
的时候，内存建议器通常被使用。另外一些建议器被用来优化恢复的平均耗时时间和段的收缩和回滚段空间设置。若要关于使用oracle企业管理器顾问的更多信息，请阅读”两天掌握oracle+性能调优指南”。
8.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. For information about
the Database Performance page, see Oracle Database 2
Day + Performance Tuning Guide.
数据库企业管理器里的数据库性能笔记显示了主机 实例服务时间、实时的诊断信息和吞吐量。这个笔记能够在选定的时间段内自动刷新或者人工刷新。关于性能调优笔记的更多信息
请阅读”2天掌握oracle数据库+数据库调优指南”。
1.2.2额外的数据库工具
This section describes additional Oracletools
that can be used for determining performance problems.
这部分描述了能够被用来确定oracle性能问题的额外工具.
1.2.2.1 V$开头的性能视图
The V$ views are the performanceinformation
sources used by all Oracle performance tuning tools.The
V$ views are based on memory structures initialized
at instancestartup. The memory structures, and the views
that represent them, areautomatically maintained by
Oracle throughout the life of the instance.See Chapter
10, "Instance Tuning Using PerformanceViews"
for information diagnosing tuning problems usingthe
V$ performance views.
V$视图是所有的oracle性能调优工具使用的性能信息的源头。V$视图基于实例启动时被初始化的内存结构。内存结构和代表他们的视图在实例的整个生命周期会被oracle自动维护。请阅读
10章节“使用性能视图的实例优化”来获取使用V$性能视图来诊断调优问题的更多信息。
See Also:
Oracle Database Reference for informationabout
dynamic performance views
同见：
oracle数据库指南来获取关于动态性能视图的更多信息。
Note:
注意：
Oracle recommends using the AutomaticWorkload
Repository to gather performance data. These tools have
been designedto capture all of the data needed for performance
analysis.
oracle推荐使用自动工作信息库来收集性能数据。这些工具已经被设计来捕获性能分析所需要的所有数据。
|
