Оптимизация - тема довольно широкая, и охватить её на одной страничке невозможно, однако я хочу только показать несколько стандартных подходов к решению, не с целью научить "как жить" а только чтобы продемонстрировать некоторые свои навыки в этом вопросе.

Процесс ест слишком много памяти или ресурсов CPU
    Например мы обнаружили периодически или постоянно возникающий на сервере процесс, который ест слишком много памяти или процессорного времени. Первое что необходимо сделать - это узнать его PID (Process ID). На Unix-подобных системах мы его увидим непосредственно в выводе команды top или ps. На windows часть процессов распараллеливается на нити и их pid не видны в диспетчере задач. Чтобы узнать требуемый PID я предпочитаю использовать программы третьих фирм например Process Explorer от Sysinternals. (в данный момент скачать можно на microsoft technet). Когда PID известен, я делаю запрос к БД вида
select
    ses.*,
    ps.SPID
  from
     v$session ses,
     v$process ps
  where
     ps.ADDR = ses.PADDR and
     ps.SPID = 'xxxx'
где xxxx - это ID "жручего" процесса.
В полученных данных сразу можно увидеть много полезного.
Первое - это столбцы SID и SERIAL# - этих столбцов достаточно чтобы сделать kill session.
    В случае взаимодействия типа клиент - сервер интерес представляют столбики USERNAME MACHINE PROGRAM которые сразу дают представление о приложении из которого сделан запрос загрузивший сервер.
    В случае OEBS эти столбики довольно бесполезны, т.к. там будет практически одна и та же информация, зато в полях MODULE и ACTION можно увидеть модули которые работают в этих сессиях и разобрать пользователя и конкретную форму откуда идёт обращение в случае если пользователь работает внутри форм.
    Когда проблема локализована вплоть до сессии и модуля,  то можно заглянуть в представления в которых можно посмотреть все текущие запросы и длительные запорсы которые были произведены за последнее время : V$SESSION_LONGOPS, V$SQLAREA

Конкретного "тяжёлого" процесс не видно, но система всё равно "тормозит"
    Причин такого поведения может быть масса. Разбираться конечно нужно в каждом конкретном случае.  Но что обычно может сделать администратор не залезая и не оптимизируюя код запросов и хранимых процедур? Настроить параметры физического расположения данных.
    Физическое расположение данных на дисковой системе лучше наверное планировать с учётом "классических" советов по планированию архитектуры хранилища. Например redo логи не должны лежать вместе с archive логами. И желательно чтобы это всё было отделено от основных файлов с данными.
    Физическое размещение в памяти в первую очередь зависит от режима работы БД - выделенный (dedicated) сервер или общий (shared). Конечно в зависимости от задач в первую очередь надо выбрать один из этих вариантов а потом уже оптимизировать распределение памяти между процессами и пулами. Помощь в подобного рода оптимизации могут оказать представления sys.v_$pgastat; sys.v_$sgastat.

Попробовать найти "тяжёлые" запросы которые могут появляться в различных сессиях.
    В этом плане незаменим запрос к представлению V_$SESSION_LONGOPS. Там в идеале должны быть только записями о работе RMAN - т.к. в нём никак не обойтись без полного сканирования таблиц и т.п. Если там появляется нечто другое - это сигнал к началу разбирательства. Как правило все проблемы в запросах решаются более качественным переписыванием оных, созданием индексов и изменениями параметров распределения памяти для процессов.

Витрины данных.
    Когда нужно представить сложный аналитический отчёт в котором, например, данные собираются из 20-ти представлений и таблиц, и в этих таблицах достаточно большое количество данных, а свяывание происходит не по полю, а , например по какому -либо агрегированному признаку, то тут конечно оптимизировать запрос трудно. Тогда можно поступить иначе - создать "витрину данных". т.е создать отдельную таблицу требуемого вида, а заполнять её например раз в сутки ночью. или повесить триггеры на изменяемые таблицы и добавлять на витрину данные по мере поступления.