Был у меня запрос:

SELECT A."USER_ID", A."FIO", A."NAME_TERR", A."TTYPE", A."TOWN",
A."SHORTNAME", A."STREET", A."STYPE", A."HOUSE", A."CORPUS", A."FLAT"
FROM
"KAMV" A WHERE ( ISCORP = 'N' AND IN09 = 'Y' AND USER_ID IN (SELECT USER_ID
FROM T_USERS U WHERE U.ISCORP = 'N' AND U.HOUSE_ID IN (SELECT H.HOUSE_ID
FROM V_HOUSES H WHERE H.STREET_ID LIKE '42%' AND H.STREET LIKE 'ХИМИКОВ%'
AND H.TOWN LIKE '%' AND H.HOUSE = '15') UNION SELECT USER_ID FROM
T_SERVICES S WHERE S.HOUSE_ID IN (SELECT H.HOUSE_ID FROM V_HOUSES H WHERE
H.STREET_ID LIKE '42%' AND H.STREET LIKE 'ХИМИКОВ%' AND H.TOWN LIKE '%' AND
H.HOUSE = '15')) AND FLAT = '164' ) ORDER BY A."FIO", A."USER_ID"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.09 0.09 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 6 0.05 0.06 0 9378 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.14 0.15 0 9378 0 6

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 43

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
282 VIEW VW_NSO_1
282 SORT UNIQUE
562 UNION-ALL
280 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
6 TABLE ACCESS BY INDEX ROWID T_STREETS_REF
108 INDEX RANGE SCAN (object id 40942)
6 TABLE ACCESS BY INDEX ROWID O_HOUSES
6 AND-EQUAL
33 INDEX RANGE SCAN (object id 31098)
29 INDEX RANGE SCAN (object id 31099)
2 INDEX UNIQUE SCAN (object id 34845)
2 TABLE ACCESS BY INDEX ROWID T_TOWN
2 INDEX UNIQUE SCAN (object id 35019)
2 INDEX UNIQUE SCAN (object id 35021)
280 TABLE ACCESS BY INDEX ROWID T_USERS
284 INDEX RANGE SCAN (object id 35044)
282 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
6 TABLE ACCESS BY INDEX ROWID T_STREETS_REF
108 INDEX RANGE SCAN (object id 40942)
6 TABLE ACCESS BY INDEX ROWID O_HOUSES
6 AND-EQUAL
33 INDEX RANGE SCAN (object id 31098)
29 INDEX RANGE SCAN (object id 31099)
2 INDEX UNIQUE SCAN (object id 34845)
2 TABLE ACCESS BY INDEX ROWID T_TOWN
2 INDEX UNIQUE SCAN (object id 35019)
2 INDEX UNIQUE SCAN (object id 35021)
282 TABLE ACCESS BY INDEX ROWID T_SERVICES
283 INDEX RANGE SCAN (object id 34775)
282 TABLE ACCESS BY INDEX ROWID T_USERS
562 INDEX UNIQUE SCAN (object id 35051)
2 TABLE ACCESS BY INDEX ROWID O_HOUSES
2 INDEX UNIQUE SCAN (object id 31103)
2 TABLE ACCESS BY INDEX ROWID T_STREETS_REF
2 INDEX UNIQUE SCAN (object id 34843)
2 TABLE ACCESS BY INDEX ROWID T_STREET_TYPE
2 INDEX UNIQUE SCAN (object id 34845)
2 TABLE ACCESS BY INDEX ROWID T_TOWN
2 INDEX UNIQUE SCAN (object id 35019)
2 TABLE ACCESS BY INDEX ROWID T_TOWN_TYPE
2 INDEX UNIQUE SCAN (object id 35021)
2 TABLE ACCESS BY INDEX ROWID T_TERRITORY_CONSTITUTE
2 INDEX UNIQUE SCAN (object id 34956)
1 TABLE ACCESS BY INDEX ROWID T_TERRITORY_TYPE
2 INDEX UNIQUE SCAN (object id 34958)


а я его переписал с использованием связанных переменных:

SELECT USER_ID
FROM
"KAMV" A WHERE (ISCORP = 'N' AND IN09 = 'Y' AND USER_ID IN (SELECT
USER_ID FROM T_USERS U WHERE U.ISCORP = 'N' AND U.HOUSE_ID IN (SELECT
H.HOUSE_ID FROM V_HOUSES H WHERE H.STREET_ID LIKE '42%' AND H.STREET
LIKE :b1 AND H.TOWN LIKE :b2 AND H.HOUSE = :b3 ) UNION SELECT USER_ID
FROM T_SERVICES S WHERE S.HOUSE_ID IN (SELECT H.HOUSE_ID FROM V_HOUSES H
WHERE H.STREET_ID LIKE '42%' AND H.STREET LIKE :b1 AND H.TOWN LIKE
:b2 AND H.HOUSE = :b3 )) AND FLAT = :b7 ) ORDER BY A."FIO",A."USER_ID"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 12 0.05 0.05 0 0 0 0
Fetch 6 1.39 1.37 2 22812 216 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 1.44 1.42 2 22812 216 6

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 43 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
282 VIEW VW_NSO_1
282 SORT UNIQUE
562 UNION-ALL
280 NESTED LOOPS
281 HASH JOIN
280 NESTED LOOPS
281 HASH JOIN
2772 HASH JOIN
268 TABLE ACCESS FULL O_HOUSES
144033 TABLE ACCESS FULL T_USERS
5 TABLE ACCESS BY INDEX ROWID T_STREETS_REF
108 INDEX RANGE SCAN (object id 40942)
280 INDEX UNIQUE SCAN (object id 34845)
33081 TABLE ACCESS FULL T_TOWN
280 INDEX UNIQUE SCAN (object id 35021)
282 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 HASH JOIN
268 TABLE ACCESS FULL O_HOUSES
5 TABLE ACCESS BY INDEX ROWID T_STREETS_REF
108 INDEX RANGE SCAN (object id 40942)
2 INDEX UNIQUE SCAN (object id 34845)
2 TABLE ACCESS BY INDEX ROWID T_TOWN
2 INDEX UNIQUE SCAN (object id 35019)
2 INDEX UNIQUE SCAN (object id 35021)
282 TABLE ACCESS BY INDEX ROWID T_SERVICES
283 INDEX RANGE SCAN (object id 34775)
282 TABLE ACCESS BY INDEX ROWID T_USERS
562 INDEX UNIQUE SCAN (object id 35051)
2 TABLE ACCESS BY INDEX ROWID O_HOUSES
2 INDEX UNIQUE SCAN (object id 31103)
2 TABLE ACCESS BY INDEX ROWID T_STREETS_REF
2 INDEX UNIQUE SCAN (object id 34843)
2 INDEX UNIQUE SCAN (object id 34845)
2 TABLE ACCESS BY INDEX ROWID T_TOWN
2 INDEX UNIQUE SCAN (object id 35019)
2 INDEX UNIQUE SCAN (object id 35021)
2 TABLE ACCESS BY INDEX ROWID T_TERRITORY_CONSTITUTE
2 INDEX UNIQUE SCAN (object id 34956)
1 INDEX UNIQUE SCAN (object id 34958)

и получил полное сканирование некоторых таблиц, вместо использования индекса и соответсвенно снижение скорости на порядок. Похоже оптимизатор не может оценить избирательность запроса по индексу по этим таблицам и принимает решение на full scan table.

Возникает извечный вопрос, что делать ???

Причем kamv & v_houses - это view-ы.

дык анализ здесь причем, ведь сказано - без связанных переменных все работает быстро и значит статистика актуальна. Да и перестраивал я ее.
Вот если в некторых моментах like поменять на = сразу индекс цепляется, а сие означает, что избирательность запроса повысилась и оптимизатор принял решение использовать индекс?????

Вот непонятно, Кайт пишет, что использовать связанные переменные обязательно. И приводит примеры. Я проверил - все правильно. НО !!!
У него в примерах запросы простые. А в документации по Оракл написано - использование связанных переменых может помешать оптимизатору использовать индекс, из-за невозможности оценить избирательность, что у меня и происходит. Вывод !!???? Используй связанные переменные - улучшишь масштабируемость, но снизишь скорость выполнения. Кайт этот вопрос обходит стороной, и это неспроста, хотя говорит о связанных переменных много, это у него конек, похоже.

Ага вот пошел конкретный треп,

Вот цитата из Кайта: ... чем больше сеансов разбирает запросы, тем больше приходится ждать освобождения защелок в библиотечном кэше, и работа системы постепенно останавливается ...

Во, это не я написал, да я читаю оччень внимательно, и прочитал, что применение связанных переменных может ухудшить ситуацию, но этому вопросу уделено две строки (буквально !!!), а вот преимуществам использования св. переменных - несколько страниц в 2-х (!!!) главах.

У меня сорок пользователей за сутки выдают 20 000 в среднем запросов, и, по Кайту, если я не буду использовать св. переменные (а я их пока не использую), то моя система тихо умрет из-за блокирования разделяемого пула. А если я их использую (это уже я), то система тихо умрет из-за многочисленных full table scan ?????, а операторы - от ожидания ответов.

Где же золотая середина, и почему Кайт о ней ничего не пишет, у него однозначно - используйте и все !!!

Ну по твоей классификации только начинаю работать с Оракл, но зерна от плевел уже отличаю. И вовсе не собираюсь верить всему, что говорят. Потому в форум полез. И все, что написали, верно, со всем согласен.

Но !!! Категорически же утверждает - работать не будет, на протяжении всей книги. Или он пытается воспитать из нас закаленных профи, как в последнем варианте, типа я говорю, а вы не верьте.

Прямо антисистема какя-то по Гумилеву.

Да, а по сути то это все-таки OLTP, с некоторыми элементами (АРМами) DSS.

Да, прочитал я статью, которую указал (спасибо ему) Denis Popov про изменения в Oracle 9i.

Там как раз перед первым выполнением значения переменных подставляются и на основе этих значений строится план выполнения, который затем используется для любых значений связанных переменных.