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: Ken Naim <>
Date: Tue, 21 Nov 2006 20:02:13 -0500
Message-ID: <023601c70dd1$d9f84180$96b016ac@KenHome>

In this case it wouldn't matter since they still would have the value from the previous run which is identical in value since I am testing for the same period of time. I just set them each time incase the volume changes in the future. I got into this habit after having a GTT that normally contained 100-500 or so records at time, but one day we had to processes 300k records so the nested loop plan was not good to say the least.

I just check the dba_tables view and the GTT's are getting updated via the scheduler. It is just odd why the v$sql_plan showed hashjoin and internally was doing a nl plan.


-----Original Message-----
From: [] On Behalf Of Wolfgang Breitling
Sent: Tuesday, November 21, 2006 5:48 PM To:
Cc: 'Jonathan Lewis'; Subject: RE: Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs

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
>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
>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
>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 - 19:02:13 CST

Original text of this message