Ric Just some spontaneous thoughts: - Have you run truss on your server process to see if it's for some reason hanging / spending a lot of time in a certain syscall ? - Tanel poders snapper, or "OS explain" could be starting points if truss reveals too much or even nothing useful Stefan ========================= Stefan P Knecht CEO & Founder s@xxxxxxxx 10046 Consulting GmbH Schwarzackerstrasse 29 CH-8304 Wallisellen Switzerland Phone +41-(0)8400-10046 Cell +41 (0) 79 571 36 27 info@xxxxxxxx http://www.10046.ch ========================= On Wed, May 6, 2009 at 7:55 PM, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx>wrote: > 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.* > > > > > > >