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