[PT]Parameters related to Performance Tuning

前几天面试时的一个问题，除了内存相关参数外，还有哪些参数和性能相关。当时只说出了第二个。今天看SQL TUNING时刚好看到相关内容，因此在这里列出一些。虽然这些参数一般取默认值即可，不过还是有必要了解一下。

CURSOR_SHARING

CURSOR_SHARING determines what kind of SQL statements can share the same cursors. The default value is EXACT.

Values:

• FORCE

  Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

• SIMILAR

  Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

• EXACT

  Only allows statements with identical text to share the same cursor.

DB_FILE_MULTIBLOCK_READ_COUNT

DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.

Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

OPTIMIZER_INDEX_CACHING

OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.

The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.

You can modify the optimizer's assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.

OPTIMIZER_INDEX_COST_ADJ

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

OPTIMIZER_MODE

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

Values:

• first_rows_n

  The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).

• first_rows

  The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

• all_rows

  The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

OPTIMIZER_DYNAMIC_SAMPLING

OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer.

OPTIMIZER_FEATURES_ENABLE

OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.

For example, if you upgrade your database from release 8.0.6 to release 9.2, but you want to keep the release 8.0.6 optimizer behavior, you can do so by setting this parameter to 8.0.6. At a later time, you can try the enhancements introduced in releases up to and including release 9.2 by setting the parameter to 9.2.0.

STAR_TRANSFORMATION_ENABLED

STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries.

Values:

• TRUE

  The optimizer will consider performing a cost-based query transformation on the star query.

• FALSE

  The transformation will not be applied.

• TEMP_DISABLE

  The optimizer will consider performing a cost-based query transformation on the star query but will not use temporary tables in the star transformation.

[@more@]

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

转载于:http://blog.itpub.net/8558093/viewspace-1022277/