Вот такая ботва:
создаю таблички, заполняю, создаю индексы, собираю статистику.

create table emp (id number(10), name varchar2(50), birthday date);

begin
for i in 1..300000 loop
insert into emp values (i,'emp' || i, sysdate - 365*40-1/i);
end loop;
end;
/

create unique index pk_emp on emp(id);
create index emp_idx1 on emp(name);

analyze table emp compute statistics;

create table orders (id number(10), id_emp number(10), id_goods number(10));

begin
for i in 10..1000000 loop
insert into orders values (i, floor(i/10), 5);
end loop;
end;
/

create unique index pk_orders on orders(id);
create index orders_idx1 on orders(id_emp, id_goods);

analyze table orders compute statistics;

выполняю запос

SQL> select name, orders.id_goods
2 from emp, orders
3 where emp.ID = orders.ID_EMP
4 and emp.id > 1000;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=980 Card=990001 Bytes=18810019)

1 0 HASH JOIN (Cost=980 Card=990001 Bytes=18810019)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=116 Card=299001 Bytes=3887013)

3 1 TABLE ACCESS (FULL) OF 'ORDERS' (Cost=243 Card=990001 Bytes=5940006)

SQL> select /*+ FIRST_ROWS */ name, orders.id_goods
2 from emp, orders
3 where emp.ID = orders.ID_EMP
4 and emp.id > 1000;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=30077 Card=990001 Bytes=18810019)

1 0 NESTED LOOPS (Cost=30077 Card=990001 Bytes=18810019)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=177 Card=299001 Bytes=3887013)

3 2 INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=625 Card=299001)

4 1 INDEX (RANGE SCAN) OF 'ORDERS_IDX1' (NON-UNIQUE) (Cost=1 Card=3 Bytes=18)

Я конечно не против фул скана, но только если это действительно оправдано!!
А здесь - первый запрос отрабатывает 18 сек, а второй 0.02 секунды. Есть разница. Помогает также убитие статистики, тогда CHOOSE тоже не делает фул скан.
Есть подозрение, что с настройками параметров не все в порядке, однако optimizer_index_caching = 95 и otimizer_index_cost_adj = 10. Статью на сайте уважаемого killed'а я тоже читал.
Как думаете, как заставить CHOOSE выбрать другой план выполнения??

дык я хочу чтоб оно без хинта нормально работало!

otimizer_index_cost_adj=1 это ж минимум как я понимаю?
hash_area_size = 2048000

забыл сказать, у меня 9.2
соответственно табличное пространство localy managed и SEGMENT SPACE MANAGEMENT AUTO. Теперь про параметры:
db_block_size = 8192
db_file_multiblock_read_count = 16
у TEMP - UNIFORM SIZE 1024K
avg Row len для emp - 26 для orders - 16

подскажите как узнать max IO buffer для операций чтения с диска.

2 non:
Не хочу я хинты использовать...

2 ShgGena:
Т.е. мне поможет увеличение hash_area_size ? Но до каких размеров? Если я правильно посчитал обе таблицы весят около 30М эдак никакой памяти не хватит..

2 Scott Tiger:
а какой размер hash_area_size?
и за какое время выолняется хинтованый и не хинтованый запрос?
Оптимизатор понятное дело прав, только надо под его правоту еще параметры подогнать :-))
А железо разное есть, есть тачка с обычным винтом на 20Гб Виндами камнем 1ГГц памятью 512М, а есть Sun двухпроцессорный со сказевыми винтами и гигабайтом памяти, ессно с соляркой.

Вроде разобрался.
Спасибо всем принявшим участие и ткнувшим носом :))