1. Запросы.

• Необходимо постоянно отслеживать выполняемые на сервере запросы. Для этой цели хорошо подходит программа Spotlight фирмы Quest.

• С её помощью можно легко отыскивать "тяжелые" запросы, просмотреть планы их выполнения, количество их запусков и т.д. В первую очередь следует оптимизировать запросы с большими показателями "Disk Read" и "Buffer Gets"

• Можно просмотреть большие запросы с ORDER BY. Если сортировка осуществляется (или может осуществляться ) на

уникальном поле таблицы, то следует рассмотреть вариант с использованием DISTINCT.

2. Индексы.

К сожалению статистики применения индексов в Oracl-е ниже 9-й версии не предусмотрено, а изменения в составе индексов могут сказаться очень сильно ( причём в обе стороны :). Поэтому новые индексы следует вводить постепенно с обязательным контролем со стороны пользователей.
Желательно, при изменении состава индексов, просмотреть все запросы, касающиеся интересуемой таблицы. С помощью Spotlight это сделать очень легко.
• Дублирование индексов
  Часто в базе есть индексы, дублирующие друг друга. Это происходит когда есть, например, индекс на поле А и составной индекс на поля А и Б. Первый индекс на поле А чаще всего можно безболезненно удалить.
  Запрос на поиск таких индексов тут.
• Контроль размера индексов.
  Встречаются индексы, размеры которых больше самой таблицы.
  Можете не улыбаться, по недосмотру или недопониманию это вполне возможно. Если таблица достаточно статичная, то такие индексы нужно пересоздать с соотв. параметром INITIAL.
  Запрос на поиск таких индексов тут.
• При проверке "тяжелых" запросов часто можно отыскать недостающие индексы.