Что Вам говорит об этом запросе EXPLAIN ?

Что Вы хотите сделать для начало объясните..
Выглядит извращением.

EXPLAIN експортированный в CSV ниже. И да - БД Oracle
OPERATION OPTIONS OBJECT_NAME POSITION
"SELECT STATEMENT" (null) (null) 1738579
" HASH" "UNIQUE" (null) 1
" HASH JOIN" (null) (null) 1
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" "RIGHT OUTER" (null) 2
" INDEX" "FAST FULL SCAN" "KF_ATTR_VALUE_FK2" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" HASH JOIN" (null) (null) 2
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 1
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1
" TABLE ACCESS" "BY INDEX ROWID" "KF_ATTR_VALUE" 2
" INDEX" "RANGE SCAN" "KF_ATTR_VALUE_FK1" 1

Жесть ))) Не уверен, но как вам такой вариант:

SELECT GROUP_CONCAT(DISTINCT item_value)
FROM KF_ATTR_VALUE 
WHERE attr_id IN (167,168,186,190,199,203,211,212,213,214,215,216,217,425)
GROUP BY obj_id;

Каждую строку результата разбивать с помощью exlode(), после чего в массиве будут лежать значения, соответствующие полям code, raion, director, nazvanie, hzchto3, nomer, hzchto2, vid, tip, finansirovanie, orgforma, podchinenie, okrug,hzchto1 (ключи от 0 до 13) для соответствующего obj_id.
Изврат, конечно, но все же.

В оракле нету group_concat
Есть замены на pl/sql, но я не так крут ;(

Я с Ораклом не сталкивался, но нашел, что там есть подобная GROUP_CONCAT функция WM_CONCAT, но это зависит от версии сервера.

Чем дальше в лес, тем круче:

SELECT    a.obj_id as id, a.item_value as director,
          b.uchreditel, c.sokr_name, d.pop_sov,
          e.upr_sov, f.obr_process,g.proezd,
          h.primechanie
from      KF_ATTR_VALUE a

left join (
    select z.ITEM_VALUE as uchreditel, z.obj_id as id
    from KF_ATTR_VALUE z
    where z.obj_id=obj_id and z.attr_id = 197) b
on b.id = a.obj_id
left join (
    select z.ITEM_VALUE as sokr_name, z.obj_id as id
    from KF_ATTR_VALUE z
    where z.obj_id=obj_id and z.attr_id = 185) c
on c.id = a.obj_id
left join (
    select z.ITEM_VALUE as pop_sov, z.obj_id as id
    from KF_ATTR_VALUE z
    where z.obj_id=obj_id and z.attr_id = 201) d
on d.id = a.obj_id
left join (
    select z.ITEM_VALUE as upr_sov, z.obj_id as id
    from KF_ATTR_VALUE z
    where z.obj_id=obj_id and z.attr_id = 202) e
on e.id = a.obj_id
left join (
    select z.ITEM_VALUE as obr_process, z.obj_id as id
    from KF_ATTR_VALUE z
    where z.obj_id=obj_id and z.attr_id = 199) f
on f.id = a.obj_id
left join (
    select z.ITEM_VALUE as proezd, z.obj_id as id
    from KF_ATTR_VALUE z
    where z.obj_id=obj_id and z.attr_id = 210) g
on g.id = a.obj_id
left join (
    select z.ITEM_VALUE as primechanie, z.obj_id as id
    from KF_ATTR_VALUE z
    where z.obj_id=obj_id and z.attr_id = 211) h
on h.id = a.obj_id

where
         a.attr_id = 186
         and a.obj_id = $this->OBJ_ID

Потом вот это вот должно будет выдерживать 20к юзверей в день(не одновременно.. надеюсб) по тз)

ПОдскажет кто - если взять запрос выше и переписать в виде:

SELECT distinct
(SELECT ITEM_VALUE FROM kf_attr_value z WHERE z.obj_id=449 and z.attr_id = 197) uchreditel,
(SELECT ITEM_VALUE FROM kf_attr_value z WHERE z.obj_id=449 and z.attr_id = 168) raion from kf_attr_value

..и тп.
Разница в скорости будет заметна? ЧТо быстрее подзапрос или джоин?

20к юзверей в день в режиме онлайн? Нагрузки предположительные известны?

В первом запросе и так все нормально. Если конечно 20к пользователей в режиме онлайн, на пентиуме 2, то все умрет в муках.

Помню трафико-счеталку делал, для тех, у кого денег на сиску нет. Ну она нормально держала по 1 ляму запросов в день на MySQL, на Core 2 Duo, нагрузка 45% проца

Смотрел я на этот запрос, смотрел... И всё не пойму никак. Зачем ДЖОЙНы-то? Узнал про них и решил использовать?
Я правильно понял запрос? Надо для всех obj_id из таблицы KF_ATTR_VALUE выбрать набор атрибутов с определенными номерами?

Можно так попробовать, с использованием специфики Оракла:

WITH
obj_ids AS (SELECT distinct obj_id FROM KF_ATTR_VALUE)

SELECT 

(SELECT item_value FROM KF_ATTR_VALUE WHERE obj_id=obj_list.obj_id AND attr_id=186) as director,
(SELECT item_value FROM KF_ATTR_VALUE WHERE obj_id=obj_list.obj_id AND attr_id=167) as code,
(SELECT item_value FROM KF_ATTR_VALUE WHERE obj_id=obj_list.obj_id AND attr_id=168) as raion,
(SELECT item_value FROM KF_ATTR_VALUE WHERE obj_id=obj_list.obj_id AND attr_id=190) as nazvanie

FROM obj_ids obj_list

order by obj_id

Я взял только первые 4 атрибута. Если все заработает, то тогда можно будет и другие атрибуты добавить :)
Если надо ограничить количество obj_id, то сделать это можно в самом первом запросе, которые идет сразу же после WITH. Пишешь там или условие WHERE или ограничение LIMIT.

При правильно сделанных индексах запрос должен просто "летать".