Kbase 14042: ORACLE: RULE-BASED and COST-BASED OPTIMIZATION (Performance)
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/10/1998 |
|
ORACLE: RULE-BASED and COST-BASED OPTIMIZATION (Performance)
In 7.1.x of the ORACLE product COST-BASED optimization is now the
default. When moving from version to 7.0.x to 7.1.x customers may
encounter what is known in a full table scan which can result in
slower performance. In version 7.0.x RULE-BASED optimations was bein
g used and they may have not had any problems. In this case you can
suggest that they try first to monitor the perfomance with the COST-BA
SED optimization and then try the RULE-BASED optimization. The type
of optimization is determined by variable OPTIMIZER_MODE entry in the
init.ora file. The mode that is set in this file decides the query
optimization for the entire instance. You can override the default f
or the session in SQL*Plus or by using a SEND-SQL statement in Progres
The following shows how to change the session and the types of modes
you can use for session.
sqlplus username/password
sql> alter session set optimizer_goal = <mode>;
OPTIMIZER_MODE MEANING
--------------------------
RULE Use the rule-based approach
ALL_ROWS Use the cost-based approach with the goal of the
best throughput for all rows that satify the
selection critieria.
FIRST_ROWS Use the cost-based approach with the goal of the
best response time for the first row that will
be retrieved.
CHOOSE Use the cost-based approach with the goal choice
decided by the optimizer at execution time.
Progress Software Technical Support Note # 14042