Oracle mechanics

04.06.2012

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

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

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

1) Использование текстовых констант вместо связанных переменных — идеальный вариант (при этом Oracle в нашем случае показал, что прекрасно умеет строить быстрые планы для конкретного набора текстовых констант), но не все инструменты позволяют это сделать — в нашем случае этот вариант не подходил

2) Стимулирование использования Adaptive Cursor Sharing подсказкой /*+ BIND_AWARE */ — не всегда подходит ввиду невысокой чуствительности механизма — т.е. в описываемом случае, например, добиться генерации «правильных» планов выполнения для всех наборов связанных переменных не удалось

3) Инвалидация курсора методом манипуляции со статистикой подлежащих объектов бд:

SQL> exec dbms_stats.set_table_stats('schema_name', 'table_name', num_rows=>null, no_invalidate=>false);

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

4) использование row-level security (Oracle VPD) для добавления неповторяющихся текстовых предикатов к запросам,  обращающимся к определённой таблице бд — Randolf Geist: How to force a hard parse — отличное решение, однако этот механизм будет применяться ко всем запросам, использующим таблицы, обзоры или синонимы, к которым применена Fine-Grained Access Control policy (FGAC) — но, в любом случае, это вариант который стоит рассмотреть. Недостатком этого подхода является то, что FGAC политика будет применяться ко всем запросам (использующим объект бд): и со связанными переменными, и с текстовыми константами

5) Использование, начиная с 11.2.0.3, параметра _disable_cursor_sharing:

SQL> @param_ _disable_cursor_sharing

NAME                     VALUE  IS_DEF   DSC
------------------------ ------ -------- ----------------------
_disable_cursor_sharing  FALSE  TRUE     disable cursor sharing

- изменением этого параметра на уровне сессии мы успешно воспользовались

Установка параметра в TRUE не влияет на запросы без связанных переменных:

11.2.0.3.ORCL112@SCOTT SQL> alter session set "_disable_cursor_sharing" = true;

Session altered.

SQL> select count(*) from emp;

SQL> @sql -- запрос PREV_SQL_ID, PREV_CHILD_NUMBER и SQL_TEXT текущей сессии из V$SESSION, V$SQL

SQL_ID             CHILD SQL_TEXT
------------- ---------- ------------------------------------------------------------
g59vz2u4cu404          1 select count(*) from emp

11.2.0.3.ORCL112@SCOTT SQL> select count(*) from emp;

11.2.0.3.ORCL112@SCOTT SQL> @sql

SQL_ID             CHILD SQL_TEXT
------------- ---------- ------------------------------------------------------------
g59vz2u4cu404          1 select count(*) from emp

- при повторных выполнениях используется тот же курсор — CHILD_NUMBER не меняется, это плюс

При использовании связанных переменных

SQL> var deptno number;
SQL> exec :deptno := 20;

PL/SQL procedure successfully completed.

SQL> select * from emp where deptno = :deptno;

SQL> @sql

SQL_ID             CHILD SQL_TEXT
------------- ---------- ------------------------------------------------------------
0zb97qw5753u6          0 select * from emp where deptno = :deptno

SQL> select * from emp where deptno = :deptno;

SQL> @sql

SQL_ID             CHILD SQL_TEXT
------------- ---------- ------------------------------------------------------------
0zb97qw5753u6          1 select * from emp where deptno = :deptno

SQL> select * from emp where deptno = :deptno;

SQL> @sql

SQL_ID             CHILD SQL_TEXT
------------- ---------- ------------------------------------------------------------
0zb97qw5753u6          2 select * from emp where deptno = :deptno

SQL> select count(distinct child_number) from v$sql where sql_id = '0zb97qw5753u6';

COUNT(DISTINCTCHILD_NUMBER)
---------------------------
3

- дочерний курсор генерируется (с выполнением необходимого hard parse) при каждом выполнении запроса независимо от значений связанных переменных — т.е. cursor sharing не работает в принципе

Использование такого решения может быть неидеальным для частовыполняемых запросов, но в нашем случае редковыполняемого ресурсоёмкого отчёта overhead’ом на дополнительные разборы можно смело пренебречь, т.к. за счёт повторных разборов удаётся получать удовлетворительные по скорости выполнения планы для каждого набора значений переменных

Однако у параметра обнаружился достаточно курьёзный недостаток — генерация отдельных дочерних курсоров для каждого PX-slave процесса при каждом параллельном выполнении:

SQL> select/*+ parallel*/ * from emp where deptno = :deptno;

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     5 |   190 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     5 |   190 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |     5 |   190 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| EMP      |     5 |   190 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

SQL> @sql

SQL_ID             CHILD SQL_TEXT
------------- ---------- ------------------------------------------------------------
3t4ks2396rm6s          0 select/*+ parallel*/ * from emp where deptno = :deptno      -- child_number координатора запроса (QC)

SQL> select count(distinct child_number) from v$sql where sql_id = '3t4ks2396rm6s';

COUNT(DISTINCTCHILD_NUMBER)
---------------------------
9                          -- по кол-ву сгенерированных курсоров можно определить DOP=8

SQL> select/*+ parallel*/ * from emp where deptno = :deptno;

SQL> @sql

SQL_ID             CHILD SQL_TEXT
------------- ---------- ------------------------------------------------------------
3t4ks2396rm6s          9 select/*+ parallel*/ * from emp where deptno = :deptno

SQL> select count(distinct child_number) from v$sql where sql_id = '3t4ks2396rm6s';

COUNT(DISTINCTCHILD_NUMBER)
---------------------------
18

- что опять же может быть в некоторых случаях источником проблем с размером и наполнением Shared Pool

About these ads

Комментарии (2) »

  1. Хорошая новость! Давно надо было сделать. Интересно, может быть хинт появился соответствующий…

    комментарий от Деев И. — 25.06.2012 @ 15:25 | Ответить

    • Самому интересно)
      К сожалению, указанный параметр в виде /*+ opt_param(‘_disable_cursor_sharing’ ‘true’)*/ — не работает

      Как вариант, стимулирующий генерацию дочерних курсоров можно задействовать механизм Extended Cursor Sharing с помощью подсказок /*+ BIND_AWARE*/ или /*+ opt_param(‘_optimizer_adaptive_cursor_sharing’ ‘false’)*/
      Однако понятно что:
      * новые курсоры будут создаваться не при каждом выполнении и даже не для нового каждого набора bind’ов
      * и только при наличии гистограмм на фильтрующих столбцах:

      SQL> var deptno number
      SQL> exec :deptno := 10;
      SQL> select /*+ BIND_AWARE*/ * from emp where deptno = :deptno;
      
      SQL> @sql
      
      SQL_ID             CHILD SQL_TEXT
      ------------- ---------- ------------------------------------------------------------
      265rtc8a1swpq          0 select /*+ BIND_AWARE*/ * from emp where deptno = :deptno
      
      SQL> exec :deptno := 20;
      SQL> select /*+ BIND_AWARE*/ * from emp where deptno = :deptno;
      
      SQL> @sql
      
      SQL_ID             CHILD SQL_TEXT
      ------------- ---------- ------------------------------------------------------------
      265rtc8a1swpq          1 select /*+ BIND_AWARE*/ * from emp where deptno = :deptno
      
      SQL> exec :deptno := 30;
      SQL> select /*+ BIND_AWARE*/ * from emp where deptno = :deptno;
      
      SQL> @sql
      
      SQL_ID             CHILD SQL_TEXT
      ------------- ---------- ------------------------------------------------------------
      265rtc8a1swpq          1 select /*+ BIND_AWARE*/ * from emp where deptno = :deptno
      
      SQL> @v$sql 265rtc8a1swpq
      
      CHILD_NUMBER OPTIMIZER_COST PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE EXECUTIONS
      ------------ -------------- --------------- ----------------- ----------------- ------------ ----------
                 0              3      3956160932 Y                 Y                 N                     1
                 1              3      3956160932 Y                 Y                 Y                     2

      комментарий от Igor Usoltsev — 26.06.2012 @ 17:40 | Ответить


RSS-лента комментариев к этой записи. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

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

Отслеживать

Get every new post delivered to your Inbox.

Join 113 other followers

%d bloggers like this: