| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
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 Corporation
www.centrexcc.com
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 21 2006 - 16:48:18 CST
 |
 |