Resolving The Problem

The optimization mode that is set for an Oracle database can significantly impact the performance of RequisitePro in executing queries. The optimization mode of an Oracle database is set through the init.ora parameter optimizer_mode.

Rule based optimization:
Rule based optimization is a syntax driven optimization mode for executing SQL statements and is supported for backward compatibility with earlier releases of the Oracle server. Using rule based optimization is most effective when experienced Oracle developers who have a very good understanding of rule based optimization syntax rules tune application SQL statements to comply with these rules. If you do not have the ability to tune SQL statements, rule based optimization is probably not an effective optimization choice. RequisitePro does not permit application SQL statements to be tuned so using rule based optimization with RequisitePro may decrease performance.

Cost-based optimization:

Cost-based optimization is statistics-driven in that it uses statistics generated for the objects involved in a SQL statement to determine the most effective execution plan. In general Oracle recommends using cost based optimization mode. You must generate and maintain current statistics on database objects for the cost based approach to be most effective. RequisitePro is tested with databases using cost based optimization. In general cost based optimization delivers better performance than rule based optimization when executing RequisitePro SQL statements.

In some databases the init.ora parameter will be set optimizer_mode will be set to the default value of Choose. This value indicates that if statistics are available for database objects referenced in a query cost based optimization will be used. If statistics are not available rule based optimization is used. It is important to keep statistics on RequisitePro tables and indexes current to make use of the cost based optimization most effective.