Temos vários hint's, "sugestões" algumas já obsoletas, mas vários que podem nos ajudar... eles são declarados como comentários.
Como por exemplo o first_rows, já me ajudou, mas é preciso fazer consultas analisando sua query, também com explan para verificar como o Oracle se comporta, esse hint ele força o uso de índice, para otimizador um caminho que retorna a 1º linha ou a quantidade de linhas que escolher mais rapidamente.
Exemplo:
select /*+ first_rows (500) */ coluna_1, coluna_2 from tabela;
O hint /*+ full_rows (tabela) */ força um scan completo na tabela, custo do acesso do índice e dos registros pode ser maior do que simplesmente ler a tabela inteira. Este hint também pode causar resultados inesperados, como varrer a tabela interia em ordem diferente da ordem de acesso, se for uma tabela com centenas de dados, não é aconselhável.
Exemplo:
select /*+ full_rows (tabela_user) */ coluna_1, coluna_2 from tabela_user where rownum <= 10;
A partir do Oracle 11g, temos uma view com mais de 250 Hint's v$sql_hint, se fosse de todo mau, não existiria mais.
Existem vários outros HINT's, mas entenda, sempre é bom se aprofundar no conhecimento, pois não têm uma receita de bolo, e sim uma melhor prática para cada caso. Vlw galera, qualquer dúvida é só compartilhar.
Referências: https://community.oracle.com/welcome
