Oracle mechanics

14.05.2017

Конкатенация значений столбцов при превышении VARCHAR2 лимита 4000 bytes

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

, т.е. в случаях, когда использование VARCHAR2-функций типа LISTAGG заканчивается

ORA-01489: result of string concatenation is too long

LISTAGG_CLOB

Использовавшаяся поначалу User-Defined Aggregate Function типа LISTAGG_CLOB в плане скорости выполнения показывала самые грустные рез-ты: (more…)

Реклама

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…)

14.06.2014

Filter Push-Down и избыточные предикаты

Filed under: Oracle,Partitioning — Игорь Усольцев @ 12:27
Tags: ,

Александр Шакура показал интересное: в простом запросе добавление очевидно избыточного условия (redundant predicate) включает partition pruning, заметно улучшая тем самым стоисмость (Cost) и скорость выполнения запроса к обзору, построенному на помесячно партиционированной таблице

SELECT
 *
  FROM (SELECT /* эта часть запроса станет view */
         TRUNC(a.DT, 'mm')                    BEG_MONTH,
         ADD_MONTHS(TRUNC(a.DT, 'mm'), 1) - 1 END_MONTH,
         SUM(a.SUM_RUR)                       SUM_RUR
          FROM MVIEW__DAILY_SALES a
          WHERE a.dt BETWEEN TRUNC (a.DT, 'mm') AND ADD_MONTHS (TRUNC (a.DT, 'mm'), 1) - 1 -- redundant predicate?
         GROUP BY TRUNC(a.DT, 'mm'), ADD_MONTHS(TRUNC(a.DT, 'mm'), 1) - 1)
 WHERE beg_month = DATE '2013-04-01'
   AND end_month = DATE '2013-04-30'

Без избыточного предиката запрос демонстрирует типичный PARTITION RANGE ALL как ожидаемое поведение: (more…)

24.11.2012

Особенности расчёта cardinality в материализованных подзапросах при обновлении 11.1 -> 11.2

Filed under: CBO,heuristics,Oracle,parameters,SQL — Игорь Усольцев @ 11:10
Tags: , , , ,

После обновления 11.1.0.7 -> 11.2.0.3 БОЛЬШОЙ ЗАПРОС перешёл к бесконечным временам выполнения, причиной чему стала неточность определения cardinality некого подзапроса, вынесенного в секцию WITH основного запроса, и автоматически материализованного Oracle ввиду неоднократности использования

План проблемного подзапроса, выполняемого отдельно, без необходимости TEMP TABLE TRANSFORMATION показывает не вполне точные, но разумные оценки: (more…)

13.10.2012

Параметризованные обзоры и Cardinality Feedback в Oracle 11.2

Filed under: bind variables,Oracle,Plan Management,SQL — Игорь Усольцев @ 15:35
Tags: , ,

При использовании в запросе [текстовых] констант значения предикатов точно определены, и для построении максимально точного плана выполнения доступны обычная и расширенная (extended statistics) статистика распределения значений столбцов

При использовании связанных переменных для построения плана значения доступны через bind peeking и, в дальнейшем при необходимости, через технологию Bind-Aware Cursor Sharing

При использовании в запросах параметризованных обзоров (parameterized view) значения предикатов недоступны до момента выполнения, планы строятся по средним распределениям значений столбцов, и для дальнейшей модификации / исправления «средних» планов у Oracle остаётся только Cardinality Feedback

Далее — тестовый пример и практическая иллюстрация:

(more…)

26.09.2012

Оценка стоимости запроса с использованием рекусивных операций WITH (Recursive Subquery Factoring)

Filed under: CBO,heuristics,Oracle,Oracle new features — Игорь Усольцев @ 08:31
Tags: , , ,

Саян Малакшинов описал интересный и эффективный способ получения неповноряющихся значений — Удивительная оптимизация получения distinct values из индекса, а также TopN для каждого

Однако, как я понимаю, оптимизатор пока не умеет правильно оценивать стоимость операции Recursive Subquery Factoring и предложенный способ, несмотря на безусловную эффективность в отдельных случаях, не является универсальным и фактически требует ручного контроля стоимости запроса в зависимости от используемых данных

(more…)

29.08.2012

Операция filter(NULL IS NOT NULL) и запросы со связанными переменными

Filed under: bind variables,heuristics,Oracle,SQL — Игорь Усольцев @ 23:31
Tags: ,

При выполнении запроса с NULL-евыми значениями связанных переменных условия типа COLUMN1 = :VAR1 превращаются в COLUMN1 = NULL и по определению становятся невыполнимыми (в том смысле, что запрос с таким условием в WHERE не возвращает строк), но при построении плана / выполнении запроса оптимизатор не всегда использует эту возможность сэкономить ресурсы

Например, при выполнении практическоого запроса (судя по тексту, выполняющему проверку на совпадение введённого в поле формы текста с ID либо логином клиента) с пустым значением переменной:

11.2.0.3.@ SQL> var P25_CLIENTX char;
SQL> exec :P25_CLIENTX := null;

PL/SQL procedure successfully completed.

SQL> select count(*)
  2    from dual
  3   where exists (SELECT id
  4            FROM (SELECT c.id, c.name, a.login
  5                    FROM T_CLIENT c, T_ACCOUNT a
  6                   WHERE c.id = a.CLIENT_ID(+))
  7           WHERE TO_CHAR(ID) = :P25_CLIENTX
  8              OR LOGIN = :P25_CLIENTX)
  9  /

Elapsed: 00:00:03.22

------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |     1 |       |       |  7208   (1)| 00:01:41 |
|   1 |  SORT AGGREGATE          |             |     1 |       |       |            |          |
|*  2 |   FILTER                 |             |       |       |       |            |          |
|   3 |    FAST DUAL             |             |     1 |       |       |     2   (0)| 00:00:01 |
|*  4 |    FILTER                |             |       |       |       |            |          |
|*  5 |     HASH JOIN OUTER      |             |     2 |    50 |    32M|  7206   (1)| 00:01:41 |
|   6 |      INDEX FULL SCAN     | T_CLIENT_PK |  1891K|    10M|       |   221   (1)| 00:00:04 |
|*  7 |      MAT_VIEW ACCESS FULL| T_ACCOUNT   |  1531K|    27M|       |  3639   (1)| 00:00:51 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (SELECT 0 FROM "T_ACCOUNT" "A","T_CLIENT" "C" WHERE
              "C"."ID"="A"."CLIENT_ID"(+) AND (TO_CHAR("C"."ID")=:P25_CLIENTX OR
              "A"."LOGIN"=:P25_CLIENTX) AND "A"."CLIENT_ID"(+) IS NOT NULL))
   4 - filter(TO_CHAR("C"."ID")=:P25_CLIENTX OR "A"."LOGIN"=:P25_CLIENTX)
   5 - access("C"."ID"="A"."CLIENT_ID"(+))
   7 - filter("A"."CLIENT_ID"(+) IS NOT NULL)

Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
      13828  consistent gets -- куча GETS
      11617  physical reads  -- и даже дисковых чтений

— запрос выполняется так же, как и для не NULL-евых значений переменных, с тем же значительным количеством излишних в этом случае чтений блоков бд

Тот же запрос без использования связанных переменных отрабатывает намного эффективнее: (more…)

27.08.2012

Преобразование PL/SQL типов в CAST(COLLECT…) при миграция на многобайтовую кодировку (UTF)

Filed under: bugs,Oracle,PL/SQL,SQL — Игорь Усольцев @ 23:37
Tags: , ,

Проблема проявляется после миграции на UTF:

SQL> desc l

 Name   Type
 -----  ----------------
 C1     VARCHAR2(1 CHAR)

SQL> create or replace type l_tbl is table of varchar2(1 char);
 2 /

Type created.

SQL> select cast(collect(l.c1) as l_tbl) as collection from l;
select cast(collect(l.c1) as l_tbl) as collection from l
 *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type

До миграции (в бд с однобайтовой кодировкой CL8MSWIN1251) та же синтаксическая конструкция работает без ошибок: (more…)

30.06.2012

Подсказки OPTIMIZER_MODE в обзорах

Filed under: CBO,commonplace,hints,Oracle — Игорь Усольцев @ 15:54
Tags: , , , ,

Запрос, использующий (напрямую или через вложенные обзоры) обзор с подсказками OPTIMIZER_MODE типа: RULE, FIRST_ROWS, FIRST_ROWS(n), будет выполняться с указанной в обзоре OPTIMIZER_MODE — что может значительно и не всегда очевидно влиять на план выполнения

Для возвращения оптимизатора к нормальному поведению (состоянию по умолчанию) OPTIMIZER_MODE придётся менять на уровне системы, сессии или запроса (подсказкой «верхнего уровня» на уровне всего запроса), либо убирать подсказку из обзора-виновника, который ещё нужно найти

Например, при создании обзора с подсказкой /*+ RULE */: (more…)

04.06.2012

Как форсировать разбор SQL при каждом выполнении: новый параметр _disable_cursor_sharing в 11.2.0.3

Filed under: bind variables,CBO,Oracle,parameters,Plan Management,SQL — Игорь Усольцев @ 01:52
Tags: , , , , , ,

Иногда (нечасто) возникает необходимость при каждом выполнении запроса со связанными переменными выполнять разбор этого запроса оптимизатором (hard parse) с целью генерации отдельного плана для каждого набора связанных переменных. Такая необходимость может возникать, например, в случае нечастого выполнения тяжёлых отчётов, для которых Oracle вполне в состоянии подобрать быстрый план, если бы в запросе не использовались связанные переменные и традиционный механизм повторного использования курсоров cursor sharing

Вариантами решения проблемы могли бы быть (Dion Cho: Making SQL always hard parsed): (more…)

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

Блог на WordPress.com.