Простой запрос: FIRST_ROWS и CURSOR_SHARING_EXACT

Простой запрос по индексу выполняется 465 секунд!

SELECT
A.ID, A.LOGIN, A.NAME, A.LASTNAME, A.EMAIL
FROM ACCOUNT A
WHERE (A.LOGIN LIKE '%%')
AND (A.NAME LIKE '%Кудряшов%' OR A.LASTNAME LIKE '%Кудряшов%')
AND A.COMPANY_ID = 1
ORDER BY A.LOGIN

Это странно, поскольку одно из условий A.COMPANY_ID = 1 обладает высокой избирательностью

SQL> SELECT 'ALL' as "Condition", count(*) FROM ACCOUNT A
  2  union
  3  SELECT 'COMPANY_ID = 1' as "Condition", count(*) FROM ACCOUNT A WHERE A.COMPANY_ID = 1;

Condition                          COUNT(*)
-------------------------------- ----------
ALL                                 2293282
COMPANY_ID = 1                          361

, столбец ACCOUNT.COMPANY_ID проиндексирован и команда EXPLAIN PLAN показывает, что именно этот индекс используется!

SQL> explain plan for
SELECT
A.ID, A.LOGIN, A.NAME, A.LASTNAME, A.EMAIL
FROM ACCOUNT A
WHERE (A.LOGIN LIKE '%%')
AND (A.NAME LIKE '%Кудряшов%' OR A.LASTNAME LIKE '%Кудряшов%')
AND A.COMPANY_ID = 1
ORDER BY A.LOGIN
/
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |     1 |    66 |     5 |
|   1 |  SORT ORDER BY               |                          |     1 |    66 |     5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACCOUNT                  |     1 |    66 |     4 |
|   3 |    INDEX RANGE SCAN          | IX_ACCOUNT_COMPANY_ID    |     1 |       |     3 |
-----------------------------------------------------------------------------------------

latch: cache buffers chains и настройка запросов с использованием cardinality feedback

Симптомы

Заметные проблемы общей производительности на работающей OLTP системе: высокая процессорная нагрузка при незначительном вводе-выводе, запрос из v$session показывает несколько активных сессий, выполняющих в течение сотен секунд один и тот же запрос, ожидающих latch: cache buffers chains или просто активно использующих процессор (On CPU / runqueue)

Отчёт ADDM в первых строках указывает на тот же запрос, но дополняет картину кластерными ожиданиями, также относящимися к буферному кэшу:

RATIONALE: Ожидание «latch: cache buffers chains» в классе «Concurrency» занимает 8% времени бд при выполнении запроса SQL_ID «6drhaqddkx6xd».
RATIONALE: Ожидание «gc current block 2-way» в классе «Cluster» занимает 6% времени бд при выполнении запроса SQL_ID «6drhaqddkx6xd».
RATIONALE: Ожидание «gc buffer busy» в классе «Cluster» занимает 2%времени бд при выполнении запроса SQL_ID «6drhaqddkx6xd».

Причину появления нагрузки (ошибка в частоте запуска модуля) удалось локализовать и устранить быстро, долгоиграющие сессии принудительно завершены, теперь интересно разобраться с событиями ожидания и проблемным запросом.

Анализ

Использование DISTINCT vs EXISTS vs GROUP BY в подзапросах

Интересное наблюдение за поведением Oracle CBO на примере трёх идентичных (по результату) запросов:

1
select b.comm from bonus b where b.ename in
(select distinct e.ename from emp e where e.mgr >0);
2

select b.comm from bonus b where exists
(select 1 from emp e where e.ename = b.ename and e.mgr >0);
3

select b.comm from bonus b where b.ename in
(select e.ename from emp e where e.mgr >0 group by e.ename);

Неправильный результат при использовании условия IN с «аналитическим подзапросом»

Замечен ошибочный результат в запросах типа

select * from t1, t2
 where t1.col1 = t2.col2
 and t1.col1 = n
 and t2.col3
IN
   (select decode(row_number()
    over(order by decode(col3,'YY',0,1),col4 desc),1,col3,'ZZ')
    from t2 where col2 = t1.col1)

Эффект замечен на версиях Oracle 10.2.0.4 и 11.1.0.7, подтверждён специалистами Oracle support на версиях с 10.2.0.3 по 11.1.0.7 для функций ROW_NUMBER() и RANK(), заведён баг 7830462.

Predicate pushdown for outer-joined views with GROUP BY

Что стоит учитывать при использовании в запросах типа

select xo.object_id, xo_view.result_count
from xxx_objects xo
left join (select object_id, count(*) as result_count
           from xxx_objects group by object_id) xo_view
on xo_view.object_id = xo.object_id
where xo.object_name = 'XXX_OBJECTS'

использующих outer join с обзорами (view), содержащими group by.
Или  об ограничениях механизма join predicate push-down (JPPD) в версиях Oracle, предшествующих Oracle11g.