| Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-02 |
|
|
View PDF |
| Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-02 |
|
|
View PDF |
This section describes new performance features of Oracle9i Release 2 (9.2) and provides pointers to additional information. The features and enhancements described in this section comprise the overall effort to optimize server performance.
|
Note: Before using this performance tuning reference, make sure you have read Oracle9i Database Performance Planning. Oracle Corporation has designed a new performance methodology, based on years of Oracle designing and performance experience. This brief book explains clear and simple activities that can dramatically improve system performance. It discusses the following topics:
|
The new and updated performance features include the following:
A new setting, FORCE, is available for the QUERY_REWRITE_ENABLED session parameter.
When QUERY_REWRITE_ENABLED is set to FORCE, Oracle always uses rewrite and does not evaluate the cost before doing so. FORCE is useful when you know that the query will always benefit from rewrite, when reduction in compile time is important, and when you know that the optimizer may be underestimating the benefits of materialized views.
A new hint, EXPAND_GSET_TO_UNION, is available to force rewrite when using function-based indexes in queries where compilation time is important and the query always benefits from rewrite (OLAP).
The EXPAND_GSET_TO_UNION hint is used for queries containing grouping sets (such as queries with GROUP BY GROUPING SET or GROUP BY ROLLUP). The hint forces a query to be transformed into a corresponding query with UNION ALL of individual groupings.
| See Also:
"EXPAND_GSET_TO_UNION" for details about using this hint |
The purpose of dynamic sampling is to improve server performance by determining more accurate selectivity and cardinality estimates. More accurate selectivity and cardinality estimates allow the optimizer to produce better performing plans.
You can use dynamic sampling to:
The DYNAMIC_SAMPLING hint lets you control dynamic sampling to improve server performance by determining more accurate selectivity and cardinality estimates. You can set the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify a table.
See Also:
|
Oracle9i Release 2 (9.2) allows creation of a database with a locally managed SYSTEM tablespace. Use the EXTENT MANAGEMENT LOCAL clause of the CREATE DATABASE statement to create a locally managed SYSTEM tablespace.
If you specify EXTENT MANAGEMENT LOCAL, then you must also specify the default temporary tablespace.
See Also:
|
Data segment compression reduces disk use and memory use (specifically, the buffer cache), often leading to a better scaleup for read-only operations. Data segment compression can also speed up query execution.
Oracle9i Release 2 (9.2) achieves a good compression ratio in many cases with no special tuning. However, if you need a better compression ratio, tuning can improve it slightly in some cases and very substantially in other cases.
The amount of memory available for the library cache can drastically affect the parse rate of an Oracle instance. With Oracle9i, Release 2 (9.2) or higher, the shared pool advisory statistics provide a database administrator with information about library cache memory and predict how changes in the size of the shared pool can affect the parse rate.
The shared pool advisory statistics track the library cache's use of shared pool memory and predict how the library cache will behave in shared pools of different sizes. Two fixed views provide the information to determine how much memory the library cache is using, how much is currently pinned, how much is on the shared pool's LRU list, as well as how much time might be lost or gained by changing the size of the shared pool.
Under automatic PGA memory management mode, the main goal of Oracle is to honor the PGA_AGGREGATE_TARGET limit set by the DBA, by controlling dynamically the amount of PGA memory allotted to SQL work areas. At the same time, Oracle tries to maximize the performance of all the memory-intensive SQL operators, by maximizing the number of work areas that are using an optimal amount of PGA memory (cache memory). The rest of the work areas are executed in one-pass mode, unless the PGA memory limit set by the DBA with the parameter PGA_AGGREGATE_TARGET is so low that multi-pass execution is required to reduce even more the consumption of PGA memory and honor the PGA target limit.
When configuring a brand new instance, it is hard to know precisely the appropriate setting for PGA_AGGREGATE_TARGET. You can determine this setting in three stages:
PGA_AGGREGATE_TARGET, based on a rule of thumb.PGA_AGGREGATE_TARGET, using Oracle's PGA advice statistics.
With Oracle9i Release 2 (9.2), you can use the FILESYSTEMIO_OPTIONS initialization parameter to enable or disable asynchronous I/O or direct I/O on file system files. This parameter is platform-specific and has a default value that is best for a particular platform. It can be dynamically changed to update the default setting.
Starting with Oracle9i Release 2 (9.2), MTTR advisory is available to help you evaluate the effect of different MTTR settings on system performance in terms of extra physical writes.
When MTTR advisory is enabled, after the system runs a typical workload for a while, you can query V$MTTR_TARGET_ADVICE, which tells you the ratio of estimated number of cache writes under other MTTR settings to the number of cache writes under the current MTTR. For instance, a ratio of 1.2 indicates 20% more cache writes.
By looking at the different MTTR settings and their corresponding cache write ratio, you can decide which MTTR value fits your recovery and performance needs. V$MTTR_TARGET_ADVICE also gives the ratio on total physical writes, including direct writes, and the ratio on total input and output, including reads.
Oracle9i Release 2 (9.2) provides the initialization parameter STATISTICS_LEVEL, which controls all major statistics collections or advisories in the database. This parameter sets the statistics collection level for the database.
BASIC, TYPICAL, or ALL. The default is TYPICAL.
| See Also:
"Setting the Level of Statistics Collection" for details about what statistics are collected at each level |
With Oracle9i Release 2 (9.2) and higher, you can gather segment-level statistics to help you spot performance problems associated with individual segments. Collecting and viewing segment-level statistics is a good way to effectively identify the hot table or index in an instance.
After viewing wait events or system statistics to identify the performance problem, you can use segment-level statistics to find specific tables or indexes that are causing the problem.
You can query segment-level statistics through the new dynamic views V$SEGMENT_STATISTICS, V$SEGSTAT, and V$SEGSTAT_NAME.
Oracle9i Release 1 (9.0.1) introduced a new dynamic performance table, V$SQL_PLAN, to show the execution plan for a cached cursor. Oracle9i Release 2 (9.2) introduces another dynamic performance table, V$SQL_PLAN_STATISTICS. This view provides, for each cached cursor, the execution statistics of each operation in the execution plan.
To view row source statistics in this view, the DBA must set the parameter STATISTICS_LEVEL to ALL.
An additional view, V$SQL_PLAN_STATISTICS, concatenates information from V$SQL_PLAN with execution statistics from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA. V$SQL_WORKAREA contains memory usage statistics for row sources that use SQL memory (for example, hash-join and sort).
Oracle Trace will be deprecated in a future release. Oracle Corporation strongly advises the use of SQL Trace and TKPROF instead.
|
 Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|