Is it possible that setting the statistics for the GTT tables fails when run as a scheduled job. If the optimizer assumes that the GTTs are empty that could explain that it goes for an NL join. I have seen that happening a lot in the Peoplesoft environment with its "temporary" tables ( not GTTs but real tables which usually are empty, especially when the weekly stats job runs :-( )
At 03:33 PM 11/21/2006, Ken Naim wrote:
I apologize, I changed the scenario on you to narrow the scope of the issue down, I took out the third table and just did a full outer join of two of the tables and got the same poor execution, just it took 15 minutes (instead of 35) now for the two tables. I am not sure if the oracle scheduler would is changing the session parameters, how can I check? Based on what you were saying about the nested loops I looked back at some code I wrote a while back that this code was loosely based on and compared them and found that my old code had GTT's with primary keys and my new ones didn't so I added them and the code now runs with a an index read instead of a full scan under the filter section which now runs in the normal 10 seconds. It is just so odd that the scheduler caused it to do nested loops instead of a full scan and the explain plan didn't even show it. I appreciate your effort not only in helping me resolve this issue, but in educating me how to handle these issues in the future.
Regards Wolfgang Breitling Centrex Consulting Corporationwww.centrexcc.com
______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System.For more information please visit http://www.messagelabs.com/email ______________________________________________________________________
-- //www.freelists.org/webpage/oracle-l
