Long Parses

From time to time I have to fight against long parses. In this post I would like to share with you what happened to a customer of mine while upgrading from 9.2.0.7 to 10.2.0.3 (10.2.0.4 was not yet available at that time).

The parse of a complex SQL statement took 5 seconds in 9.2.0.7. Not very fast… But, for a report that is rarely executed, it is not a disaster. The problem was that after upgrading to 10.2.0.3 the parse time when up to more than one hour! Of course:

• It was no longer acceptable.
• The problem was caused by a bug (in 10.2.0.4 the parse lasts 20 seconds).

In case of long parses that are executed only a few times as in this case, it is usually not possible to avoid the parse. In fact, the SQL statement must be parsed at least once. In addition, if the SQL statement is rarely executed, a hard parse is probably inevitable because the cursor will be aged out of the library cache between executions. This is especially true if no bind variables are used. Therefore, the only possible solution is to reduce the parse time itself. What causes long parse times? Commonly, they’re caused by the query optimizer evaluating too many different execution plans. This means that to shorten the parse times, you must reduce the number of evaluated execution plans. This is generally possible only by forcing an execution plan through hints or stored outlines.

In this case after creating a stored outline the parsing time went down to one second. However, the solution (workaround) implemented in this case was another one…

A careful comparison of the query optimizer’s configuration of both databases pointed out that the 9.2.0.7 was running with the following undocumented parameters set (it goes without saying that nobody remembered why…):

• _b_tree_bitmap_plans = FALSE
• _new_initial_join_orders = FALSE

By setting these two undocumented parameters in 10.2.0.3 the parse time when down to 18 seconds. The customer got the consent from Oracle Support to set them in production and, therefore, so they did.