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: 這節包含下面的章節

效能考慮之初始化引數配置
為最最佳化效能建立和維護表
效能考慮之共享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 .

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
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.
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.

See Also:

to learn more about these initialization parameters

includes the most important parameters to set with performance implications:

Table 4-2 Important Initialization Parameters With Performance Impact

Parameter	Description
COMPATIBLE	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.
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.
PGA_AGGREGATE_TARGET	Specifies the target aggregate PGA memory available to all server processes attached to the instance.
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）
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 the undo space management mode used by the database. The default is AUTO. If unspecified, the database uses AUTO.（在11G中這個是預設的了，所以不用再用設定了）
UNDO_TABLESPACE	Specifies the undo tablespace to be used when an instance starts.

4.1.2 Configuring Undo Space

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段的行為。

4.1.3 Sizing Redo Log Files

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.

Set storage options carefully 仔細的考慮storage配置

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.

4.2.1 Table Compression

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或者應用開發人員，你可以透過重新組織資料調優壓縮因子。在一些情況下壓縮會小幅提升壓縮因子，有些場景下也會大幅提升。

為了提升壓縮因子，你必須增加塊中重複資料的機率。可行的壓縮因子依賴於特定的列或列對的基數，以及這些列的平均長度，列對指的是代表重複列的機率。表壓縮不僅僅是壓縮某一列上重複的值，而是一有機會就試圖使用多列值對壓縮。如果沒有對資料分佈有很好的瞭解，預測一個最佳的順序是非常困難的。

4.2.2 Reclaiming Unused Space

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）

4.2.3 Indexing Data

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中使用時也是非常有用的。

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 顯示目前所有在佇列中的請求的數量

Oracle Performance Tuning 11g2 (4)

相關文章