Простой запрос: 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 |
-----------------------------------------------------------------------------------------

Oracle < 11g: имена параметров PL/SQL (named notation) и ORA-00907

В стародавние времена, до версии Oracle 11g при попытке использования имён параметров (named notation) PL/SQL объектов из SQL-интерфейса, получали ORA-907 – «потому что нельзя использовать имена параметров (plsql’изм) в SQL«. Можно было использовать только стандартное позиционное указание параметров (standard positional notation):

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

SQL> SELECT * FROM TABLE(pkg1.f1(5));
COLUMN_VALUE
------------
 ...
SQL> SELECT * FROM TABLE(pkg1.f1(x => 5));
SELECT * FROM TABLE(pkg1.f1(x => 5))
 *
ERROR at line 1:
ORA-00907: missing right parenthesis

В Oracle 11g допустимо использовать, наряду с позиционным, указание параметров PL/SQL по имени (named notation) и смешанное перечисление параметров (mixed notation) – сначала параметры перечисляются по позиции, затем указываются по имени:

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> SELECT * FROM TABLE(pkg1.f1(5, 0));
 COLUMN_VALUE
 ------------
 ...

SQL> SELECT * FROM TABLE(pkg1.f1(x => 5, y=>0));
COLUMN_VALUE
------------
 ...

SQL> SELECT * FROM TABLE(pkg1.f1(5,y=>0));

COLUMN_VALUE
------------
 ...

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».

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

Анализ

Oracle Unbreakable MySQL / LAMP?

По сообщениям информированных источников, в течение Oracle OpenWorld (Oct. 11-15, San Francisco), активно обсуждались предположения о том, что Oracle готовится оснастить свой дистрибутив Linux (Oracle Enterprise Linux, инициатива Unbreakable Linux) приобретённой вместе с Sun СУБД MySQL — и получить таким образом достойное орудие против платформы Windows Server-SQL Server.

ORA-03114 при explain plan и компиляции с debug option в PL/SQL Developer

PL/SQL Developer 8.0.0 при построении плана выполнения некоторых запросов (с конструкцией WITH) иногда выдаёт ошибку ORA-03114: not connected to ORACLE.  Трейс файлы – чистые.

При попытке компиляции процедуры/пакета ту же ошибку получаем стабильно  + трейсы:
alert.log

Errors in file /u01/app/oracle/admin/.../udump/12345.trc:

ORA-07445: exception encountered: core dump [ptmak()+191] [SIGSEGV]
[Address not mapped to object] [0x000000010] [] []
Wed Nov 11 12:35:21 2009
Trace dumping is performing id=[cdmp_20091111123521]

udump trace

Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object)
, addr: 0x10, PC: [0x420111f, ptmak()+191]
ksedmp: internal or fatal error[SIGSEGV] [Address not mapped to object] [0x000000010] [] []
Current SQL statement for this session:
select dummy from dual where  ora_dict_obj_type = :"SYS_B_0"

Проблем не наблюдается при использовании SQL*Plus и PL/SQL Developer 7.1.4 при работе с тем же экземпляром Oracle 10.2.0.4 на платформе x86_64.