老李分享： Oracle Performance Tuning Overview 翻译 2

精选转载

北京茑萝00 2015-12-16 17:19:08

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 自动性能调优特性包括以下

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 "Automatic Database Diagnostic Monitor".
自动数据库诊断和监测工具针对oracle数据库可能的性能问题分析了AWR收集的信息。详见自动数据库诊断监控。
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优化建议器。
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访问建议器概述。
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 for information about monitoring the operation of the database with server-generated alerts.
当问题逼近的时候，服务器产生的告警日志会自动通知。关于用服务器告警日志监控数据运行的更多信息详见“oracle数据库管理员指南”。
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+性能调优指南”。
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$性能视图来诊断调优问题的更多信息。