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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 21 Nov 2006 22:16:19 -0000
Message-ID: <01d501c70dba$aba4a240$0300a8c0@Primary>

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

Received on Tue Nov 21 2006 - 16:16:19 CST