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.