Особенности SQL Patch

Лишь частично документированный, но достаточно удобный для простого манипулирования планами выполнения SQL Patch, как и всякий инструмент имеет свои ограничения/особенности

Так, например, длина добавляемой к запросу подсказки имеет точное ограничение:

DBA_AUDIT_TRAIL, ORA-64203 и китайская грамота

При выполнении запросов к DBA_AUDIT_TRAIL (например, для архивации данных) можно получить легковоспроизводимую ORA-64203:

SQL> select max(lengthb(sql_text)), max(lengthb(sql_bind)) from DBA_AUDIT_TRAIL;
 
select max(lengthb(sql_text)), max(lengthb(sql_bind)) from DBA_AUDIT_TRAIL
 
ORA-64203: Destination buffer too small to hold CLOB data after character set conversion.

Найти проблемные строки несложно:

SPM подходы к изменению плана запроса с hardcoded подсказками

Вопрос: как максимально корректно изменить план выполнения уже хинтованного запроса? Корректно, в смысле не меняя текста запроса (само собой) и, желательно, не меняя параметров сессии, т.е. используя, например, безконтактный SQL Plan Management (SPM)?

Простейшая тестовая схема:

11.2.0.3@SCOTT SQL> create table emp123
  2  as
  3  select * from emp
  4  /

Table created.

SQL> exec dbms_stats.gather_table_stats('','EMP123')

PL/SQL procedure successfully completed.

SQL> create index emp123_sal_idx on emp123(sal)
  2  /

Index created.

Простой запрос с использованием FTS:

Гарантированный DOP и параметр PARALLEL_MIN_PERCENT

Встречаются запросы с завидным потреблением временного табличного пр-ва для HASH JOIN-ов разного рода и других сортировочно-группировочных операций, при недостатке показывающие:

ORA-01652: невозможно увеличить временный сегмент до 64 в разделе TEMP

Одним из методов сокращения требуемых объёмов temp является использование параллельного выполнения благодаря которому, попутно с улучшением общей скорости выполнения запроса, можно съэкономить временное пр-во, используя бОльший объём PGA большИм кол-вом px-процессов

В этом случае бывает необходимо обеспечить определённый Degree Of Parallelism (DOP) для выполнения конкретного запроса, т.е. ограничить DOP «снизу», для чего теоретически идеально подходит механизм parallel statement queuing, однако установка параметра _PARALLEL_STATEMENT_QUEUING = TRUE не всегда срабатывает, например, при выполнении DBMS_SCHEDULER-ного задания (job) запросы без ожидания resmgr:pq queued, начинают выполняться непараллельно, невзирая на отсутствие свободных PX

Как оказалось, в заданиях DBMS_SCHEDULER отлично срабатывает другой параметр PARALLEL_MIN_PERCENT, со своими особенностями, но срабатывает и в PL/SQL блоке DBMS_SCHEDULER, и на уровне обычной пользовательской сессии

Итак, на сервере со следующими ресурсами:

ROWID доступ к партицированной таблице

С Леонидом Борчуком разбирали страшное:

SQL ordered by Elapsed Time           DB/Inst: OEBS/OEBS1  Snaps: 77632-77633

        Elapsed                  Elapsed Time                                   
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    
---------------- -------------- ------------- ------ ------ ------ -------------
        49,260.9            139        354.39   72.4    9.0   93.5 2b01txt3hnjha -- запрос потребляет более 70% DB Time
Module: e:SQLAP:bes:oracle.apps.xla.accounting.postproce
UPDATE /*+ PARALLEL (AEL) */ XLA_AE_LINES AEL SET AEL.ANALYTICAL_BALANCE_FLAG = 
:B8 ,AEL.LAST_UPDATE_DATE = :B5 ,AEL.LAST_UPDATED_BY = :B7 ,AEL.LAST_UPDATE_LOGI
N = :B6 ,AEL.PROGRAM_UPDATE_DATE = :B5 ,AEL.PROGRAM_APPLICATION_ID = :B4 ,AEL.PR
OGRAM_ID = :B3 ,AEL.REQUEST_ID = :B2 WHERE (AEL.ROWID) IN (SELECT ...

         5,312.4          1,801          2.95    7.8   99.2     .0 7r2xtc0ru1h0s -- , на порядок опережая конкурентов
...

Один из стандартных запросов OEBS, судя по истории обычно выполнялся небыстро, потребляя значительные I/O ресурсы на direct path read (судя по соотношению DISK_READS_PER_EXEC и READS_PER_EXEC) большим количеством параллельных процессов (PX_PER_EXEC):

ORA-8103 при использовании BCT на standby db

После восстановления очередной тестовой бд с использованием инкрементальных бэкапов, сделанных с использованием Block Change Tracking (BCT) на standby версии 11.2.0.3 получили при попытке последовательно прочитать определённые блоки таблицы:

SQL> select/*+ full(t)*/ count(*) from U2.CALL_STAT t;
 
select/*+ full(t)*/ count(*) from U2.CALL_STAT t
 
ORA-08103: object no longer exists

SQL> analyze table U2.CALL_STAT validate structure;
 
analyze table U2.CALL_STAT validate structure
 
ORA-08103: object no longer exists

, при этом в трейсах можно видеть фактический источник ошибки:

Медленный накат логов / Media Recovery Log и скрипт для standby ASH

Сразу после разрешения предыдущих проблем ASM диагностики успешно возобновлённый накат логов / Standby Log Apply может выполняться чрезвычайно медленно, по крайней мере, первое время

Итак, физический стендбай:

SQL> @inst
 
INST_ID INSTANCE_NAME HOST_NAME    VERSION    PLATFORM_NAME        DATABASE_STATUS DATABASE_ROLE    OPEN_MODE
------- ------------- ------------ ---------- -------------------- --------------- ---------------- ---------
1*      orcl1         stby1.com.ru 11.2.0.2.0 Linux x86 64-bit     ACTIVE          PHYSICAL STANDBY MOUNTED

Для анализа причин медлительности работы standby удобно использовать несколько упрощённый скрипт ash_wait_tree4stby.sql для ожиданий в ASH, которая, к счастью, продолжает успешно заполняться и в статусе MOUNTED

ASM bug / таймауты типа Waited 15 secs for write IO to PST

Недавно с Наташей Егоровой разбирали проблемы standby бд, которые отражались в alert.log следующего типа жалобами процессов ARC и RFS:

Errors in file /opt/oracle/admin/diag/rdbms/orcl/orcl1/trace/orcl1_arc2_9237.trc:
ORA-00235: control file read without a lock inconsistent due to concurrent update
...
RFS[147]: Controlfile enqueue unavailable
Errors in file /opt/oracle/admin/diag/rdbms/orcl/orcl1/trace/orcl1_rfs_32434.trc:
ORA-16146: standby control file enqueue unavailable

И если первая ошибка може быть игнорирована согласно ORA-235 Reported in standby alert log (Doc ID 1104343.1), то ORA-16146 в версии 11.2 из трейса RFS:

Официальный документ про Smart Flash Cache

Спустя 5 лет после выхода версии 11.2 появился практически полезный документ от Oracle Optimizing Oracle Database Performance on Oracle Linux with Flash, где кроме традиционного маркетингового слоя, можно найти толковое техническое руководство по конфигурации Smart Flash Cache на Linux, включая установку специфичных для Flash/SSD параметров, пошаговые процедуры создания ASM и конфигурации Smart Flash Cache в бд, плюс подробные рез-ты сравнительных тестов

При планировании использования Smart Flash Cache стоит иметь в виду, что фича эта может эффективно использоваться не только с брендовыми Oracle Sun Flash Accelerator PCIe Card, и даже не только со всё ещё достаточно дорогими Flash PCIe Card других производителей, но и с вполне доступными SSD дисками

История одного запроса: SPM, трансформации и подсказки

Типичный случай — план запроса «неожиданно» измененился, и не в лучшую сторону:

11.2.0.4.@ SQL> @shared_cu 8dvbszd8kj04m

INST EXECS LAST_LOAD_TIME LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON1
---- ----- -------------- ---------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- ------------------
   2     9 07.10 14:50    07.10 17:57        1076192531       465674203            132     1 Y          N          Y          N                  Y                N                NLS Settings(0)  |
   2     5 07.10 16:04    07.10 17:04            263416      4268563287            246     3 Y          N          Y          N                  N                N                NLS Settings(0)  |

, при этом V$SQL_SHARED_CURSOR.LOAD_OPTIMIZER_STATS=Y (обозначенное как OPTIMIZER_STATS) означает, что неудачный выбор формально более выгодного плана был динамически предопределён технологией cardinality feedback (CF):

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('8dvbszd8kj04m', 1,format => '+note'));
...
Note
-----
   - cardinality feedback used for this statement

Часть 1. SQL Plan Management (SPM)