0681-0683

Previous Table of Contents Next

Page 681

In either situation, a transaction was accessing the before picture of some data that was still in a rollback segment when the system was forced to reclaim that extent to use for a currently executing transaction. Because this before picture is no longer available, the executing transaction cannot continue. You can use three steps (separately or in conjunction with each other) to alleviate this problem:

1. Increase the size of your rollback segments.
2. Increase the OPTIMAL size of your rollback segments.
3. Reschedule your processing so that no two processes are updating and/or reading from updated tables while the other is running.

TIP

Make sure that your rollback segments are sized according to the largest common transaction that takes place. Create a large rollback segment to accommodate unusually large updates, and use the SET TRANSACTION command to force that transaction to use the larger rollback segment.

Using the Optimizer

Oracle's optimizer is a critical part in the execution of a transaction. The optimizer is responsible for taking a SQL statement, identifying the most efficient way of executing the statement, and then returning the data requested . There is a great likelihood that a SQL statement can be executed in more than one way. The optimizer is responsible for identifying the most efficient means of executing that statement.

Optimization can take many steps to complete, depending on the SQL statement. The steps used to execute the SQL statement are called an execution plan. Once the execution plan is completed, it is then followed to provide the desired results (updated or returned data).

Many factors govern how the optimizer creates an execution plan. These factors are based on the type of optimization method the database uses. At the database level, you have two types of optimization: cost based and rule based. The database parameter OPTIMIZER_MODE, located in the init.ora parameter file, determines which type of optimization mode your instance will use. The parameter has two possible values:

• Cost: Use cost-based analysis.
• Rule: Use rule-based analysis.

Page 682

CAUTION

Some words of wisdom: First, with each version of Oracle that is released, there are more modifications to the way the optimizer makes a statement more efficient. It is important to note that the optimizer in each version may function differently, and each statement passed to the optimizer may perform differently.

Second, although the optimizer makes every attempt to create an execution plan that is optimal in performance, the developer has the true knowledge of the data and its purpose. In some situations, the developer may be able to choose a more efficient means of executing the statement than the optimizer can. Whenever possible, the developer should use the EXPLAIN PLAN option of the database to examine the execution plan provided by the optimizer.

Cost-Based Analysis

Cost-based analysis is a mode of analyzing SQL statements to provide the most efficient way of execution. When the optimizer is running in cost-based mode, it follows these steps to decide which plan is the best way to execute the statement unless the developer has provided a hint to use in the execution:

1. Generate a set of execution plans based on available access paths.
2. Rank each plan based on estimated elapsed time to complete.
3. Choose the plan with the lowest ranking (shortest elapsed time).

Cost-based analysis uses statistics generated by the ANALYZE command for tables, indexes, and clusters to estimate the total I/O, CPU, and memory requirements required to run each execution plan. Because the goal of the cost-based approach is to provide maximum throughput, the execution plan with the lowest ranking or lowest estimated I/O, CPU, and memory requirements will be used.

The analysis used to provide the final cost of an execution plan is based on the following data dictionary views:

• USER_TABLES, USER_TAB_COLUMNS, USER_INDEXES, USER_CLUSTERS
• ALL_TABLES, ALL_TAB_COLUMNS, ALL_INDEXES, ALL_CLUSTERS
• DBA_TABLES, DBA_TAB_COLUMNS, DBA_INDEXES, DBA_CLUSTERS

Rule-Based Analysis

Rule-based analysis rates the execution plans according to the access paths available and the information in Table 27.1. The rule-based approach uses those rankings to provide an overall rating on the execution plan and uses the plan with the lowest ranking. Generally speaking, the lower the rating, the shorter the execution time ”although this is not always the case.

Page 683

Table 27.1. Access type ratings.

Ranking	Type of Access
1	Single row by ROWID
2	Single row by cluster join
3	Single row by hash cluster key with unique or primary key
4	Single row by unique or primary key
5	Cluster join
6	Hash cluster key
7	Indexed cluster key
8	Composite index
9	Single-column index
10	Bounded range search on indexed columns
11	Unbounded range search on indexed columns
12	Sort-merge join
13	MAX() or MIN() of indexed column
14	ORDER BY on indexed columns
15	Full table scan

Overriding the OPTIMIZER_MODE Parameter

Because the developer can sometimes optimize code more efficiently than the optimizer can, various directives, called hints, can be issued from within the SQL statement to force the optimizer to choose a different method of optimization. This method works at the statement level from within the transaction and affects only the current statement.

To affect all statements at the transaction level, the SQL command ALTER SESSION SET OPTIMIZER_GOAL can be used. This command overrides the OPTIMIZER_MODE initialization parameter and forces all statements within the current transaction to be optimized according to this value. This parameter has four possible values:

• CHOOSE. Tells the optimizer to search the data dictionary views for data on at least one related table (referenced in the SQL statement). If the data exists, the optimizer will optimize the statement according to the cost-based approach. If no data exists for any tables being referenced, the optimizer will use rule-based analysis.
• ALL_ROWS. Chooses cost-based analysis with the goal of best throughput.
• FIRST_ROWS. Chooses cost-based analysis with the goal of best response time.
• RULE. Chooses rule-based analysis regardless of the presence of data in the data dictionary views related to the tables being referenced.

Previous Table of Contents Next