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

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.

Thank you very much,
Ken Naim

-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Tuesday, November 21, 2006 5:16 PM To: kennaim_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs

Given your comments about numbers of rows, and the execution plan you have produced here, the number of extra tablescans you get in the stats is consistent with the hash join turning into a nested loop with full tablescan, and the filter operating in a way that results in a tablescan per row from the driving table.

I am, however, a little puzzled, because I can't see the 180K row table - only three tables with exactly the same cardinality prediction; and I can't see the filter doing its usual 5% adjustment to the cardinality of the first table.

Is it possible that your scheduler mechanism causes code to run with some other session parameters also adjusted - for example hash joins disabled ?

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "Ken Naim" <kennaim_at_gmail.com> To: "'Jonathan Lewis'" <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Sent: Tuesday, November 21, 2006 9:56 PM Subject: RE: Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs

>I call the procedure both from the scheduler and from the command line so
> both use same settings for the manual pga, sort and hash area. After each
of
> the 3 GTT's are populated via insert as select statements I use the
> sql%rowcount and dbms_stats to set the num_rows. So both methods are
exactly
> identical except one is called via the scheduler.
>
> Yes each time the proc run there are 150k record in 2 of the table and
188k
> in the 3rd table.
>
> I followed your advise, and logged the v$sql and v$sqlplan data and the
plan
> is identical via the scheduler to non scheduled one. I am at such a loss
at
> figuring this out.
>
> OPERATION OPTIONS OBJECT_TYPE OPTIMIZER ID PARENT_ID
> DEPTH COST CARDINALITY
> INSERT STATEMENT ALL_ROWS 0 0
> 119157
> VIEW 1 0 1
> 119156 142121
> UNION-ALL 2 1 2
>
> HASH JOIN OUTER 3 2 3
244
> 135353
> TABLE ACCESS FULL TABLE (TEMP) ANALYZED 4 3 4 21
> 135353
> TABLE ACCESS FULL TABLE (TEMP) ANALYZED 5 3 4 21
> 135353
> FILTER 6 2 3
>
> TABLE ACCESS FULL TABLE (TEMP) ANALYZED 7 6 4 26
> 135353
> TABLE ACCESS FULL TABLE (TEMP) ANALYZED 8 6 4 28
> 1354
>

--
http://www.freelists.org/webpage/oracle-l