| 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
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.
Thanks,
Ken
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Wolfgang Breitling
Sent: Tuesday, November 21, 2006 5:48 PM
To: kennaim_at_gmail.com
Cc: 'Jonathan Lewis'; oracle-l_at_freelists.org
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
(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-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 21 2006 - 19:02:13 CST
 |
 |