Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost
Date: Fri, 29 May 2009 20:34:52 +0200
Jonathan Lewis pisze:
> "Grzegorz" <fgrzegorzof_at_finteria.pl> wrote in message > news:gvp1l6$jls$1_at_news.task.gda.pl...
>> 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
>> 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 188.8.131.52 .
> > > > 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
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