Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost

From: Grzegorz <>
Date: Fri, 29 May 2009 20:34:52 +0200
Message-ID: <gvp9sa$bbf$>

Jonathan Lewis pisze:
> "Grzegorz" <> wrote in message 
> news:gvp1l6$jls$

>> Hi,
>> I'm doing some investigations
>> because of query plan changing in the same session (from NL Index RS to
>> HJ FTS), and got question about Hash_Join_Area_Size when
>> work_area_policy=auto.
>> Is that possible that the same session can have different
>> HJAS for same query ?
>> When auto pga allocation is in place its quite possible in my opinion.
>> Looks like depending on instance workload (in PGA terms) some queries may
>> switch between NL Index RS and HJ FTS in the same session.
>> I'm on .
>> Regards.
>> GG
> The cost calculation for the hash join is based on a number
> derived from the pga_aggregate_target parameter, not from
> the amount of memory currently available; so load doesn't
> affect the plan.  As JBH implied, a difference in the input
> bind variables (especially where histograms are involved)
> could be the reason why you see different plans at different
> times from the same session.

Thank You Jonathan.
My problem is I cant reproduce in test env plan switch issue even when using extreme bind variables values.
The only switch I've got was when I've manipulated hash_area_size value (changing Work Area Policy to manual or db_file_multiblock_read_count). I've imported 'bad' statistics from production , the data comes from production snapshot (so its binary copy). Both environments got same optimizer related parameters , except PAT in prod = 4GB in test = 131 MB .
So in test I've got NL with Index RS (which is optimal) and in PROD in some cases (think because of histograms + BV combo probably) I've got FTS on large table + hash join with second one (accessed by Index Range Scan).

Query looks like (cant provide You with exact text right now but its simple):

both tables about 15M rows

select distinct a.col1 from tab1 a, tab2 b where
a.col1 = b.col2
and a.col2 = '1' --highly unselective (90% of values) and b.col3 = '1' --highly unselective (90% of values) and (b.col3 = :1 or b.col3 = :2) --selective (varchar2(11) type with hight histogram 75 buckets not null ) with a little skew in some values but its like 1% of total rows .

The catch is :2 for b.col3 is equal to '2' sometimes ('2' not exists in b.col3) .

The above query return few rows (distinct is avoidable), but sometimes plan changes to full scan on tab1 .

And as I said, even when using skewed values for :1 and :2 ('2' especially). I cant reproduce HJ + FTS issue. Stats are gathered via dbms_utility.gatcher_schema_stats low sample = 10000 an for ALL COLUMNS (so 75 buckets ). I know its bad , but still fighting with manager :D.
GG Received on Fri May 29 2009 - 13:34:52 CDT

Original text of this message