ORACLE DOC

一、实例优化

A.      考虑的方面

1.       内存

2.       IO

3.       操作系统

原则：调整一般是被动的，针对的是重大的瓶颈问题，通过改变来减小这个瓶颈的负面影响。

B.      性能基线

·         Application statistics (transaction volumes, response time)

应用程序的交易量、响应时间

·         Database statistics

数据库统计数据

·         Operating system statistics

操作系统统计数据

·         Disk I/O statistics

磁盘统计数据

·         Network statistics

网络统计数据

         In the Automatic Workload Repository, baselines are identified by a range of snapshots that are preserved for future comparisons.

         自动性能调整工具会保留快照。

C.      症状和问题

看病和下药是两回事。

•Slow physical I/O 物理I/O较慢

Generally, this is caused by poorly-configured disks. However, it could also be caused by a significant amount of unnecessary physical I/O on those disks issued by poorly-tuned SQL.

通常是由磁盘配置不当造成的，但也有可能是由大量性能不佳SQL引起的无意义磁盘IO。

•Latch contention 闩竞争

Rarely is latch contention tunable by reconfiguring the instance. Rather, latch contention usually is resolved through application changes.

这种闩竞争很少能由调节实例来调整，对应用程序的调整会比较有帮助。

•Excessive CPU usage  CPU使用过高

Excessive CPU usage usually means that there is little idle CPU on the system. This could be caused by an inadequately-sized system, by untuned SQL statements, or by inefficient application programs.

操作系统配置不当，SQL语句不当，或者应用程序不当都表现出这种症状。

D.      何时调整

主动监视

消除瓶颈

Changes in the application, or the way the application is used

Changes in Oracle

Changes in the host hardware configuration

通常最有效的方法是改变应用程序

二、SQL语句调整

Oracle Database Data Warehousing Guide. DSS系统

本书讨论的是OLTP环境

查询优化与执行计划

三、改善性能和性能紧急故障处理

改善性能的本质是迭代，解决第一个问题不一定能缓解性能瓶颈。

性能调整成功的目标最终还是用户体验的要求。实现响应时间和处理能力与业务需求协调即为性能调整成功。

步骤：

1.       初始标准检查

a. 听取用户反馈，这是性能调整的目标和关键。

b. 获取完整的操作系统数据、数据库数据、应用程序统计数据，并且要有系统性能c. 好与坏时的两种数据。

d. 检查操作系统的性能和资源利用，检查硬件设备是否运转正常。

e. 检查最常见的十种错误。

f. 构建概念模型。

e. 一条金科玉律是每次只更改一件事，以观测变化，但通常情况下，我们需要同时应用多个更改。

g. 重复f-e直到达到目标。

2.       使用自动诊断，如果不使用，如何手动确立问题：

Is the response time/batch run time acceptable for a single user on an empty or lightly loaded system?

对于单用户来说，在一个空负荷/轻负载的系统上这个响应时间或者批量处理时间是否可接受？

如果不可接受的话，这就代表应用程序的编码有问题，它不可能接受在多用户时共享系统资源。在这种情况下，获取应用程序内部的统计信息，并获得 SQL 跟踪和 SQL 计划信息。使用开发人员调查的数据、 索引、 事务 SQL 设计和工作可能推迟到批处理/后台处理的问题。

         Is all the CPU being utilized?

如果CPU使用超过40%，则调查操作系统的网络传送、分页、SWAP、进程负荷。除去操作系统的消耗，检查非数据库资源的占用情况，当确定数据库占用了大部分资源后，调查顶部的SQL的CPU统计。

如果不存在应用程序优化和SQL效率低下的问题，请重新考虑您的工作繁忙或使用更大的系统。

At this point, the system performance is unsatisfactory, yet the CPU resources are not fully utilized.

系统性能不佳，但CPU资源未被充分利用。

Get the WAIT_EVENTS statistics from the server, and determine the biggest serialization point. If there are no serialization points, then the problem is most likely outside the database, and this should be the focus of investigation. Elimination of WAIT_EVENTS involves modifying application SQL and tuning database parameters. This process is very iterative and requires the ability to drill down on the WAIT_EVENTS systematically to eliminate serialization points.

3.       Oracle最常见的十种错误

a.     Bad Connection Management

b.     Bad Use of Cursors and the Shared Pool

不使用游标会导致重复解析。不使用绑定变量，会硬解析所有SQL语句。

应用程序产生的动态SQL也是怀疑对象。

c.     Bad SQL

从查询返回结果的时间上很容易很辨。在线系统的查询结果超过1分钟或DSS系统的结果返回超过1个小时都是可能于此有关。追踪占用大量资源的SQL。

d.     Use of Nonstandard Initialization Parameters

e.     Getting Database I/O Wrong

f.      Redo Log Setup Problems

Small redo logs cause system checkpoints to continuously put a high load on the buffer cache and I/O system. If there are too few redo logs, then the archive cannot keep up, and the database will wait for the archive process to catch up.

过小的重做日志写出量一直很高，过少的日志，在归档模式下可能会引起等待事件。

g.     Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots (INITRANS), or shortage of rollback segments.

This is particularly common on INSERT-heavy applications, in applications that have raised the block size above 8K, or in applications with large numbers of active users and few rollback segments. Use automatic segment-space management (ASSM) to and automatic undo management solve this problem.

h.     Long Full Table Scans

i.      High Amounts of Recursive (SYS) SQL

以SYS用户身份执行的大量递归SQL，表明空间管理活动，比如分区的分配和替换。尽量使用本地管理的表空间。Recursive SQL executed under another user Id is probably SQL and PL/SQL, and this is not a problem.递归SQL执行的下一个USER ID很可能是SQL/PL/SQL语句，这不是错误。

j.      Deployment and Migration Errors

将系统从开发环境迁移到新环境，性能可能会下降，这可能是缺少索引或统计信息不正确造成的。When migrating applications of known performance, export the schema statistics to maintain plan stability using the DBMS_STATS package.

4.       紧急性能故障

这种需求的特点是：需要迅速确认发生了什么变化，尽快恢复正常服务，基于此，严格的改进措施方案是不切实际的。在问题解决之后，还必须收集足够多的信息，以防止下次再次发生。

步骤：

a.       调查及收集症状

包括：用户反馈，问题是在响应时间还是数据吞吐量上；“因为什么改变之后性能出现了下降”，该提问可以为定位问题提供线索，不过，这种问题通常很难回答，取统计数据或者日志在出问题前后会有帮助；使用自动优化功能诊断和检测问题。

b.       检查硬件。检查使用CPU最高的，检查磁盘、内存使用情况及系统所有组件的网络性能。这能很快定位问题来自应用程序还是数据库服务。

c.       检查是否数据库受CPU限制，或者处于等待事件。

如果是CPU限制，调查如下内容：

e  Sessions that are consuming large amounts of CPU at the operating system level and database.check V$SESS_TIME_MODEL for database CPU usage（在操作系统和数据库级消耗大量CPU的会话）

e  Sessions or statements that perform. many buffer gets at the database level;

（buffer gets量比较大的会话或语句，检查V$SESSTAT、 V$SQLSTATS）

e  执行计划更改导致次优SQL执行，通常很难定位。

e  初始参数设置不正确

e  Algorithmic issues as a result of code changes or upgrades of all components

等待事件

If the database sessions are waiting on events, then follow the wait events listed in V$SESSION_WAIT to determine what is causing serialization. The V$ACTIVE_SESSION_HISTORY view contains a sampled history of session activity which can be used to perform. diagnosis even after an incident has ended and the system has returned to normal operation. In cases of massive contention for the library cache, it might not be possible to logon or submit SQL to the database. （在库缓存大量争用的情况下，它不可能登录或提交到数据库的 SQL）In this case, use historical data to determine why there is suddenly contention on this latch.（在这种情况下必须使用历史数据来确定为什么会出现闩锁的争用） If most waits are for I/O, then examine V$ACTIVE_SESSION_HISTORY to determine the SQL being run by the sessions that are performing all of the inputs and outputs. See Chapter 10, "Instance Tuning Using Performance Views" for a discussion on wait events.

四、实例的性能优化

1.       配置数据库的性能

初始化参数

a)       最低限度的初始化参数

Parameter	Description
DB_NAME	Name of the database. This should match the ORACLE_SID environment variable.
DB_DOMAIN	Location of the database in Internet dot notation.
OPEN_CURSORS	Limit on the maximum number of cursors (active SQL statements) for each session. The setting is application-dependent; 500 is recommended. 每个会话能打开的游标(活动SQL语句)的最大数目，推荐值为500.
CONTROL_FILES	Set to contain at least two files on different disk drives to prevent failures from control file loss.
DB_FILES	Set to the maximum number of files that can assigned to the database. 数据库能管理的最大文件数目。

 

 

b)       对性能影响重要的参数

Parameter	Description
COMPATIBLE	Specifies the release with which the Oracle server must maintain compatibility. It lets you take advantage of the maintenance improvements of a new release immediately in your production systems without testing the new functionality in your environment. If your application was designed for a specific release of Oracle, and you are actually installing a later release, then you might want to set this parameter to the version of the previous release. 通过修改该参数，为系统升级，可以启用某个版本的新特性。ORACLE不允许降级，但网络上有高人手工将数据库实现了降级，但是会造成什么负面影响很难判断，因此，严禁在生产库中将数据库版本降级。
DB_BLOCK_SIZE	Sets the size of the Oracle database blocks stored in the database files and cached in the SGA. The range of values depends on the operating system, but it is typically 8192 for transaction processing systems and higher values for database warehouse systems.
SGA_TARGET	Specifies the total size of all SGA components. If SGA_TARGET is specified, then the buffer cache (DB_CACHE_SIZE), Java pool (JAVA_POOL_SIZE), large pool (LARGE_POOL_SIZE), and shared pool (SHARED_POOL_SIZE) memory pools are automatically sized. See "Automatic Shared Memory Management".
PGA_AGGREGATE_TARGET	Specifies the target aggregate PGA memory available to all server processes attached to the instance. See "PGA Memory Management" for information on PGA memory management.
PROCESSES	Sets the maximum number of processes that can be started by that instance. This is the most important primary parameter to set, because many other parameter values are deduced from this.
SESSIONS	This is set by default from the value of processes. However, if you are using the shared server, then the deduced value is likely to be insufficient.
UNDO_MANAGEMENT	Specifies which undo space management mode the system should use. AUTO mode is recommended.
UNDO_TABLESPACE	Specifies the undo tablespace to be used when an instance starts up.

      配置UNDO空间

      Oracle needs undo space to keep information for read consistency, for recovery, and for actual rollback statements.

Adding the UNDO TABLESPACE clause in the CREATE DATABASE statement sets up the undo tablespace. Set the UNDO_MANAGEMENT initialization parameter to AUTO to operate your database in automatic undo management mode. The V$UNDOSTAT view contains statistics for monitoring and tuning undo space. Using this view, you can better estimate the amount of undo space required for the current workload. The V$ROLLSTAT view contains information about the behavior. of the undo segments in the undo tablespace.

重做日志大小

会影响数据库的写进程和归档进程。一般性况下，重做日志大一点要合适，检查点动作过于频繁会降低数据库的性能。日志应该二十分钟切换一次比较合理。

创建后续表空间（指除SYS表空间和SYSAUX表空间以外的表空间），通常有临时表空间、回滚表空间、用户表空间。表空间分为永久表空间和临时表空间，永久表空间由自动段空间管理，或者由字典管理。临时表空间的主要贡献在于优化磁盘的排序性能。它可以由字典管理或者本地管理，Oracle Corporation recommends the use of locally managed temporary tablespaces with a UNIFORM. extent size of 1 MB

创建和维护良好的性能表

当安装应用程序，第一步是创建所有需要的表和索引。当您创建一个段，如表，Oracle 数据分配数据库中的空间。如果随后数据库操作导致超过分配的空间，增加的数据量，Oracle 将延伸段。创建表和索引时注意：指定表空间自动段空间管理；精心设置存储选项。

表压缩

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24355950/viewspace-707594/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24355950/viewspace-707594/