RE: Long Parse Time
- From: "Tanel Poder" <tanel@xxxxxxxxxx>
- To: <ric.van.dyke@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 May 2009 21:47:29 +0300
Hi Ric,
As you're on Solaris 10, you can use my DStackProf (which uses DTrace) to
capture and aggregate a bunch of stack traces of that parsing process
(during parsing of course) and send the couple bottom ones back to the
list...
http://blog.tanelpoder.com/2008/09/02/oracle-hidden-costs-revealed-part2-usi
ng-dtrace-to-find-why-writes-in-system-tablespace-are-slower-than-in-others/
Or if you don't have OS access for some reason then you can use my
OStackProf (which uses oradebug short_stack) to do the same via sqlplus -
but this is experimental and not 100% safe due some issues with oradebug
short_stack.
http://blog.tanelpoder.com/2008/10/31/advanced-oracle-troubleshooting-guide-
part-9-process-stack-profiling-from-sqlplus-using-ostackprof/
This would give us the Oracle kernel modules where most of time is spent and
this would give us the clue where the problem may be happening.
The above would be systematic approach for resolving such issues, if you
can't do it for some reason then another approach would be disabling some
optimizer's features by (undocumented) parameters and seeing which setting
makes the query parse fast. Obviously one thing which probably makes the
parsing fast is adding ORDERED hint, but that will affect your query plan.
Another things to disable and test are query_rewrite_enabled,
_simple_view_merging, _complex_view_merging, _unnest_subquery,
_optimizer_cost_based_transformation etc. These are just some common
troublemakers that come into my mind.
Disclaimer: I mention these only in context of troubleshooting your current
issue - disable a parameter and see if parsing goes faster - if it does,
search metalink by that parameter to see whats the cause. I do not advise
these as any kind of tuning parameters in this case!
--
Tanel Poder
http://blog.tanelpoder.com
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ric Van Dyke
Sent: 06 May 2009 20:56
To: oracle-l@xxxxxxxxxxxxx
Subject: Long Parse Time
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
System name: SunOS
Release: 5.10
I'm working on a query that takes about 30 seconds to parse. Run time is
not and issue, it runs in .12 seconds once parsed. Its the parse time is a
problem. Yes I know the mantra "parse once execute many", just forget that
for a moment and assume that the parsing of this statement must be reduced.
The query original was based on a view that was based on several views, and
finally hit the base tables 2-3 layers down. I've got the query now only
hitting the base tables and it still takes about 30 seconds to parse. It
hits 8 tables which I would not expect to be a huge deal.
The 10053 trace file is over 200 Megs. Reading thru it has been interesting
but still less then clear why this thing is taking that long to parse.
Using a RULE hint in one of the views did reduce the parse time.
None of the predicates are very complex, there are two correlated
sub-queries, one like and the others are run of the mill equality
predicates.
My searches on Metalink/Google have not turned up any bugs or the like.
Would anyone happen to have to ideas on what would cause the optimizer to
take this long to parse a statement? Is anyone aware of something that
would cause the optimizer to do something like this? I'm just looking for
some clues as to what to investigate. I was pretty sure it was a view
resolution issue, but now that seems not the case.
-----------------------
Ric Van Dyke
Hotsos Enterprises
-----------------------
Hotsos Symposium
March 7 - 11, 2010
Be there.
Other related posts:
