Re: Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <kennaim@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Nov 2006 22:16:19 -0000
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:
