RE: Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <breitliw@xxxxxxxxxxxxx>
- Date: Tue, 21 Nov 2006 20:02:13 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Wolfgang Breitling
Sent: Tuesday, November 21, 2006 5:48 PM
To: kennaim@xxxxxxxxx
Cc: 'Jonathan Lewis'; oracle-l@xxxxxxxxxxxxx
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
______________________________________________________________________
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
