> When I pass a hint of the same Index , it returns in seconds
> 1 recursive calls
ok, that's for parsing - if you execute the statment just using "/" a second time, that disappears. But that's neglectible.

> 1 db block gets
> 6,123,550 consistent gets

6.1 Mio x 8kb = 48.8 GB
How big is your table and how big is the index ?
Is this one partition or all partitions of index or table ?

> 6,058,079 physical reads

35 min = 2,100,000 ms / 6 mio phys reads = 0.35 ms/read => your operating system cache works fine :-) - but of course you should not have such much blocks accessed at all.

Please send this output (explain plan for...) for BOTH executions - with and without hint.
And what happens if you force it to a full-table scan ?
(I am just wondering if you get a INDEX-FULL scan or a Full-Table-Scan)

A full-table scan on a 6 GB table should 5 minutes (or 1 minute if you do it in parallel :-)

> 8144 redo size

==> That's most likeley from some delayed block cleanout from previous update changing more blocks than those which are "immediately on commit" cleaned.

But even if all 120 rows are in different blocks and you must visit the rollback-segment 120 times you should not get such many blocks to access.

> 8121 bytes sent via SQL*Net to client
> 120 rows processed

67 bytes/row (reasonable number)

> 9 SQL*Net roundtrips to/from client

Some of the 9 are for the parsing - but neglectible in our case

> 0 sorts (memory)
> 0 sorts (disk)

In your 2nd mail you post:

> 1* ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10
> Session altered.
> 120 rows selected.
> Elapsed: 00:22:52.09
> Statistics
> ----------------------------------------------------------
> 798 recursive calls

!!!! 798 !!!! recursive calls !!!!!
> 38 sorts (memory)

do you see parallel Query slaves ?
Once I did run into "parallel Index Range Scan" problem (after upgrade from 8.0.4 to 8.1.7 :-)) on an partitioned table.

Please make sure that you don't run into this situation

How do your data look like ?

select /*+ full(B) parallel(B 4) */ count(*), bld_seq_id B from building_info group by bld_seq_id order by 1;

(if data are not equally distributed -> Histograms...)

How small is your DB_BLOCK_BUFFER ? (used by optimizer to calculate index-cache-hit ratio)

OPTIMIZER_INDEX_COST_ADJ was already posted by LKBrwn_DBA

There is a second parameter:

OPTIMIZER_INDEX_CACHING default 0

(alter session set ...)

OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based
optimization to favor nested loops joins and IN-list iterators.
The cost of executing an index using an IN-list iterator or of executing a nested
loops join when an index is used to access the inner table depends on the caching of
that index in the buffer cache. The amount of caching depends on factors that the
optimizer cannot predict, such as the load on the system and the block access
patterns of different users.
You can modify the optimizer’s assumptions about index caching for nested loops
joins and IN-list iterators by setting this parameter to a value between 0 and 100 to
indicate the percentage of the index blocks the optimizer should assume are in the
cache. Setting this parameter to a higher value makes nested loops joins and IN-list
iterators look less expensive to the optimizer. As a result, it will be more likely to
pick nested loops joins over hash or sort-merge joins and to pick indexes using
IN-list iterators over other indexes or full table scans. The default for this parameter
is 0, which results in default optimizer behavior.