There are two classes of low volume SQLs: (of course it possible for a single SQL to span both classes)
When accessing a small table (say with 500 or fewer rows) it is surprising how often a Full Table Scan will out-perform indexed access. Typically no SQL on a small table will run for very long at all, however the cumulative effect of a small performance loss over many iterations can become enormous. For example, a loss of one twentieth of a second (0.05 sec) over 100,000 iterations will add over 80 minutes to a report or batch job.
The way to tell which is better is to try both. Use an INDEX() or HASH() hint to encourage indexed / hash cluster access respectively, or FULL() to encourage a Full Table scan, and SQL*Trace both. Compare the TK*Prof output to determine which is faster.
A low volume SQL on a large table should always be using an index or hash cluster. Use Explain Plan to obtain the execution plan. Is it using the index or hash cluster you expect? The problem may be one of the following:
Is there an index covering your columns? Has it been dropped?
The Cost Based Optimizer may choose not to use the index, or there may be something in the SQL that stops the index being used.
There are two types of index scans - Unique Scan and Range Scan. A Unique Scan is guaranteed to read no more than 1 row, but a range scan can read any number of rows. A Range Scan may be processing more rows than you expect. Click on the above link to find out why this might be the case.
You think a table is hash clustered but it's really not.
You've gone to the effort of creating a hash cluster and now Oracle won't use it.
There is a particular problem with correlated sub-queries that blows out if your sub-query has to scan multiple rows.
When you access a hash cluster, it is possible that you may be reading more rows or more blocks than you think.