Oracle mechanics

25.07.2015

12c: как надёжно отключить Automatic Dynamic Statistics на уровне запроса?

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

В предыдущей заметке 12c: Automatic Dynamic Statistics я сослался на документ поддержки Dynamic Sampling Level Is Changed Automatically in 12C (Doc ID 2002108.1), рекомендующий в качестве метода отключения ADS
использовать подсказку на следующем примере:

Disabling Dynamic Statistics

ADS can be disabled but setting optimizer_dynamic_sampling to 0 either with a parameter or using a hint.

Disable for all tables:
select /*+ dynamic_sampling(0) */ …

Пробуя отключить Automatic Dynamic Sampling в одном из тестовых запросов, обратил внимание, что метод с /*+ DYNAMIC_SAMPLING(0) */ нельзя признать надёжным:

12.1.0.2.@ SQL> -- при выполнении запроса с подсказкой /*+ DYNAMIC_SAMPLING(0) */ получаю

SQL> @shared_cu12_noxml ddsbykygmt7qv
 
CURSOR_STATUS PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL DOP DOP_REASON
------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- --- --------------
VALID              2418282357           3909693900       39328287     0 N          N          Y          Y                  Y             119       20        0         11       5   table property

SQL> -- (Dynamic Sampling LEVEL) DS_LEVEL = 11, что согласованно отражается в выводе

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format => 'note'));
...
Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - Degree of Parallelism is 5 because of table property
SQL> -- (точнее, оба скрипта пользуют один источник - V$SQL_PLAN.OTHER_XML)

SQL> -- А вот при отключении параметра на уровне сессии:
SQL> alter session set OPTIMIZER_DYNAMIC_SAMPLING=0;

Session altered.

SQL> -- , либо на уровне запроса хинтом /*+ OPT_PARAM('optimizer_dynamic_sampling' 0) */ ADS действительно отключается: 

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format => 'note'));
...
Note
-----
   - Degree of Parallelism is 5 because of table property -- в секции Note нет упоминания ADS

SQL> -- , скрипт во время выполнения запроса также не находит DS_LEVEL в V$SQL_PLAN.OTHER_XML:

SQL> @shared_cu12_noxml dzhjuwwfdxwkb
 
EXECS USERS_OPENING LAST_LOAD_TIME      LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL DOP DOP_REASON
----- ------------- ------------------- ------------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- --- --------------
    0             5 2015-07-17/13:00:57 17.07.2015 13:25:05   1839775111 VALID              2526823064       37300878     0 N          N          Y          N                  N                       24        0                  5   table property

SQL> -- - интересно, что в описании курсора поля REOPT(V$SQL.IS_REOPTIMIZABLE) и [V$SQL_SHARED_CURSOR.]USE_FEEDBACK_STATS,
SQL> -- а также содержимое V$SQL_REOPTIMIZATION_HINTS (поле REOPT_HINTS) меняются (наполняются содержимым) лишь по окончанию выполнения курсора:
SQL> @shared_cu12_noxml dzhjuwwfdxwkb
 
EXECS USERS_OPENING LAST_LOAD_TIME      LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL DOP DOP_REASON
----- ------------- ------------------- ------------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- --- --------------
    1             1 2015-07-17/13:00:57 17.07.2015 13:25:29   1865692150 VALID              2526823064       37300878     0 N          N          Y          Y                  Y             124       24        0                  5   table property

Кроме невозможности надёжного отключения ADS подсказка DYNAMIC_SAMPLING, согласно документации, лишена возможности форсировать dynamic sampling (level=AUTO), что легко позволяет параметр OPTIMIZER_DYNAMIC_SAMPLING:

SQL> -- , например, с помощью хинта /*+ OPT_PARAM('optimizer_dynamic_sampling' 11) */
SQL> @shared_cu12_noxml 5nfq563bdbv70
 
EXECS USERS_OPENING ELA_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL DOP DOP_REASON
----- ------------- ------------ --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- --- --------------
    0             1     76844620      3510564990            918940959       37385588     0 N          N          Y          N                  N                       24        0         11       5   table property

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format => 'note'));
...
Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - Degree of Parallelism is 5 because of table property

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

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

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

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

Логотип WordPress.com

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

Фотография Twitter

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

Фотография Facebook

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

Google+ photo

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

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: