Пример применения Result Cache на стороне сервера

Тема неновая, относится к «бородатым» новым фичам Oracle 11g, однако, технология хорошо работающая и, что важно, позволяющая при определённых условиях, существенно экономить ресурсы сервера RDBMS

Далее привожу результаты успешного практического применения кэша результатов запроса для ресурсоёмкого частовыполняемого (т.е. выполняющегося чаще, чем обновляются данные) запроса, оптимизировать который другим методом было бы объективно непросто – встречаются запросы, которые просто обязаны много читать :)

ORA-00600 [ktbdchk1: bad dscn] – как найти повреждённые индексы

Пример практического проявления Bug 8895202 – ORA-1555 / ORA-600 [ktbdchk1: bad dscn] in Physical Standby after switch-over [ID 8895202.8] и действий, которые могут помочь в разрешении возникающих проблем.

Проблема (неоднократно наблюдалась после switch-over Physical Standby database to the Primary database role): в блоках индексов itl (Interested Transaction List) commit SCN оказывается больше чем block SCN

Практические симптомы:

• в 11.1.0.7 – ORA-00600 [ktbdchk1: bad dscn] при попытке изменения повреждённых индексных блоков (при операциях INSERT/UPDATE)
• в 11.2.0.2 – к предыдущей ошибке добавляется ORA-1555 при выполнении запросов, в плане которых используется индекс с «битыми» блоками

Сообщение об ошибке в alert.log в последнем случае выглядит весьма примечательно (Query Duration=0 sec или несколько секунд):

ORA-01555 caused by SQL statement below (SQL ID: 06mwy7ua78adg, Query Duration=0 sec, SCN: 0x0786.62a03def)

Баг зафиксирован и описан для версий 11.1.0.7 и далее, полное избавление намечено сделать в версии 12.1 :)

В описании можно найти порядок «лечения», остаётся найти и обезвредить (например, с помощью ALTER INDEX REBUILD [ONLINE PARALLEL N]) индексы, затронутые проблемой

Антибаг: Ora-00918 после обновления на 11g

При обновлении Oracle 10.2.0.4 -> 11gR2 неожиданно сталкиваемся с интересной ошибкой:

11.2.0.3@SQL> select group_id
2    from (select user_mode.group_id
3            from USERS_LIST U
4           inner join user_state
5              on user_state.user_id = U.user_id
6           inner join user_mode
7              on user_mode.mode_id = user_state.mode_id
8           where USER_STATUS_ID = 1
9             and USER_ID = 87960018
10           order by user_mode.group_id desc)
11   where rownum < 2
12  /
and USER_ID = 87960018
*
ERROR at line 9:
ORA-00918: column ambiguously defined

- ошибка вполне справедливая – столбец USER_ID действительно присутствует в двух таблицах, соединяемых в запросе с использованием ANSI варианта INNER JOIN.

Но в версии 10.2 тот же запрос безошибочно выполняется!

sqlplus preliminary connection – как соединиться с бд, если CONNECT AS SYSDBA не возможен

Начиная с Oracle 10.2 доступно и докуметировано sqlplus preliminary connection специально для случаев, когда прочие методы соединения недоступны – How To Connect Using A Sqlplus Preliminary Connection [ID 986640.1]:

$ sqlplus -prelim / as sysdba

или так:

$ sqlplus /nolog
set _prelim on
connect / as sysdba

При этом традиционно стартует клиентский процесс, но сессия не создаётся, доступ к структурам SGA – ограниченный, можно запустить oradebug hanganalyze | dump systemstate – что, собственно и требуется в таких случаях

Кроме того, опция preliminary connection – клиентская и, по словам Tanel Poder – How to log on even when SYSDBA can’t do so?, sqlplus версии 10.2+ может быть использован при соединении со «старыми» версиями – Oracle 9i, например, локально или удалённо:

$ sqlplus -prelim sys/manager@orcl112 as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Dec 5 01:26:28 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01012: not logged on

SQL> oradebug hanganalyze 3
Statement processed.

ORA-600 [qsmkii:inline obj not found] при выполнении запроса с множественными WITH в Oracle 10.2.0.4

Пользовательская ошибка при попытке создать материализованное представление:

ORA-03114: not connected to ORACLE

Судя по трейсам отключение пользовательского процесса:

ORA-07445: exception encountered: core dump [kghalp()+58] [SIGSEGV] [Address not mapped to object] [0x000000068] [] []

вызвано предыдущей ошибкой:

ORA-00600: internal error code, arguments: [qsmkii:inline obj not found], [], [], [], [], [], [], []

Два способа решения проблемы:

Элементы технологий Bind Aware Cursor Sharing / Cardinality Feedback

English version

Комментарии к демонстрации на осенней конференции RUOUG

Вступление

Поводом к изучению новых возможностей Oracle 11g стала простая практическая проблема – запрос с определённым набором связанных переменных выполнялся неприемлемо долго. При этом тот же запрос с текстовыми подстановками вместо связанных переменных выполнялся быстро с использованием другого, более подходящего плана выполнения.

Ситуация показалась мне интересной: для «текстового» запроса Oracle смог выбрать «быстрый» план выполнения, а для запроса с использованием связанных переменных , несмотря на известные новые технологии Adaptive/Extended Cursor Sharing, по какой-то причине продолжал использовать «старый» и очевидно неэффективный план выполнения:

Oracle 11g: иерархический профилировщик PL/SQL

Пункт диагностического меню Forms PL/SQL profiling в OEBS 12 (Oracle 11g) использует пакет DBMS_HPROF – относительно новый (для меня) иерархический профилировщик с отличным описанием в документации - Using the PL/SQL Hierarchical Profiler: примеры использования и анализа данных, формат трейс файла, описание используемых обозначений функций с разделением по пространствам имён (namespace: SQL, PL/SQL) и т.д.

Запускается просто:

Подсказка parallel

Иногда оптимизатор Oracle может трактовать подсказки в запросах шире, чем описывает документация

Например, хинт PARALLEL, формально требующий указания таблицы при использовании в качестве подсказки на уровне объекта бд в версиях Oracle 10-11.1:

/*+ PARALLEL( [@query_block] tablespec [ integer_degree_of_parallelism | DEFAULT ] ) */

, либо, начиная с Oracle 11.2, получивший дополнительно возможность действовать на уровне целого запроса без указания наименований таблиц:

/*+ PARALLEL[ ( DEFAULT | AUTO| MANUAL | integer_degree_of_parallelism ) ] */

- может прекрасно «работать» с указанием имени обзора (view) – при этом параллельность выполнения отлично отражается на времени выполнения :)

10.2.0.4@SQL> select--+ parallel(v 8)
 ...
 9 from SOME_USER_VIEW v,
 ...
 36 /

Elapsed: 00:00:17.83

А вот без указания объектов в подсказке (в этом случае) – запрос выполняется непараллельно и медленно – в точном соответствии с документацией ;)

10.2.0.4@SQL> select--+ parallel
 ...
 9 from SOME_USER_VIEW v,
 ... 36 /

Elapsed: 00:26:44.37

Oracle 11g: автоматическое управление памятью (ASMM) и serial direct read

Ничем (или недостаточно) ограниченный механизм Automatic Shared Memory Management (ASMM), динамически меняя размеры пулов SGA, может заставить Oracle  переключаться между режимами direct path read и кэшированного чтения (через buffer cache SGA), что, в свою очередь, может неожиданно, но вполне заметно влиять на время выполнения типовых запросов

И, хотя в документе High ‘direct path read’ waits in 11g [ID 793845.1]  описывается чудесное автоматическое переключение между этими режимами, в зависимости от динамически изменяющегося размера buffer cache:

When ASMM increases the buffer cache due to increased demand, 11g might not again do serial direct path read scans for these same large tables

- в действительности, это только пожелание или план работ на будущее – по крайней мере в версии 11.1.0.7.2 :)

Далее – небольшой практический пример

Медленный разбор SQL запроса (long parse time): вариант №2

Ещё один интересный случай, связанный с длительной фазой разбора (hard parsing) запроса в Oracle 10.2.0.3

В отличие от предыдущего случая (Медленный разбор SQL запроса (long parse time)), когда много времени тратилось на перебор многочисленных комбинаций соединений таблиц (пермутаций) уже на этапе оптимизации и зависело от лимита кол-ва таких перестановок (параметр _optimizer_max_permutations) или выбора используемого оптимизатора (CBO или RBO), на этот раз при первом выполнении (и, соответственно, выполнении hard parsing) запроса из 118 таблиц:

SQL> select operation, count(*) from v$sql_plan
 2 where (plan_hash_value, child_number) =
 3       (select plan_hash_value from v$sql where sql_text like 'SELECT /* MY_LONG_QUERY */%' and child_number = 0)
 4 and operation like 'TABLE%'
 5 group by operation
 6 /

OPERATION    COUNT(*)
------------ --------
TABLE ACCESS      118

кроме табличной комбинаторики, значительное время тратилось на этапе трансформации запроса (query transformation), предшествующей оптимизации:

-----------------------------------------------------------------------------
|                   Query Text & Parameters Parsing                         |
|                                  V                                        |
|                        Query Transformation                               |  <-- описываемый случай "тормозил" и в этом месте
|            (incl.Cost-Based Query Transformation - CBQT)                  |
|                                  V                                        |
|                         Query Optimization                                |
|                                  V                                        |
|       Cost-Based Optimization <     > Rule-Based Optimization             |
|(with join order permutations)        (with simplified table order choice?)|  <-- тут "притормаживал" случай, описанный ранее
-----------------------------------------------------------------------------