4 Configuring a Database for Performance
This chapter contains an overview of the Oracle methodology for configuring a database for performance. Although performance modifications can be made to Oracle Database on an ongoing basis, significant benefits can be gained by proper initial configuration of the database.
本章是一个oracle概要方法论，为性能测试目的配置一个数据库。尽量数据库优化方面的修改是一个不断深化的过程，但是合适适当的初始化参数配置可以让我们获得非常大的好处。
This chapter contains the following sections: 这节包含下面的章节
- Performance Considerations for Initial Instance Configuration 性能考虑之初始化参数配置
- Creating and Maintaining Tables for Optimal Performance 为最优化性能创建和维护表
- Performance Considerations for Shared Servers 性能考虑之共享server
4.1 Performance Considerations for Initial Instance Configuration
This section discusses some initial database instance configuration options that have important performance impacts.
If you use the Database Configuration Assistant (DBCA) to create a database, then the supplied seed database includes the necessary basic initialization parameters and meets the performance recommendations that are discussed in this chapter.
这节讨论了一个对于性能有重要影响的初始化参数。
假如你使用DBCA去创建一个数据库，那么这个数据库包含了一些基本的必要的初始化参数，并且满足了以下章节中要讨论的建议参数。
4.1.1 Initialization Parameters
A running Oracle database instance is configured using initialization parameters, which are set in the initialization parameter file. These parameters influence the behavior of the running instance, including influencing performance. In general, a very simple initialization file with few relevant settings covers most situations, and the initialization file should not be the first place you expect to do performance tuning, except for the few parameters shown in Table 4-2.
Table 4-1 describes the parameters necessary in a minimal initialization file. Although these parameters are necessary, they have no performance impact.
运行中的数据库实例是由一系列的在参数文件中配置的参数控制的。这些参数影响着数据库实例的行为，包括性能的影响。通常来讲，一个由少数相关的设置组成的相对简单的参数文件覆盖了多数情况，这些参数文件也不应该是做性能优化时首先要去优化的地方，除了下面列的参数(table 4-2)。
Table 4-1描述了一个最少需要设置的参数文件。尽管这些参数是必需的，但是他们对性能没有什么影响。
Table 4-1 Necessary Initialization Parameters Without Performance Impact
| Parameter | Description |
| Name of the database. This should match the ORACLE_SID environment variable. | |
| Location of the database in Internet dot notation. | |
| Limit on the maximum number of cursors (active SQL statements) for each session. The setting is application-dependent; 500 is recommended. | |
| 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. |
See Also:
Oracle Database Administrator's Guide to learn more about these initialization parameters
Table 4-2 includes the most important parameters to set with performance implications:
Table 4-2 Important Initialization Parameters With Performance Impact
| Parameter | Description |
| Specifies the release with which the Oracle database 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 Database, and you are actually installing a later release, then you might want to set this parameter to the version of the previous release. | |
| 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. | |
| 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. | |
| Specifies the target aggregate PGA memory available to all server processes attached to the instance. | |
| 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. | |
| Specifies the undo space management mode used by the database. The default is AUTO. If unspecified, the database uses AUTO.（在11G中这个是默认的了，所以不用再用设置了） | |
| Specifies the undo tablespace to be used when an instance starts. |
The database uses undo space to store data used for read consistency, recovery, and rollback statements. This data exists in one or more undo tablespaces. If you use the Database Configuration Assistant (DBCA) to create a database, then the undo tablespace is created automatically. To manually create an undo tablespace, add the UNDO TABLESPACE clause to the CREATE DATABASE statement.
To automate the management of undo data, Oracle Database uses automatic undo management, which transparently creates and manages undo segments.To enable automatic undo management, set the UNDO_MANAGEMENT initialization parameter to AUTO (the default setting). If unspecified, then the UNDO_MANAGEMENT initialization parameter uses the AUTO setting. Oracle strongly recommends using automatic undo management because it significantly simplifies database management and eliminates the need for any manual tuning of undo (rollback) segments. Manual undo management using rollback segments is supported for backward compatibility.
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. Oracle Database also uses this information to help tune undo usage. The V$ROLLSTAT view contains information about the behavior of the undo segments in the undo tablespace.
数据库使用undo space存储那些需要读一致性，恢复和回滚的数据。这些数据存在于一个或多个undo表空间中。假如使用DBCA创建数据库，那么undo自动创建。手动创建的时候，将undo tablespace语句加到create database语句后面，然后一起创建起来。
自动化管理的UNDO，即自动的创建和管理Undo段。Alter system set undo_management=auto来设计为自动UNDO管理（默认就是这个参数，不用去修改了）。如果没有设置，默认就是AUTO了。Oracle强烈的建议你使用AUTO来管理，因为它简化了数据库的管理，以及消除了需要手动去调优回滚段的需要。手动管理目前仅仅是为了和以前老版本兼容。（也就是永远别再使用手动管理了）
V$undostat 视图包含了监控和调优undo space的统计信息。使用这个视图，你就可以很好的评估当前的压力以及需要的UNDO空间值。Oracle也使用这个视图去帮助我们调优UNDO的。V$rollstat 视图包含着在undo表空间中这个undo段的行为。
The size of the redo log files can influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes. Generally, larger redo log files provide better performance. Undersized log files increase checkpoint activity and reduce performance.
Although the size of the redo log files does not affect LGWR performance, it can affect DBWR and checkpoint behavior. Checkpoint frequency is affected by several factors, including log file size and the setting of the FAST_START_MTTR_TARGET initialization parameter. If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time, Oracle Database automatically tries to checkpoint as frequently as necessary. Under this condition, the size of the log files should be large enough to avoid additional checkpointing due to under sized log files. The optimal size can be obtained by querying the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_RECOVERY view. You can also obtain sizing advice on the Redo Log Groups page of Oracle Enterprise Manager.
It may not always be possible to provide a specific size recommendation for redo log files, but redo log files in the range of 100 MB to a few gigabytes are considered reasonable. Size online redo log files according to the amount of redo your system generates. A rough guide is to switch log files at most once every 20 minutes.
Redo文件的大小对性能是有影响的，因为dbwr和arcn进程依赖于redo的大小的。通常来说，大一些的redo会提升性能。比较小的redo增加了checkpoint的工作，因此降低了性能。
尽管redo的大小不影响LGWR性能，但是对于DBWR和checkpoint的行为会有所影响。Checkpoint的频繁被多个因素所影响，包括redo大小，fast_start_mttr_target参数。假如fast_start_mttr_target参数被设置，以减少实例恢复的时间，oracle将会自动地在需要的时候频繁地运行checkpoint。在这种情况下，应该尽可能地将redo设置大一些，以避免额外的因为文件太小造成的checkpoint。这个最优的参数可以通过v$instance_recovery视图的optimal_logfile_size列查看。也可以通过OEM来观察REDO LOG GROUP的大小建议值。
提供一个准确的REDO大小通常是不可能的，REDO的大小在100M到几G都是有其原因的。这个大小的确定是由你系统日志产生的大小决定的。大概设置成让系统能在20分钟内进行一次切换（也就是说如果你的系统产生的日志大小在20分钟内大约是500M的话，那就设置成500M）。
4.1.4 Creating Subsequent Tablespaces
If you use the Database Configuration Assistant (DBCA) to create a database, then the seed database automatically includes the necessary tablespaces. If you choose not to use DBCA, then you must create extra tablespaces after creating the database.
假如使用DBCA来创建数据库，种子数据库自动的包括了必要的表空间。如果手动创建，那么就要在创建完以后再添加一些额外的表空间。
所有的数据库都要有SYSTEM,SYSAUX表空间以及一些其他的表空间，额外的表空间包括：
- 临时表空间，它们被用于类似排序的功能
- UNDO表空间，包含一致性读，恢复和UNDO语句
- 至少一个让应用程序使用的表空间（多数情况下应用程序可能需要很多表空间。我自己通常会设置很多的表空间，一般主要是2个，一个数据，一个索引，其它的是一些存放分区数据和历史数据的）
All databases should have several tablespaces in addition to the SYSTEM and SYSAUX tablespaces. These additional tablespaces include:
- A temporary tablespace, which is used for operations such as sorting
- An undo tablespace to contain information for read consistency, recovery, and undo statements
- At least one tablespace for application use (in most cases, applications require several tablespaces)
For extremely large tablespaces with many data files, you can run multiple ALTER TABLESPACE . . . ADD DATAFILE statements in parallel. During tablespace creation, the data files that make up the tablespace are initialized with special empty block images. Temporary files are not initialized.
Oracle Database does this to ensure that it can write all data files in their entirety, but this can obviously be a lengthy process if done serially. Therefore, run multiple CREATE TABLESPACE statements concurrently to speed up tablespace creation. For permanent tables, the choice between local and global extent management on tablespace creation can greatly affect performance. For any permanent tablespace that has moderate to large insert, modify, or delete operations compared to reads, choose local extent management.
对于需要非常大的表空间时，这些表空间需要很多的数据文件，这样可以通过alter tablespace xxx add datafile并行执行（一般很少并行做了，毕竟通常也就上线前会创建一次，项目中间可能几年才再增加一次）。在表空间创建时，数据文件被初始化成特殊空块image。临时表空间不会初始化（我们称之为稀疏文件，也就是根本没在硬盘上写内容，0.00001秒就可以创建临时表空间）
Oracle这样做是为了确保在整个过程中可以写所有的数据文件，但是串行做的话显然要花费很长的时间。因此多个create tablespace可以同时执行以加快表空间的创建速度。对于永久表，本地化和全局化的extent管理对性能会有很大的影响。对于任何永久表空间，与读操作相比起来，选择local extent管理的话，INSERT,UPDATE,DELTE时系统会快一些。（注：也就是说如果改动多的话，使用local来管理；如果主要是读操作，都无所谓了。不过现在系统默认几乎都是local管理了）
4.1.4.1 Creating Permanent Tablespaces - Automatic Segment-Space Management
For permanent tablespaces, Oracle recommends using automatic segment-space management. Such tablespaces, often referred to as bitmap tablespaces, are locally managed tablespaces with bitmap segment space management.
对于永久表空间，oracle建议使用ASSM。这种表空间常常称之为位图表空间，即使用本地的位图段空间管理技术来管理的。
4.1.4.2 Creating Temporary Tablespaces
Properly configuring the temporary tablespace helps optimize disk sort performance. Temporary tablespaces can be dictionary-managed or locally managed. Oracle recommends the use of locally managed temporary tablespaces with a UNIFORM extent size of 1 MB.
You should monitor temporary tablespace activity to check how many extents the database allocates for the temporary segment. If an application extensively uses temporary tables, as in a situation when many users are concurrently using temporary tables, then the extent size could be set smaller, such as 256K, because every usage requires at least one extent. The EXTENT MANAGEMENT LOCAL clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The default for SIZE is 1M.
适当的临时表空间配置可以优化sort的性能。临时表空间可以用字典或本地管理的。Oracle建议使用local的管理，同时uniform大小设置成1M。
你应该监控临时表空间的活跃性，以确定数据库分配了多少临时段。如果一个应用程序大量的使用临时表，那么在这种情况下，当许多用户同时使用临时表，那么extent大小应该设置的小一些，比如256K，因为分配的最小值是1个extent，即256K了。EXTENT MANAGEMENT LOCAL对于临时表空间是最好的，因为他们都是按Uniform来分配的，默认值为1M。（注：临时表是manual管理的）
4.2 Creating and Maintaining Tables for Optimal Performance
When installing applications, an initial step is to create all necessary tables and indexes. When you create a segment, such as a table, the database allocates space for the data. If subsequent database operations cause the data volume to increase and exceed the space allocated, then Oracle Database extends the segment.
当实施时，一个初始化步骤就是创建所有必须的表和索引。当你创建一个表时，数据库就需要给它分配空间。假如随后不断的增加表的数据，导致数据库分配更多容量时，oracle扩展这个段的空间。
当你创建表和索引的时候，注意下面2点：
When creating tables and indexes, note the following:
- Specify automatic segment-space management for tablespaces 使用ASSM来管理
In this way Oracle Database automatically manages segment space for best performance.
Applications should carefully set storage options for the intended use of the table or index. This includes setting the value for PCTFREE. Note that using automatic segment-space management eliminates the necessity of specifying PCTUSED.
应用程序应该仔细的考虑自己的storage选项。包括了pctfree参数。注意当使用ASSM时，不需要再设置PCTUSED了（设置了也没用了）
Note: 下面的默认都是知道的：extent management local segment space management auto
Use of free lists is not recommended. To use automatic segment-space management, create locally managed tablespaces, with the segment space management clause set to AUTO.
You can store heap-organized tables in a compressed format that is transparent for any kind of application. Compressed data in a database block is self-contained, which means that all information needed to re-create the uncompressed data in a block is available within the block. A block is also compressed in the buffer cache. Table compression not only reduces the disk storage but also the memory usage, specifically the buffer cache requirements. Performance improvements are accomplished by reducing the amount of necessary I/O operations for accessing a table and by increasing the probability of buffer cache hits.
Oracle Database has an advanced compression option that enables you to boost the performance of any type of application workload—including data warehousing and OLTP applications—while reducing the disk storage that is required by the database. You can use the advanced compression feature for all types of data, including structured data, unstructured data, backup data, and network data.
你可以将堆表（我们平时create table出来的就是这种表），设置成压缩格式，压缩对于应用程序是透明的。在一个数据块中的压缩记录是自包含的，意思就是所有需要重建这个非压缩数据的信息都存储在这个块中。块在buffer cache中也是压缩的。表压缩不仅仅是减少了磁盘消耗，也减少了内存使用，特别是buffer cache数据。通过压缩可以减少IO的操作，以及增加buffer cache命中率，从而可以增长性能。（同样的数据因为压缩后数据变小，所以可以在内存中存储更多的数据，增加了命中率）
Oracle提升了多种高级压缩，以满足不同的应用场景，包括OLTP和DSS，减少磁盘的使用。你可以对所有类型的数据进行压缩，包括结构化的，非结构化的，备份的和网络的数据进行高级特性压缩。
4.2.1.1 Estimating the Compression factor
Table compression works by eliminating column value repetitions within individual blocks. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table. The compression is higher in blocks that have more repeated values.
Before compressing large tables you should estimate the expected compression factor. The compression factor is defined as the number of blocks necessary to store the information in an uncompressed form divided by the number of blocks necessary for a compressed storage. The compression factor can be estimated by sampling a small number of representative data blocks of the table to be compressed and comparing the average number of records for each block for the uncompressed and compressed case. Experience shows that approximately 1000 data blocks provides a very accurate estimation of the compression factor. Note that the more blocks you are sampling, the more accurate the result become.
表压缩消除了私有块中重复列值。在一个块中所有重复的行和列在块的头部存储一次，这种块称之为symbol table。所有对这些值的引用都指向这个symbol table。在块上进行压缩的比例更高，因为拥有更多的重复值。
在你压缩大表时，先要评估一下压缩因子。压缩因子定义是：压缩存储时需要的数据块数量/需要在非压缩格式中存储信息的块数量。压缩因子被评估，通过采样一部分表中将被压缩的数据块，对比每个块的平均记录数量的非压缩和压缩情况。经验显示大约1000数据块可以提供一个相对准确的压缩因子评估结果。你采样的数据越多，结果越准确。
4.2.1.2 Tuning to Achieve a Better Compression Ratio
Oracle Database achieves a good compression factor in many cases with no special tuning. As a DBA or application developer, you can try to tune the compression factor by reorganizing the records when the compression takes place. Tuning can improve the compression factor slightly in some cases and substantially in other cases.
To improve the compression factor you must increase the likelihood of value repetitions within a data block. The achievable compression factor depends on the cardinality of a specific column or column pairs (representing the likelihood of column value repetitions) and on the average row length of those columns. Table compression not only compresses duplicate values of a single column but tries to use multi-column value pairs whenever possible. Without a detailed understanding of the data distribution it is very difficult to predict the most optimal order.
Oracle提供了一个简单而有非常好的压缩因子，在许多场合下不需要特别的调优。作为DBA或者应用开发人员，你可以通过重新组织数据调优压缩因子。在一些情况下压缩会小幅提升压缩因子，有些场景下也会大幅提升。
为了提升压缩因子，你必须增加块中重复数据的概率。可行的压缩因子依赖于特定的列或列对的基数，以及这些列的平均长度，列对指的是代表重复列的概率。表压缩不仅仅是压缩某一列上重复的值，而是一有机会就试图使用多列值对压缩。如果没有对数据分布有很好的了解，预测一个最佳的顺序是非常困难的。
Over time, it is common for segment space to become fragmented or for a segment to acquire a lot of free space as the result of update and delete operations. The resulting sparsely populated objects can suffer performance degradation during queries and DML operations.
Oracle Database provides a Segment Advisor that provides advice on whether an object has space available for reclamation based on the level of space fragmentation within an object.
If an object does have space available for reclamation, then you can compact and shrink segments or deallocate unused space at the end of a segment.
一段时间之后，段中空间存在碎片或者由于大量的update或者delete造成大量的未使用空间。这个稀疏的对象，将导致在查询和DML操作时性能下降。
Oracle提供了一个segment advisor，它基于对象内空间碎片化的程序提供一个是否有空间可以回收的建议。
假如对象中真的有空间可以回收，那么你可以compact和shrink段或者直接在段的尾部回收未使用空间。（一般很少这么做，如果手动storage的多的话可以在尾部回收；一般使用shrink足够了，但是前提是要enable row movement）
The most efficient time to create indexes is after data has been loaded. In this way, space management becomes simpler, and no index maintenance takes place for each row inserted. SQL*Loader automatically uses this technique, but if you are using other methods to do initial data load, then you may need to create indexes manually. Additionally, you can perform index creation in parallel using the PARALLEL clause of the CREATE INDEX statement. However, SQL*Loader is not able to parallelize index creation, so you must manually create indexes in parallel after loading data.
创建索引最有效的方式是在数据全部都加载完以后再创建。在这种情况下，空间管理将变得简单化，且在数据插入时没有索引维护的额外工作。Sql*loader自动的使用这种技术，但是假如你使用其他的方法去初始化数据，那么你需要去手动的创建索引。另外你可以在创建索引时使用并行技术。然而sql*loader无法并行创建索引，所以你必须手动地在加载完数据以后手动去并行创建。
4.2.3.1 Specifying Memory for Sorting Data
During index creation on tables that contain data, the data must be sorted. This sorting is done in the fastest possible way, if all available memory is used for sorting. Oracle recommends that you enable automatic sizing of SQL working areas by setting the PGA_AGGREGATE_TARGET initialization parameter.
在一个有数据的表上创建索引时，数据将被排序。这个排序的过程如果全部使用内存的话，将是最快的方式。Oracle建议设置pga_aggregate_target参数来自动地定制sql 工作区大小。
4.3 Performance Considerations for Shared Servers
Using shared servers reduces the number of processes and the amount of memory consumed on the database host. Shared servers are beneficial for databases where there are many OLTP users performing intermittent transactions.
Using shared servers rather than dedicated servers is also generally better for systems that have a high connection rate to the database. With shared servers, when a connect request is received, a dispatcher is available to handle concurrent connection requests. With dedicated servers, however, a connection-specific dedicated server is sequentially initialized for each connection request.
Performance of certain database features can improve when a shared server architecture is used, and performance of certain database features can degrade slightly when a shared server architecture is used. For example, a session can be prevented from migrating to another shared server while parallel execution is active.
A session can remain nonmigratable even after a request from the client has been processed, because not all the user information has been stored in the UGA. If a server were to process the request from the client, then the part of the user state that was not stored in the UGA would be inaccessible. To avoid this situation, individual shared servers often need to remain bound to a user session.
When using some features, you may need to configure more shared servers, because some servers might be bound to sessions for an excessive amount of time.
使用shared server减少了进程的数量以及内存的消耗。使用它的好处在：在OLTP应用中大道用户执行间歇性的事务。
使用shared server而不是dedicated模式的另一个好处是对于大量需要连接到数据库时。在使用shared server，当接收到一个连接请求后，dispatcher就可以去并发处理连接请求。在使用dedicated server时，一个特定的dedicated连接是在每个连接请求后顺序地初始化的。
当使用shared架构时，在某些数据库特性时性能会提升，而有些则会下降。比如一个session，在并行执行时无法迁移到其他的share进程。
一个session可以保留非迁移，即使一个客户的请求已经被处理，因为不是所有的用户信息都已经被存储到UGA中了。假如一个server处理一个客户的请求，那么没有被存储到UGA中的用户状态将是无法访问的。为了避免这种情况，独立的shared经常需要保留到user session的限制。
当使用一些特性的时候，你可能需要配置更多的shared， 因为一些server可能绑定到session要一定的时间。
下面的章节讨论了如何去减少进程竞争。（这部分shared讨论oracle讲的不太好，其实是很简单的，如果这里理解不了，后面还有更详细的。在前面中我可能已经介绍过，我在银行中只见过一次需要使用shared的，但是那个笨蛋还不知道用，因为当时我不是做DBA，而是应用实施，所以没告诉他，当然也没见过他呵呵。因此到目前为止我没有见过一家银行采用过这种技术。）
This section discusses how to reduce contention for processes used by Oracle Database architecture:
- Identifying Contention Using the Dispatcher-Specific Views
- Identifying Contention for Shared Servers
4.3.1 Identifying Contention Using the Dispatcher-Specific Views
The following views provide dispatcher performance statistics:
- V$DISPATCHER: general information about dispatcher processes
- V$DISPATCHER_RATE: dispatcher processing statistics
The V$DISPATCHER_RATE view contains current, average, and maximum dispatcher statistics for several categories. Statistics with the prefix CUR_ are statistics for the current sample. Statistics with the prefix AVG_ are the average values for the statistics after the collection period began. Statistics with the prefix MAX_ are the maximum values for these categories after statistics collection began.
To assess dispatcher performance, query the V$DISPATCHER_RATE view and compare the current values with the maximums. If your present system throughput provides adequate response time and current values from this view are near the average and less than the maximum, then you likely have an optimally tuned shared server environment.
If the current and average rates are significantly less than the maximums, then consider reducing the number of dispatchers. Conversely, if current and average rates are close to the maximums, then you might need to add more dispatchers. A general rule is to examine V$DISPATCHER_RATE statistics during both light and heavy system use periods. After identifying your shared server load patterns, adjust your parameters accordingly.
If necessary, you can also mimic processing loads by running system stress tests and periodically polling V$DISPATCHER_RATE statistics. Proper interpretation of these statistics varies from platform to platform. Different types of applications also can cause significant variations on the statistical values recorded in V$DISPATCHER_RATE.
V$dispatcher_rate视图包含了当前的，平均的和最大的dispatcher统计数据。以CUR_开关的是当前采样的统计数据。以AVG_开关的是在收集过程开始后的一个平均统计数据。以MAX_开关的是在统计收集开始后的一个最大数据。
访问dispatcher的性能，查询v$dispatcher_rate视图，比较当前和最大的值。假如目前系统的响应时间吞吐量都合适，并且CUR_的值接近平均值，少于最大值，那么你的环境就可能是最优的。（接受平均值，而且怎么可能会大于最大值呢？不是废话吗？ 其实oracle真正想表达的是，平均值！=最大值。也就是说平均值是20的话，最大值别是21这样的值，太接近就不好了。）
假如当前和平均的rate比最大值小的多，那么考虑减少dispatcher的数量。相反地，假如当前和平均值已经接受最大值了，那么就需要增加dispatcher值了。最好的方法是在系统负荷比较轻的时候和最重的时候去查看一下v$dispatcher_rate统计数据。在确定了数据之后再去调整dispatcher的参数。
假如有需要的话，你可以通过压力测试来模拟以查看v$dispatcher_rate统计。不同平台这个视图内容有不同含义。不同类型的应用也可能会造成v$dispatcher_rate值差异。
4.3.1.1 Reducing Contention for Dispatcher Processes
To reduce contention, consider the following: 为了减少竞争，考虑以下几个方面
- Adding dispatcher processes 增加dispatcher数量
The total number of dispatcher processes is limited by the value of the initialization parameter MAX_DISPATCHERS. You might need to increase this value before adding dispatcher processes. M
AX_DISPATCHERS控制着最多可以有多少。在增加dispatcher进程之前先要去增加这个值的
- Enabling connection pooling 允许连接池
When system load increases and dispatcher throughput is maximized, it is not necessarily a good idea to immediately add more dispatchers. Instead, consider configuring the dispatcher to support more users with connection pooling.
当系统压力增加，dispatcher吞吐量达到最大时，立刻去增加dispatcher不是一个好主意。相反应该考虑让dispatcher使用连接池支持更多的用户
- Enabling Session Multiplexing 允许session多路复用
Multiplexing is used by a connection manager process to establish and maintain network sessions from multiple users to individual dispatchers. For example, several user processes can connect to one dispatcher by way of a single connection from a connection manager process. Session multiplexing is beneficial because it maximizes use of the dispatcher process connections. Multiplexing is also useful for multiplexing database link sessions between dispatchers.
多路复用被使用于连接管理进程，建立和维护网络session从多个用户到单一的dispatcher上（理解成映射到独立的dispatcher吧）。例如，许多用户进程通过一个连接管理进程以单一连接的方式连接到一个dispatcher。Session复用是非常好的，因为它最大化的利用了dispatcher的连接。多路复用对于使用database link在不同dispatcher中使用时也是非常有用的。
See Also:
-
- Oracle Database Net Services Administrator's Guide to learn how to configure connection pooling
4.3.2 Identifying Contention for Shared Servers
Steadily increasing wait times in the requests queue indicate contention for shared servers. To examine wait time data, use the dynamic performance view V$QUEUE. This view contains statistics showing request queue activity for shared servers. By default, this view is available only to the user SYS and to other users with SELECT ANY TABLE system privilege, such as SYSTEM. Table 4-3 lists the columns showing the wait times for requests and the number of requests in the queue.
当响应时间不断的增加时，就表示shared有竞争了。通过 v$queue 视图查询这个等待数据。这个视图包含对于shared server的请求队列活跃统计。默认情况下这个视图仅仅对SYS可用及select any table权限的用户，例如SYSTEM。 下面的表列出了等待时间和队列中的等待数据。
Table 4-3 Wait Time and Request Columns in V$QUEUE
| Column | Description |
| WAIT | Displays the total waiting time, in hundredths of a second, for all requests that have ever been in the queue 显示所有在队列中的请求的、总的等待时间，1%秒的单位。 |
| TOTALQ | Displays the total number of requests that have ever been in the queue 显示目前所有在队列中的请求的数量 |
Monitor these statistics occasionally while your application is running by issuing the following SQL statement:
当你的应用程序运行时，通过下面的SQL语句定期的监控一下：
SELECT DECODE(TOTALQ, 0, 'No Requests', WAIT/TOTALQ || ' HUNDREDTHS OF SECONDS') "AWTPR"
FROM V$QUEUE
WHERE TYPE = 'COMMON';
This query returns the results of a calculation that show the following:
| AVERAGE WAIT TIME PER REQUEST ----------------------------- .090909 HUNDREDTHS OF SECONDS |
From the result, you can tell that a request waits an average of 0.09 hundredths of a second in the queue before processing.
从结果中看到，在处理之前的一个等待时间是0.09/100 = 0.0009 = 0.9ms = 900us（这个时间还是可以接受的了）
You can also determine how many shared servers are currently running by issuing the following query:
通过下面的SQL查看当前有多少shared server在进行
| SELECT COUNT(*) "Shared Server Processes" FROM V$SHARED_SERVER WHERE STATUS != 'QUIT'; |
The result of this query could look like the following:
Shared Server Processes
-----------------------
10
If you detect resource contention with shared servers, then first ensure that this is not a memory contention issue by examining the shared pool and the large pool. If performance remains poor, then you might want to create more resources to reduce shared server process contention. You can do this by modifying the optional server process initialization parameters:
当发现shared有资源竞争时，第一步确定是不是shared pool 和large pool中的内存竞争。假如不是，但是性能还很差，那么就增加资源以减少竞争。通过这几个参数来修改:
- MAX_DISPATCHERS
- MAX_SHARED_SERVERS
- DISPATCHERS
- SHARED_SERVERS
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1696240/viewspace-1173836/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/1696240/viewspace-1173836/
317
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
