Re: How to optimize query's execution?

The explain plan you posted does not really help. You need to actually run the query and gather information using extended sql_trace (do a search on sql_trace binds waits). Run the resulting output through tkprof with waits=yes sys=no. This will tell you what actually happened.

If you suspect that there might be a problem with the optimizer or statistics, compare the tkprof output with the output from an explain plan with the cardinality estimates. If the rows in tkprof and the cardinality estimates from the explain plan are not similar (don't expect them to be exact), you have to address the reason.

I know that this response does not provide you the answer you are looking for, but it provides you enough clues to locate the problem yourself with some research. Received on Tue Aug 30 2005 - 09:18:52 CDT