Как форсировать разбор 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