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

There is probably a change in execution plan, due to the switch from pga_aggregate_target to manual workareas.

When you generated the plans, did you try setting the workarea_size_policy, sort_area_size and hash_area_size to the batch values before using explain plan to see if things changed ?

Do you populate the temporary tables with realistic data before running the explain plan ? (Which might make a difference if you are 10g, or 9i with a non-default value for
dynamic sampling).

The extreme number of tablescans suggest a problem either with a non-mergeable view, or with a nested loop join where the second (inner) table is being scanned. Either problem could occur if the optimizer had produced an estimated cardinality of one row on a critical table - therefore allowing a 'for each row' approach to the next table to be a full scan.

A common example of this problem is a
NOT IN, or NOT EXISTS clause that
Oracle turns into an anti-join - using a nested loop anti-join when the human eye sees the obvious need for a hash anti-join.

Given you can get at v$mystat etc. at the end of the batch process - could you also get at v$sql_plan for the statement. Put an tag into to to make it easy to find.

select /*+ this_is_a_tag */ .....

select hash_value, child_number from v$sql where sql_text like '%this_is_a_tag%';

select ... from v$sql_plan
where hash_value = .. . and child_number = ...

Then you can find what actually happened.

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_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Sent: Tuesday, November 21, 2006 7:23 PM Subject: RE: Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs

> Taking your suggestion, I trapped the v$mystat data and compared them
> between the runs, seems when called by the schedule it does 30000 times more
> full scans (from 11 to 316,889) of the global temporary tables. Below is an
> extract of any stat with over a 200% or larger increase between runs. Any
> idea what could cause this. I Also noticed in the trace file it didn't show
> an explain for this insert as select even though it did for others, any way
> to force it to show it.
>
> Thanks much,
> Ken
>
>
> NAME
> REGULAR_CALL_VALUE
> SCHEDULED_CALL_VALUE
> DIFF
> percent_diff
>
> number of map operations
> 73,554
> 33,125,668
> 33052114
> 449
>

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