Aug 31 2008
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.
September 1st, 2008 at 10:42 am
Can you explain, why they used 2 undocumented parameters in 10.2.0.3? They probably decrease the optimizers quality of the whole instance. Using a stored outline instead would help in this particular problem, but leave all other plans untouched.
Where other statements affected also?
September 1st, 2008 at 11:49 am
Hi Martin
I agree that this is not the best way to solve the problem. Actually, it’s not a solution, it’s a workaround.
It was not possible to use the stored outline because the SQL statement was dynamically generated and, in addition, no bind variables were used.
AFAIK only this one hit the problem with the long parse time.
Cheers,
Chris
September 12th, 2008 at 10:34 am
What about SQL Profiles? From what I am reading they can change DB parameters and statistics. Wouldn’t this be another option? To avoid confusion, I am talking about this:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i37675
Greetings
robert
September 15th, 2008 at 10:36 am
Hi Robert
One of the characteristics of SQL profiles is to *not* force the query optimizer to choose a specific execution plan. Probably for this reason, I never saw a SQL profiles containing access and join hints (I would really like to see one if somebody did such an encounter… sort of close encounter of the third kind…). Therefore, they are generally not helpful to solve (workaround) long parsing problems.
Best regards,
Chris
March 1st, 2010 at 5:14 pm
Hello guys
Can someone please send me the Oracle support response related to this issue as we are also planning to change that DB parameter?
Thank you!
Regards
Vineet
March 1st, 2010 at 5:30 pm
Hi Vineet
The service request was opened by the customer and, as a result, I do not have the response. In any case, if I were you, I would not base any decision on a response given to somebody else 1.5 years ago. Hence, I suggest you to open a service request and let Oracle Support have a look to your specific problem.
HTH,
Chris