Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs

RE: Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs

From: Wolfgang Breitling <>
Date: Tue, 21 Nov 2006 15:48:18 -0700
Message-Id: <>

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
>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.


Wolfgang Breitling
Centrex Consulting Corporation

This email has been scanned by the MessageLabs Email Security System. For more information please visit
Received on Tue Nov 21 2006 - 16:48:18 CST

Original text of this message