-
we have recently installed the release oracle 8.1.x for a SAP system release 40B. Our system is on 32 bits and SAP suggested us dissable some features for the optimizer (8.1.6).
We have had many problems with sequential read on differents tables and yesterday we have a crash of the system and we don´t know the reason.
Are There any one who explain me the new features of the optimizer for release 8.1.X and if these can be disabled to improve the performance and eliminate the probability of crash in our system again.
oal
-
Here is an article from metalink, and hope that this would help you,
Explanation for Cost-based Optimizer and Rule-based Optimizer
=============================================================
To most, the best plan is the one that results in the fastest response time
(first_rows hint). To others, the best plan is the plan that uses minimum
amount of database resources (all_rows hint). In general, common goals of SQL
tuning include :
* Alleviating full-table scans by creating indexes whenever the cost of the
index is smaller than the cost of waiting for the full-table scan.
* Ensuring that the Oracle SQL optimizer is using all index resources
properly.
* Using bitmapped indexes whenever appropriate.
* Using cost-based hints to improve the performance of SQL queries.
-----------------------------------------------------------------------
SQL Optimizers
==============
Cost-based optimizer (henceforth CBO) can be used from release 7.2 and
above, while rule-based optimizer (henceforth RBO) is recommended for
releases 7.1 and below. When using the rule-base optimizer, the indexing of
tables and order of clauses within an SQL statement control the access path.
The CBO automatically determines the most efficient execution path, and hints
can be added to a query to alter the access path. CBO or RBO can be set in the
"init.ora" using the following parameter and values associated with it :
+ optimizer_mode=rule This will invoke the rule-based optimizer
regardless of the presence of statistics on tables and indexes.
+ optimizer_mode=all_rows or first_rowsThis will use the cost-based
optimizer and will estimate statistics at runtime if table and index
statistics do not exist.
+ optimizer_mode=choose The cost-based optimizer will be invoked whenever
statistics exist for any of the tables in a query. The rule-based
optimizer will be used to service a query if all the tables in the query
do not contain statistics.
When you give Oracle the ability to choose the optimizer mode, it will
favour the CBO if any table in a query has statistics (in other words table is
analyzed ). And if this happens, Oracle will issue an ANALYZE TABLE ESTIMATE
STATISTICS at runtime, slowing down any queries.
With the RBO, table names are read from right to left. In a CBO, table
names are read from left to right. Hence, it is important to know the driving
table, which has the smallest number of rows in a query. Despite enhancements
to CBO, many systems still perform better with RBO. Enhancements such as
bitmapped indexes and STAR query hints have been offered for CBO in Oracle8,
useful in data warehousing kind of applications
Oracle Applications users please note that the CBO is used by default
only from version 11i. All earlier versions require optimizer_mode=choose in
"init.ora".
Cost-Based Optimizer
====================
CBO works by weighing the relative 'costs' for different access paths to the
data, and choosing the path with the smallest relative cost. The CBO uses
statistics derived from tables and indexes, and, in 7.3 and above column value
distribution statistics can also be gathered. There are three ways to invoke
the CBO :
o set the optimizer_mode = all_rows or first_rows
o ALTER SESSION SET OPTIMIZER_GOAL=all_rows or first_rows
o Use of hints /*+ all_rows */ or /*+ first_rows */
The 'costs' for a query are determined with the aid of table and index
statistics that are computed with the following commands :
sql>analyze table estimate statistics sample 10%;
sql>analyze index compute statistics;
NOTE: It is important that the statistics are refreshed periodically,
especially when the distribution of data changes frequently. Hints override all
settings for optimizer_mode and optimizer_goal , while optimizer_goal overrides
optimizer_mode settings. The optimizer_mode settings only take effect when
neither optimizer_goal settings nor hints are present. Because hints are coded
into queries as comments, SQL will not indicate an error if a hint is
improperly entered. So it is necessary to ensure that hints are properly stated
within your queries.
When issuing the ALTER SESSION SET OPTIMIZER_GOAL command, it is important to
remember that the command will not take effect on SQL that is already in shared
pool because Oracle will directly load the plan from the shared pool, ignoring
the new setting for OPTIMIZER_GOAL. The two ways to get around this problem :
o Issue the ALTER SYSTEM FLUSH SHARED POOL command.
o Slightly alter (by adding spaces or changing the case of the characters) the
SQL statement to make it different from the one in the shared pool.
Rule-Based Optimizer
====================
In RBO, the ordering of the table names in the FROM clause determines the
driving table. The driving table is important because it is retrieved first,
and the rows from the second table are then merged into the result set from the
first table. Following are some tips while using RBO :
+ Try changing the order of the tables listed in the FROM clause. Joins
should be driven from tables returning fewer rows and they should be
listed last in the FROM clause.
+ Try changing the order of the statements in the WHERE clause. Oracle
parses the SQL from the bottom of the SQL statement in the reverse order
with boolean expressions separated by ANDs. Therefore, the most
restrictive boolean expression should be on the bottom.
+ Be careful when creating indexes, especially while using RBO. RBO
does not know the about the selectivity or distribution of the index
column, as in the case of CBO where the tables and indexes are analyzed.
+ Always use EXPLAIN PLAN to examine the access path.
+ Understand which query paths are the fastest. For example, accessing a
table by ROWID is the fastest access method, whereas a full-table scan is
listed at the end for the ranking of query paths.
+ In Oracle8, the use of arrays significantly reduce database I/O. If there
are 1000 rows to be selected an array size of 100 will reduce the reads to
10 as opposed to 1000 if arrays are not used.
RBO fails at times to recognize the most effective index that needs to be
used while doing a select. This would lead to a slow-running query and would
be necessary to disable the index by mixing data type on the index (where
numeric_column=123||'') or use the INDEX hint. With CBO, the issue is resolved
when the tables and indexes are analyzed, allowing the CBO to choose the best
execution path based on the statistics available.
Thanx
Sam
Life is a journey, not a destination!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|