If your high volume SQL is using an index, it may be a problem. There are 6 main types of index scan; in Explain Plan you will see one of the following:
[UNIQUE] INDEX RANGE SCAN
Oracle is reading 0 or more contiguous rows from the index.
[UNIQUE] INDEX RANGE SCAN (MIN/MAX)
Oracle is identifying 0 or more contiguous rows in the index, but is reading only one (the first or the last) in order to satisy a MIN or MAX aggregate function.
UNIQUE INDEX UNIQUE SCAN
Oracle is reading 0 or 1 rows from the index.
[UNIQUE] INDEX FULL SCAN
Oracle is reading all rows from the index, and may be accessing these rows in the underlying table.
[UNIQUE] INDEX SKIP SCAN
Oracle is reading 0 or more rows from different parts of the index, and may be accessing these rows in the underlying table.
[UNIQUE] INDEX FAST FULL SCAN
Oracle is reading all rows from the index, and is not accessing these rows in the underlying table. ie. The index contains all columns required to resolve the query without having to lookup the table.
In a high volume SQL, the last index scan type above is not a problem. With the first five types, they are usually only a problem if Oracle is also performing a table access along with the index scan. The table access will appear in your Explain Plan immediately above the index scan. eg:
TABLE ACCESS BY INDEX ROWID table_name UNIQUE INDEX RANGE SCAN index_name
If your SQL is not performing a table access, but the index uses a RANGE SCAN (but not a RANGE SCAN MIN/MAX), you could still have a Range Scan problem, where the range scan is processing a lot more rows than you expect. Otherwise, an index scan that does nore have a TABLE ACCESS is not necessarily a problem; try performing a full table scan instead and compare the performance.
Does the full table scan perform better? If not, then one of the following will apply:
EXPLAIN=uid/pwdand check how many rows are selected from the index. If the TK*Prof row counts show zeros, make sure you exited your session before running TK*Prof, or check with the DBA in case that feature has been disabled on your database. If the rows selected from the index are less than say 4% of the total rows in the table or partition, then Oracle is probably right to use the index.