RE: Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: "'Jonathan Lewis'" <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Nov 2006 17:33:04 -0500
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@xxxxxxxxxxxxxxxxxx]
Sent: Tuesday, November 21, 2006 5:16 PM
To: kennaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx>
To: "'Jonathan Lewis'" <jonathan@xxxxxxxxxxxxxxxxxx>;
<oracle-l@xxxxxxxxxxxxx>
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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
