Release 1 (9.0.1)
Part Number A87503-02
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Database Performance Guide and Reference Release 1 (9.0.1) Part Number A87503-02 |
|
This section describes new performance features of Oracle9i release and provides pointers to additional information. The features and enhancements described in this section comprise the overall effort to optimize server performance.
You can now set the OPTIMIZER_MODE initialization parameter to this value. With this, the optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return first n number of rows (where n can equal 1, 10, 100, or 1000). There is also a new FIRST_ROWS(n) hint (where n can be any positive integer). FIRST_ROWS is available for backward compatibility and plan stability.
The CBO now peeks at the values of user-defined bind variables on the first invocation of a cursor. This lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals had been used instead of bind variables. When bind variables are used in a statement, it is assumed that cursor sharing is intended and that different invocations are supposed to use the same execution plan.
Also, the CURSOR_SHARING parameter can now be set to SIMILAR to force similar statements to share SQL by replacing literals with system-generated bind variables. Replacing literals with bind variables improves cursor sharing with reduced memory usage, faster parses, and reduced latch contention. The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans. There is also a new hint: CURSOR_SHARING_EXACT.
You can find indexes that are not being used by using the ALTER INDEX MONITORING USAGE functionality over a period of time that is representative of your workload.
You can now gather system statistics, which allow the optimizer to consider a system's I/O and CPU performance and utilization. For each plan candidate, the optimizer computes estimates for I/O and CPU costs. It is important to know the system characteristics to pick the most efficient plan with optimal proportion between I/O and CPU cost.
The following hints are new with 9i: NL_AJ, NL_SJ, CURSOR_SHARING_EXACT, FACT, NO_FACT and FIRST_ROWS(n).
You can now edit private outlines. While the optimizer usually chooses optimal plans for queries, there are times when users know things about the execution environment that are inconsistent with the heuristics that the optimizer follows. By editing outlines directly, you can tune the SQL query without having to alter the application.
The DBMS_OUTLN package (synonym for OUTLN_PKG) and the new DBMS_OUTLN_EDIT package provides procedures used for managing stored outlines and their outline categories.
The optimizer now calculates the cost of access paths and join orders based on the estimated computer resources, including I/O, CPU, and memory.
This new chapter describes how to design and create a database for the intended needs.
For systems where a file system can be used to contain all Oracle data, database administration is simplified by using Oracle-managed files. Oracle internally uses standard file system interfaces to create and delete files as needed for tablespaces, tempfiles, online logs, and controlfiles.
You can now specify in seconds the expected "mean time to recover" (MTTR), which is the expected amount of time Oracle takes to perform recovery for an instance. The FAST_START_IO_TARGET parameter has been deprecated and should not be used.
The Statspack package builds off the traditional UTLBSTAT/UTLESTAT tuning scripts. Statspack automates the gathering of data, stores data and statistics, and generates performance reports. Statspack takes "snapshots" of data to work with, letting you choose the snapshot levels and thresholds to be used.
There are new sections describing Oracle Trace events, event sets, data items specific to the Oracle9i server, and Oracle Trace troubleshooting. The section on methods available for collecting Oracle Trace data has been reorganized. Oracle Trace Manager is no longer shipped, and the Data Viewer GUI tool is now described in Enterprise Manager documents.
With release 9i, it is now possible to simplify and improve the way the PGA is allocated in DSS systems. There is an automatic mode to adjust the size of the tunable portion of the PGA memory allocated by an instance dynamically. The size of that tunable portion is adjusted based on an overall PGA memory target explicitly set by the DBA.
It is now possible to resize the buffer cache and shared pool dynamically, while the instance is running.
This chapter is new in this book. It provides detailed information on the most important dynamic performance views that can help you tune your system and investigate performance problems.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|