Oracle mechanics

14.12.2014

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

Filed under: Oracle,SQL Tuning — Игорь Усольцев @ 20:18
Tags: ,

Вопрос: как максимально корректно изменить план выполнения уже хинтованного запроса? Корректно, в смысле не меняя текста запроса (само собой) и, желательно, не меняя параметров сессии, т.е. используя, например, безконтактный 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: (more…)

12.12.2014

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

Filed under: Oracle,SQL Tuning — Игорь Усольцев @ 23:16
Tags: , ,

Встречаются запросы с завидным потреблением временного табличного пр-ва для 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, и на уровне обычной пользовательской сессии

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

07.12.2014

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

Filed under: Oracle,Plan Management,SQL Tuning — Игорь Усольцев @ 10:53
Tags: ,

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

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): (more…)

04.12.2014

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

Filed under: Active Data Guard,bugs,Oracle — Игорь Усольцев @ 22:59
Tags: ,

После восстановления очередной тестовой бд с использованием инкрементальных бэкапов, сделанных с использованием 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

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

25.11.2014

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

Filed under: Oracle,Standby — Игорь Усольцев @ 00:04
Tags: ,

Сразу после разрешения предыдущих проблем 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 (more…)

24.11.2014

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

Filed under: Диагностика системы (instance),Oracle,Standby — Игорь Усольцев @ 23:08
Tags: ,

Недавно с Наташей Егоровой разбирали проблемы 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: (more…)

16.10.2014

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

Filed under: Hardware,Oracle — Игорь Усольцев @ 00:48
Tags: ,

Спустя 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 дисками

12.10.2014

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

Filed under: Oracle,Plan Management,SQL Tuning — Игорь Усольцев @ 23:29
Tags: ,

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

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)

(more…)

10.10.2014

Планы выполнения запросов на RO Standby

Filed under: Active Session History,Oracle — Игорь Усольцев @ 02:42
Tags: ,

Хорошо известно, что перераспределение запросной активности на Standby db, открытую в режиме READ ONLY — чистое благо, по крайней мере покуда «всё идёт по плану»

Диагностика возникающих при этом проблем типа периодического замедления выполнения конкретных запросов, на ro-standby имеет особенности и может быть любопытна

Например, была получена достоверная информация, что запрос, посылаемый через dblink на standby:

SQL> @inst
 
INST_ID INSTANCE_NAME HOST_NAME VERSION    PLATFORM_NAME    DATABASE_STATUS DATABASE_ROLE    STATUS   OPEN_MODE
------- ------------- --------- ---------- ---------------- --------------- ---------------- -------- ---------
1*      myinst        myhost    11.2.0.3.0 Linux x86 64-bit ACTIVE          PHYSICAL STANDBY OPEN     READ ONLY

периодически вместо секунд выполняется десятки минут, что очевидно заслуживает пристального внимания (more…)

Простая проблема при создании AWR снапшотов в Oracle 11g

Filed under: bugs,Диагностика системы (instance),Oracle — Игорь Усольцев @ 00:10
Tags: ,

Проблема действительно несложная (более интересный вариант отписывал ранее) и выражается, кроме собственно прекращения генерации снапшотов, сообщениями в alert.log вида:

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1245278029_19314 by 128 in tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1245278029_19314 by 8192 in tablespace SYSAUX

, ошибками в трейсах процесса Manageability Monitor (mmon) и детей ея m00x, выраженным ростом партиций сегмента WRH$_LATCH_CHILDREN:

11.2.0.4@ SQL> select * from dba_segments where tablespace_name = 'SYSAUX' and bytes > 1e9 order by bytes desc;
 
SEGMENT_NAME                    PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SUBTYPE TABLESPACE_NAME      BYTES     BLOCKS
------------------------------- ------------------------------ ------------------ --------------- --------------- ---------- ----------
WRH$_LATCH_CHILDREN             WRH$_LATCH__1245278029_19314   TABLE PARTITION    ASSM            SYSAUX          3414687744    4168320
WRH$_LATCH_CHILDREN_PK          WRH$_LATCH__1245278029_19314   INDEX PARTITION    ASSM            SYSAUX          2771281510    3382912
WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_1245278029_19314   TABLE PARTITION    ASSM            SYSAUX          1407188992     171776

, отражена в документе поддержки SYSAUX Tablespace can Become Full Due to WRH$_LATCH_CHILDREN growth (Doc ID 874518.1) и чаще всего возникает при установке statistics_level=ALL на уровне системы

Некоторой определённости заслуживают быстрые методы очистки SYSAUX и перезапуска mmon_slave процессов для возобновления автогенерации снапшотов (more…)

Следующая страница →

Тема: Rubric. Блог на WordPress.com.

Отслеживать

Get every new post delivered to your Inbox.

Join 142 other followers