Oracle mechanics

14.11.2011

Элементы технологий Bind Aware Cursor Sharing / Cardinality Feedback

Filed under: heuristics,hints,Oracle,Oracle new features,parameters — Игорь Усольцев @ 23:59
Tags: , ,

English version

Комментарии к демонстрации на осенней конференции RUOUG

Вступление

Поводом к изучению новых возможностей Oracle 11g стала простая практическая проблема — запрос с определённым набором связанных переменных выполнялся неприемлемо долго. При этом тот же запрос с текстовыми подстановками вместо связанных переменных выполнялся быстро с использованием другого, более подходящего плана выполнения.

Ситуация показалась мне интересной: для «текстового» запроса Oracle смог выбрать «быстрый» план выполнения, а для запроса с использованием связанных переменных , несмотря на известные новые технологии Adaptive/Extended Cursor Sharing, по какой-то причине продолжал использовать «старый» и очевидно неэффективный план выполнения:

-------------------------------------------------------------------------------------
| Id  | Operation                           | Starts | E-Rows | A-Rows |   A-Time   |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      1 |        |      2 |00:01:21.06 |
|   1 |  NESTED LOOPS                       |      1 |        |      2 |00:01:21.06 |
|   2 |   NESTED LOOPS                      |      1 |    890 |      2 |00:01:21.08 |
|   3 |    NESTED LOOPS                     |      1 |    890 |      2 |00:01:21.08 |
|*  4 |     TABLE ACCESS BY INDEX ROWID     |      1 |    890 |  25193 |00:00:07.68 |
|*  5 |      INDEX RANGE SCAN               |      1 |    890 |  25193 |00:00:00.01 |
|   6 |     TABLE ACCESS BY INDEX ROWID     |  25193 |      1 |      2 |00:01:17.23 |
|   7 |      BITMAP CONVERSION TO ROWIDS    |  25193 |        |      2 |00:01:17.17 |
|   8 |       BITMAP AND                    |  25193 |        |      2 |00:01:17.17 |
|   9 |        BITMAP CONVERSION FROM ROWIDS|  25193 |        |  25193 |00:00:00.16 |
|* 10 |         INDEX RANGE SCAN            |  25193 |      1 |  25193 |00:00:00.10 |
|  11 |        BITMAP CONVERSION FROM ROWIDS|  25193 |        |  25193 |00:01:16.99 |
|* 12 |         INDEX RANGE SCAN            |  25193 |      1 |    396M|00:00:00.04 |
|* 13 |    INDEX UNIQUE SCAN                |      2 |      1 |      2 |00:00:00.01 |
|* 14 |   TABLE ACCESS BY INDEX ROWID       |      2 |      1 |      2 |00:00:00.01 |
-------------------------------------------------------------------------------------

, подготовленный и эффективно работающий для другого часто встречающегося набора связанных переменных:

-------------------------------------------------------------------------------------
| Id  | Operation                           | Starts | E-Rows | A-Rows |   A-Time   |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      1 |        |     29 |00:00:01.80 |
|   1 |  NESTED LOOPS                       |      1 |        |     29 |00:00:01.80 |
|   2 |   NESTED LOOPS                      |      1 |    890 |     29 |00:00:01.80 |
|   3 |    NESTED LOOPS                     |      1 |    890 |     29 |00:00:01.80 |
|*  4 |     TABLE ACCESS BY INDEX ROWID     |      1 |    890 |   1944 |00:00:00.01 |
|*  5 |      INDEX RANGE SCAN               |      1 |    890 |   1944 |00:00:00.01 |
|   6 |     TABLE ACCESS BY INDEX ROWID     |   1944 |      1 |     29 |00:00:06.72 |
|   7 |      BITMAP CONVERSION TO ROWIDS    |   1944 |        |     29 |00:00:06.72 |
|   8 |       BITMAP AND                    |   1944 |        |     29 |00:00:06.72 |
|   9 |        BITMAP CONVERSION FROM ROWIDS|   1944 |        |   1944 |00:00:00.01 |
|* 10 |         INDEX RANGE SCAN            |   1944 |      1 |   1944 |00:00:00.01 |
|  11 |        BITMAP CONVERSION FROM ROWIDS|   1944 |        |   1937 |00:00:06.72 |
|* 12 |         INDEX RANGE SCAN            |   1944 |      1 |     31M|00:00:00.02 |
|* 13 |    INDEX UNIQUE SCAN                |     29 |      1 |     29 |00:00:00.01 |
|* 14 |   TABLE ACCESS BY INDEX ROWID       |     29 |      1 |     29 |00:00:00.01 |
-------------------------------------------------------------------------------------

Не вдаваясь в причины «нечувствительности» новых технологий к очевидно изменившейся статистике выполнения запроса A-rows (причина которой, вероятно, состоит в действительной сложности конкретного плана выполнения и значительных отличиях статистики выполнения только на 7-8 уровнях плана — см. отличия A-rows на шаге 12), интересно было попытаться разобраться как заставить Oracle применять или не применять технологии Adaptive/Extended Cursor Sharing ?

Bind Aware Cursor Sharing: термины и определения

Из материалов Oracle Optimizer team известно, что комплексная технология Bind Aware Cursor Sharing (BASC) состоит из 2-х последовательно применяемых функционалов Adaptive Cursor Sharing (ACS) и Extended Cursor Sharing (ECS), при этом ACS играет роль фильтра, ограничивающего применение ECS во избежание излишнего использования ресурсов CPU и памяти:

«Как работает ACS?

  • Мониторит запросы, для которых применима ECS
  • Идентифицирует запросы с различающимся количеством обрабатываемых данных …
  • Разрешает применение ECS для таких запросов»

Также в документе поддержки Adaptive Cursor Sharing Overview [ID 740052.1] можно найти список условий, препятствующих использованию ECS:

If any of the following checks fail ECS will be disabled :

  • Extended cursor sharing is disabled
  • The query has no binds
  • Parallel query is used
  • Certain parameters like («bind peeking»=false) are set
  • Hints are in use
  • Outlines are being used
  • It is a recursive query
  • The number of binds in a given sql statement are greater than 14

В вышеприведённом примере все эти условия были соблюдены, и успешно решить проблему удалось, форсировав применение ECS (иначе говоря, отключив контролирующую технологию ACS) с помощью подсказки /*+ BIND_AWARE*/, упоминаемой в описании Bug 9532657:

For cursors where one knows it wants to be bind aware then add a /*+ BIND_AWARE */ hint. This makes the cursor aware of bind values rather than automatically trying to work out …

— к счастью, проблемный запрос допускал внесение изменений.

Однако вопросы о возможности управления BACS на уровне системы / сессии / запроса остались. На первый взгляд следующие параметры имеют непосредственное отношение к ACS / ECS:

SQL> @param_ _cursor_sharing
NAME                                   VALUE IS_DEF IS_MOD IS_ADJ DSC
-------------------------------------- ----- ------ ------ ------ ----------------------------------------------------------
_optimizer_adaptive_cursor_sharing     TRUE   TRUE  FALSE  FALSE  optimizer adaptive cursor sharing
_optimizer_extended_cursor_sharing     UDO    TRUE  FALSE  FALSE  optimizer extended cursor sharing
_optimizer_extended_cursor_sharing_rel SIMPLE TRUE  FALSE  FALSE  optimizer extended cursor sharing for relational operators

Для тестирования технологии Bind Aware Cursor Sharing (а также формально применимой только к части запроса не использующей связанных переменных, однако тесно связанной с BACS, как будет показано ниже, технологией Cardinality Feedback) мною был подготовлен простой тестовый сценарий в виде набора SQL*Plus скриптов. Тесты проводились на версии Oracle 11.2.0.2 при стандартных параметрах инстанса и легко повторяемы

Тестовая схема

SQL> create table bacs(n1 number, n2 number, c1 char(2000));

Table created.

SQL> create index bacs_n1_idx on bacs(n1) nologging tablespace users;

Index created.

SQL> create index bacs_n2_idx on bacs(n2) nologging tablespace users;

Index created.

Тестовые данные (конечно же, с неравномерно распределёнными значениями):

SQL> insert /*+ append */ into bacs
2  select level, 1, 1
3  from dual
4  connect by level <= 102400
5  union all
6  select 1, level, 1
7  from dual
8  connect by level <= 102400;

204800 rows created.

BACS в отсутствии гистограмм: типичное поведение

По умолчанию Oracle собирает статистику для нашей тестовой таблицы без гистограмм несмотря на очевидно неравномерное распределение значений в столбцах таблицы (skewed data):

SQL> exec DBMS_STATS.gather_table_stats(ownname => NULL,tabname =>  'BACS');

PL/SQL procedure successfully completed.

SQL> select column_name, histogram from user_tab_col_statistics where table_name = 'BACS';
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
N1                             NONE
N2                             NONE
C1                             NONE

Вначале выполняем тестовый запрос с набором редко встречающихся (редких) связанных переменных:

SQL> var v1 number
SQL> var v2 number
SQL> exec :v1 := 1000; :v2 := 1;

PL/SQL procedure successfully completed.
SQL> select/*+ gather_plan_statistics bacs*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

COUNT(DISTINCTN2)
-----------------
                1

1 row selected.

Elapsed: 00:00:00.04

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('','','basic +iostats +cost +note -outline -predicate last'));

PLAN_TABLE_OUTPUT
------------------------

Plan hash value: 3778004472

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     3 (100)|      1 |00:00:00.01 |       3 |      1 |
|   1 |  SORT AGGREGATE                |             |      1 |      1 |            |      1 |00:00:00.01 |       3 |      1 |
|   2 |   VIEW                         | VW_DAG_0    |      1 |      2 |     3  (34)|      1 |00:00:00.01 |       3 |      1 |
|   3 |    HASH GROUP BY               |             |      1 |      2 |     3  (34)|      1 |00:00:00.01 |       3 |      1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      1 |      2 |     2   (0)|      1 |00:00:00.01 |       3 |      1 |
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      1 |      2 |     1   (0)|      1 |00:00:00.01 |       2 |      0 |
------------------------------------------------------------------------------------------------------------------------------

Теперь заменим значения связанных переменных на часто встречающиеся (частые):

SQL> exec :v1 := 1; :v2 := 1;

PL/SQL procedure successfully completed.

SQL> select/*+ gather_plan_statistics bacs*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

COUNT(DISTINCTN2)
-----------------
102400

1 row selected.

Elapsed: 00:00:06.19

Дочерний курсор (child cursor) пока один и отмечен как «чувствительный» к значениям связанных переменных (bind sensitive) — V$SQL.IS_BIND_SENSITIVE=Y, т.е. находящийся под мониторингом ACS, и пока не отмечен как курсор, сгенерированный ECS (bind aware cursor) — V$SQL.IS_BIND_AWARE=N:

SQL> @v$sql_bacs 9u8vgmxdk9bdz

old   9: from v$sql where sql_id = '&1'
new   9: from v$sql where sql_id = '9u8vgmxdk9bdz'

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS

------------- ------------ --------------- ----------------- ----------------- ----------------- ----------

9u8vgmxdk9bdz            0      3778004472 Y                 N                 Y                          2

После повторного выполнения запроса с теми же частыми значениями:

PLAN_TABLE_OUTPUT
------------------------

Plan hash value: 3778004472

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     3 (100)|      1 |00:00:00.15 |   34316 |      1 |
|   1 |  SORT AGGREGATE                |             |      1 |      1 |            |      1 |00:00:00.15 |   34316 |      1 |
|   2 |   VIEW                         | VW_DAG_0    |      1 |      2 |     3  (34)|    102K|00:00:00.16 |   34316 |      1 |
|   3 |    HASH GROUP BY               |             |      1 |      2 |     3  (34)|    102K|00:00:00.14 |   34316 |      1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      1 |      2 |     2   (0)|    102K|00:00:00.11 |   34316 |      1 |
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      1 |      2 |     1   (0)|    102K|00:00:00.02 |     181 |      0 |
------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement

ситуация меняется: в секции «Note» плана выполнения появляется интересное замечание cardinality feedback used for this statement однако план выполнения не изменяется

Насколько я понимаю, ACS использует данные Cardinality Feedback (CF), как один из источников информации для принятии решения считать ли курсор bind-aware (V$SQL.IS_BIND_AWARE) и об использовании ECS для запуска процесса генерации нового плана выполнения. В этом примере данные CF оказали решающее действие, даже несмотря на невозможность ECS построить отличный от существующего план выполнения ввиду отсутствия достаточных для этого данных (гистограмм распределения значений по столбцам)

Трейс CBO (10053 event) показывает, что при попытке построения нового плана ECS не использует Cardinality Feedback, по крайней мере в виде подсказок OPT_ESTIMATE — что легко заметно и по ожидаемому количеству строк E-rows последнего плана выполнения

В обзоре V$SQL можно увидеть новый (CHILD_NUMBER=1) bind-aware курсор с тем же планом выполнения. Прежний курсор при этом отмечен, как более недоступный для использования (IS_SHAREABLE=N):

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
9u8vgmxdk9bdz            0      3778004472 Y                 N                 N                          2
9u8vgmxdk9bdz            1      3778004472 Y                 Y                 Y                          1

При последующих выполнениях тестового запроса с различными редкими или частыми значениями переменных Oracle выбирает курсор #1 и, к счастью, больше не пытается создавать новых:

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
9u8vgmxdk9bdz            0      3778004472 Y                 N                 N                          2
9u8vgmxdk9bdz            1      3778004472 Y                 Y                 Y                          3

Таким образом, в отсутствии гистограмм ECS, который судя по всему строит планы на основе стандартной статистики объектов бд, просто не имеет возможности создать новый план. Однако, пытается сделать это — к счастью, технология BACS ограничивает количество этих тщетных попыток до одной :)

В принципе, поскольку мой тестовый запрос в первый раз выполнялся более 5 секунд и следовательно попал в зону SQL plan monitoring (который является частью Oracle Database Tuning Pack и требует установки STATISTICS_LEVEL в значение не ниже TYPICAL), ECS имел доступ к реальной статистике выполнения запроса и теоретически мог бы использовать её для корректировки плана выполнения:

SQL> @V$SQL_PLAN_MONITOR_LAST 9u8vgmxdk9bdz

SQL_PLAN_HASH_VALUE PLAN_OPERATION    PLAN_OPTIONS    PLAN_OBJECT_NAME  PLAN_CARDINALITY OUTPUT_ROWS
------------------- ----------------- --------------- ----------------- ---------------- -----------
3778004472          SELECT STATEMENT                                                               1
3778004472          SORT              AGGREGATE                                        1           1
3778004472          VIEW                              VW_DAG_0                         2      102400
3778004472          HASH              GROUP BY                                         2      102400
3778004472          TABLE ACCESS      BY INDEX ROWID  BACS                             2      102401
3778004472          INDEX             RANGE SCAN      BACS_N1_IDX                      2      102401

— однако ECS не использует эту информацию

Кроме того, несмотря на упоминание cardinality feedback в плане выполнения, в обзоре V$SQL_SHARED_CURSOR вместо логичной причины генерации нового курсора USE_FEEDBACK_STATS указывается OPTIMIZER_STATS=Y — некая отличная от CF статистика оптимизатора:

SQL> @v$sql_shared_cursor 9u8vgmxdk9bdz

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- ---------------------------------------------
9u8vgmxdk9bdz      0 N                  Y                N                Bind mismatch(25)  |  extended_cursor_sharing
9u8vgmxdk9bdz      1 N                  N                Y

Возможно, это просто особенности отображения и поле USE_FEEDBACK_STATS зарезервировано для классического случая CF без связанных переменных

BACS с гистограммами: типичное поведение

SQL> exec DBMS_STATS.gather_table_stats(ownname => NULL,tabname =>  'BACS',method_opt => 'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select column_name, histogram from user_tab_col_statistics where table_name = 'BACS';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
N1                             HEIGHT BALANCED
N2                             HEIGHT BALANCED
C1                             FREQUENCY

Для первого выполнения с редкими значениями ничего не меняется:

PLAN_TABLE_OUTPUT
------------------------
EXPLAINED SQL STATEMENT:
------------------------
select/*+ gather_plan_statistics bacs+h*/ count(distinct n2) from bacs
where n1 = :v1 and n2 >= :v2

Plan hash value: 3778004472

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     3 (100)|      1 |00:00:00.01 |       3 |      1 |
|   1 |  SORT AGGREGATE                |             |      1 |      1 |            |      1 |00:00:00.01 |       3 |      1 |
|   2 |   VIEW                         | VW_DAG_0    |      1 |      1 |     3  (34)|      1 |00:00:00.01 |       3 |      1 |
|   3 |    HASH GROUP BY               |             |      1 |      1 |     3  (34)|      1 |00:00:00.01 |       3 |      1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |      1 |
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |      0 |
------------------------------------------------------------------------------------------------------------------------------

При повторном выполнении тестового запроса с частыми связанными переменными ECS генерирует новый более подходящий план выполнения с использованием HASH JOIN вместо INDEX RANGE SCAN:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

Plan hash value: 3674053097

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |  1265 (100)|      1 |00:00:00.17 |     565 |
|   1 |  SORT AGGREGATE       |                  |      1 |      1 |            |      1 |00:00:00.17 |     565 |
|   2 |   VIEW                | VW_DAG_0         |      1 |  75881 |  1265   (2)|    102K|00:00:00.18 |     565 |
|   3 |    HASH GROUP BY      |                  |      1 |  75881 |  1265   (2)|    102K|00:00:00.17 |     565 |
|   4 |     VIEW              | index$_join$_001 |      1 |  99981 |   954   (1)|    102K|00:00:03.60 |     565 |
|   5 |      HASH JOIN        |                  |      1 |        |            |    102K|00:00:03.58 |     565 |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |      1 |  99981 |   189   (2)|    102K|00:00:00.02 |     181 |
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |      1 |  99981 |  1502   (2)|    204K|00:00:00.04 |     384 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement

Вновь ACS рапортует об использовании cardinality feedback, но в этом случае вместе с изменившимся планом выполнения, а точнее, для построением нового плана оптимизатор использует вычисленные на основе гистограмм и знания значений связанных переменных (bind peeking) предположительное количество получаемых на этапах выполнения количества строк E-Rows, отличное как от ожидаемого количества строк E-Rows в прежнем плане, так и от данных SQL plan monitoring. При этом в shared pool появляется новый bind-aware (V$SQL.IS_BIND_AWARE=Y) курсор:

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
dq20zj849552k            0      3778004472 Y                 N                 Y                          2
dq20zj849552k            1      3674053097 Y                 Y                 Y                          1

После нескольких последующих выполнений с разными наборами переменных в V$SQL можно найти несколько bind-aware дочерних курсоров в разном статусе (V$SQL.IS_SHARABLE):

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
dq20zj849552k            0      3778004472 Y                 N                 N                          2
dq20zj849552k            1      3674053097 Y                 Y                 Y                          1
dq20zj849552k            2      3778004472 Y                 Y                 N                          2
dq20zj849552k            3      3778004472 Y                 Y                 Y                          1

Это — классичкское поведение BACS, хорошо описанное Optimizer development team

BACS: модифицируемые данные

В продолжение предыдущего примера, удалим из тестовой таблицы строки с частыми значениями (n1 = 1):

SQL> delete from bacs where n1 = 1;

102401 rows deleted.

SQL> commit;

Commit complete.

Что произойдёт  при повторных выполнениях тестового запроса с частыми значениями связанных переменных к таблице с изменившимися данными:

SQL > exec :v1 := 1; :v2 := 1;
PL/SQL procedure successfully completed.
SQL > select/*+ gather_plan_statistics bacs+h*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

COUNT(DISTINCTN2)
-----------------
0

1 row selected.

SQL > /
...

SQL > /
...
PLAN_TABLE_OUTPUT
---------------------------

Plan hash value: 3674053097

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |  1265 (100)|      1 |00:00:00.01 |     181 |
|   1 |  SORT AGGREGATE       |                  |      1 |      1 |            |      1 |00:00:00.01 |     181 |
|   2 |   VIEW                | VW_DAG_0         |      1 |  75881 |  1265   (2)|      0 |00:00:00.01 |     181 |
|   3 |    HASH GROUP BY      |                  |      1 |  75881 |  1265   (2)|      0 |00:00:00.01 |     181 |
|   4 |     VIEW              | index$_join$_001 |      1 |  99981 |   954   (1)|      0 |00:00:00.01 |     181 |
|   5 |      HASH JOIN        |                  |      1 |        |            |      0 |00:00:00.01 |     181 |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |      1 |  99981 |   189   (2)|      0 |00:00:00.01 |     181 |
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |      0 |  99981 |  1502   (2)|      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
- cardinality feedback used for this statement

При выполнении Oracle использует тот же план выполнения с HASH JOIN. Статистика выполнения плана A-Rows заметно отличается от оценок оптимизатора E-Rows. V$SQL показывает,что при этом используется тот же дочерний курсор (child_number=1), технология BACS повторно не срабатывает:

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
dq20zj849552k            0      3778004472 Y                 N                 N                          2
dq20zj849552k            1      3674053097 Y                 Y                 Y                          4
dq20zj849552k            2      3778004472 Y                 Y                 N                          2
dq20zj849552k            3      3778004472 Y                 Y                 Y                          1

Повторный сбор статистики не помогает:

SQL> exec DBMS_STATS.gather_table_stats(ownname => NULL,tabname =>  'BACS',method_opt => 'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select column_name, histogram from user_tab_col_statistics where table_name = 'BACS';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
N1                             NONE
N2                             FREQUENCY
C1                             FREQUENCY
...
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
dq20zj849552k            0      3778004472 Y                 N                 N                          2
dq20zj849552k            1      3674053097 Y                 Y                 Y                          7
dq20zj849552k            2      3778004472 Y                 Y                 N                          2
dq20zj849552k            3      3778004472 Y                 Y                 Y                          1

Попытки форсировать применение ECS для изменения плана с помощью подсказки /*+ BIND_AWARE*/ также не приносят результатов :(

Мне известен только один способ заставить Oracle сгенерировать новый план выполнения с учётом изменившихся данных — пересоздание курсора:

  • меняя текст запроса
  • удаляя существующий курсор из shared pool:
SQL> declare
2      v_address_hash varchar2(60);
3  begin
4      select address||', '||hash_value
5         into v_address_hash
6      from v$sqlarea
7      where sql_id = 'dq20zj849552k';
8  sys.dbms_shared_pool.purge(v_address_hash, 'c');
9  end;
10  /

PL/SQL procedure successfully completed.
  • или (как предлагал Илья Деев на семинаре) пересобрать статистику с параметром no_invalidate => false, что также приводит к удалению и пересозданию курсора в shared pool

Одноразовое применение BACS (несмотря на изменения данных) — значительный недостаток технологии

BACS с гистограммами: форсирование использования ECS

Начальные тестовые данные. Тестовый запрос с подсказкой /*+ BIND_AWARE */ и редкими значениями переменных:

SQL> exec :v1 := 1000; :v2 := 1;

PL/SQL procedure successfully completed.

SQL> select/*+ BIND_AWARE*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

COUNT(DISTINCTN2)
-----------------
1

1 row selected.

PLAN_TABLE_OUTPUT
---------------------------

Plan hash value: 3778004472

----------------------------------------------------------------------------
| Id  | Operation                      | Name        | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |        |     3 (100)|
|   1 |  SORT AGGREGATE                |             |      1 |            |
|   2 |   VIEW                         | VW_DAG_0    |      1 |     3  (34)|
|   3 |    HASH GROUP BY               |             |      1 |     3  (34)|
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      1 |     2   (0)|
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      1 |     1   (0)|
----------------------------------------------------------------------------

Далее ECS успешно применяется и изменяет план при первом же выполнении запроса с частыми переменными:

SQL> exec :v1 := 1; :v2 := 1;

PL/SQL procedure successfully completed.

SQL> select/*+ BIND_AWARE*/ count(distinct n2) f

COUNT(DISTINCTN2)
-----------------
102400

1 row selected.

PLAN_TABLE_OUTPUT
------------------------
EXPLAINED SQL STATEMENT:
------------------------
select/*+ BIND_AWARE*/ count(distinct n2) from bacs where n1 = :v1 and
n2 >= :v2

Plan hash value: 3674053097

------------------------------------------------------------------------
| Id  | Operation             | Name             | E-Rows | Cost (%CPU)|
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |        |  1274 (100)|
|   1 |  SORT AGGREGATE       |                  |      1 |            |
|   2 |   VIEW                | VW_DAG_0         |  76709 |  1274   (2)|
|   3 |    HASH GROUP BY      |                  |  76709 |  1274   (2)|
|   4 |     VIEW              | index$_join$_001 |    101K|   959   (1)|
|   5 |      HASH JOIN        |                  |        |            |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |    101K|   192   (2)|
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |    101K|  1502   (2)|
------------------------------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

Кроме изменения плана при первом же выполнении, важно отметить, что cardinality feedback used for this statement не упоминается в секции Note: ACS отключён подсказкой BIND_AWARE, и естественно не может использовать данные cardinality feedback, насколько я понимаю

ECS при построении нового плана использует стандартную статистику объектов бд и это заметно по оценкам оптимизатора E-Rows, которые отличаются от оценок E-Rows при классическом применении Cardinality Feedback (через подсказки OPT_PARAM во время генерации плана), как будет показано ниже

В обзоре V$SQL  оба дочерних курсора помечены как bind-aware, оба имеют рабочий статус (IS_SHARABLE=Y) и, несмотря на предположительное отключение ACS, выполнение запросов мониторится IS_BIND_SENSITIVE=Y:

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
gvhr0bvnn3yph            0      3778004472 Y                 Y                 Y                          1
gvhr0bvnn3yph            1      3674053097 Y                 Y                 Y                          1

При других редких значениях переменных используется тот же курсор с CHILD_NUMBER=0 либо генерируется новый:

SQL> exec :v1 := 10; :v2 := 1;

PL/SQL procedure successfully completed.

SQL> select/*+ BIND_AWARE*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;
...
SQL> exec :v1 := 10; :v2 := 10;

PL/SQL procedure successfully completed.

SQL> select/*+ BIND_AWARE*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

COUNT(DISTINCTN2)
-----------------
0

1 row selected.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
gvhr0bvnn3yph            0      3778004472 Y                 Y                 N                          2
gvhr0bvnn3yph            1      3674053097 Y                 Y                 Y                          1
gvhr0bvnn3yph            2      3778004472 Y                 Y                 Y                          1

Подсказка BIND_AWARE могут быть использована для отключения ACS и форсированного использования ECS только на уровне запроса

В точности тот же результат может быть получен, устанавливая значение параметра _OPTIMIZER_ADAPTIVE_CURSOR_SHARING=FALSE на уровне системы или сессии, либо в виде подсказки /*+ opt_param(‘_optimizer_adaptive_cursor_sharing’ ‘false’)*/ на уровне запроса

BACS: как отключить?

Параметр _OPTIMIZER_EXTENDED_CURSOR_SHARING выглядит подходящим для отключения BACS через отключение ECS, но допустимые значения выглядят достаточно странно:

SQL> @pvalid_ _optimizer_extended_cursor_sharing

PARAMETER                                          VALUE
-------------------------------------------------- -----
_optimizer_extended_cursor_sharing                 NONE
                                                   UDO

По умолчанию _OPTIMIZER_EXTENDED_CURSOR_SHARING = UDO (User Defined Operation), что соответствует определению параметра:

«Controls ECS for user-defined operators»

Модификация параметра _OPTIMIZER_EXTENDED_CURSOR_SHARING никак не влияет не только на поведение ECS для тестового запроса (который очевидно не содержит никаких User Defined Operation), но также не влияет на ECS для запроса с использованием пользовательской функции:

SQL> create or replace function udo(n1 number, v1 number)
2   return number
3  as
4  begin
5    if n1 = v1 then return 1;
6       else return 0;
7    end if;
8  end;
9  /

Function created.

SQL> select/*+ opt_param('_optimizer_extended_cursor_sharing' 'none')*/ count(distinct n2) from bacs where udo(n1, :v1) = 1;
...
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
57g18qxn49jpu            0       543507907 N                 N                 Y                       2

SQL> select/*+ opt_param('_optimizer_extended_cursor_sharing' 'udo')*/ count(distinct n2) from bacs where udo(n1, :v1) = 1;
...
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
3nq0wp7qf6wp8            0       543507907 N                 N                 Y                       2

— либо моя реализация UDO некорректна

Следующий параметр _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL («controls ECS for relational predicates») упоминается в MOS 11657468:

Disable adaptive cursor sharing by setting:

_optimizer_adaptive_cursor_sharing = false

AND

_optimizer_extended_cursor_sharing_rel = «none»

Установив значение _optimizer_extended_cursor_sharing_rel=none в тестовом запросе:

PLAN_TABLE_OUTPUT
------------------------
EXPLAINED SQL STATEMENT:
------------------------
select/*+ opt_param('_optimizer_extended_cursor_sharing_rel' 'none')*/
count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2

Plan hash value: 3778004472

----------------------------------------------------------------------------
| Id  | Operation                      | Name        | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |        |     3 (100)|
|   1 |  SORT AGGREGATE                |             |      1 |            |
|   2 |   VIEW                         | VW_DAG_0    |      2 |     3  (34)|
|   3 |    HASH GROUP BY               |             |      2 |     3  (34)|
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      2 |     2   (0)|
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      2 |     1   (0)|
----------------------------------------------------------------------------

, получим отключение мониторинга выполнения курсора IS_BIND_SENSITIVE=N:

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
4fdv1yngrgg82            0      3778004472 N                 N                 Y                          4

Интересно , что установка _optimizer_extended_cursor_sharing_rel = «none» успешно отключает ECS (и как следствие ACS), не только для простых реляционных операторов (>,<,=,<>) но также для оператора  like (что было обещано в блоге Optimizer team, начиная с 11.2.0.2) и текстовых функций substr и instr !

SQL> select/*+ opt_param('_optimizer_extended_cursor_sharing_rel' 'none')*/ count(distinct n2) from bacs where instr(to_char(n1),:v1) > 0 and c1 like '%'||:v2||'%';
...
Plan hash value: 543507907

---------------------------------------------------------------
| Id  | Operation            | Name     | E-Rows | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |        | 18739 (100)|
|   1 |  SORT AGGREGATE      |          |      1 |            |
|   2 |   VIEW               | VW_DAG_0 |    511 | 18739   (1)|
|   3 |    HASH GROUP BY     |          |    511 | 18739   (1)|
|   4 |     TABLE ACCESS FULL| BACS     |    512 | 18521   (1)|
---------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
...
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
...
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
56hhxcaaqgfgd            0       543507907 N                 N                 Y                       3

SQL> select/*+ opt_param('_optimizer_extended_cursor_sharing_rel' 'none')*/ count(distinct n2) from bacs where instr(to_char(n1),:v1) > 0;
...
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
4pjgd1q8hna53            0       543507907 N                 N                 Y                       1

Можно предположить, что установка парамета _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL=NONE отключает ECS (BACS) для широкого диапазона предикатов, однозначно не ограниченного простыми реляционными операторами

Для отключения ECS (BACS) на уровне запроса также может успешно применяться подсказка /*+ NO_BIND_AWARE*/:

SQL> select/*+ no_bind_aware*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

SQL> @v$sql_bacs 2tfwrnqfs9k8t

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
2tfwrnqfs9k8t            5      3778004472 N                 N                 Y                          7

Cardinality Feedback (CF)

Из блога Oracle Optimizer team:

… подходит для запросов со стабильными данными… Опция не предполагает изменения планов со временем в зависимости от изменения данных в таблицах [т.е. CF применяется одноразово — точно также, как и технология BACS, что показано выше]

… использование статистики выполнения для запросов, использующих связанные переменные, является проблематичной

В Oracle 11gR2, cardinality feedback мониторит и исправляет следующие статистики выполнения запросов (cardinalities):

  • Single table cardinality (after filter predicates are applied)
  • Index cardinality (after index filters are applied)
  • Cardinality produced by a group by or distinct operator

Поскольку для использования CF подробная статистика не требуется, я использовал те же самые тестовую схему и данные без гистограмм:

SQL> select column_name, histogram from user_tab_col_statistics where table_name = 'BACS';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
N1                             NONE
N2                             NONE
C1                             NONE

Первое выполнение тестового запроса, выбирающего частовстречающиеся данные :

SQL> select/*+ gather_plan_statistics cf*/ count(distinct n2) from bacs where n1 = 1 and n2 >= 1;

COUNT(DISTINCTN2)
-----------------
102400

Elapsed: 00:00:06.33

Plan hash value: 3778004472

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     3 (100)|      1 |00:00:06.63 |   34316 |  34134 |
|   1 |  SORT AGGREGATE                |             |      1 |      1 |            |      1 |00:00:06.63 |   34316 |  34134 |
|   2 |   VIEW                         | VW_DAG_0    |      1 |      2 |     3  (34)|    102K|00:00:06.63 |   34316 |  34134 |
|   3 |    HASH GROUP BY               |             |      1 |      2 |     3  (34)|    102K|00:00:06.62 |   34316 |  34134 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      1 |      2 |     2   (0)|    102K|00:00:06.37 |   34316 |  34134 |
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      1 |      2 |     1   (0)|    102K|00:00:00.10 |     181 |      0 |
------------------------------------------------------------------------------------------------------------------------------

После второго выполнения:

SQL> select/*+ gather_plan_statistics cf*/ count(distinct n2) from bacs where n1 = 1 and n2 >= 1;

Plan hash value: 3674053097

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |   955 (100)|      1 |00:00:00.19 |     565 |
|   1 |  SORT AGGREGATE       |                  |      1 |      1 |            |      1 |00:00:00.19 |     565 |
|   2 |   VIEW                | VW_DAG_0         |      1 |    102K|   955   (2)|    102K|00:00:00.20 |     565 |
|   3 |    HASH GROUP BY      |                  |      1 |    102K|   955   (2)|    102K|00:00:00.19 |     565 |
|   4 |     VIEW              | index$_join$_001 |      1 |    102K|   581   (1)|    102K|00:00:03.68 |     565 |
|   5 |      HASH JOIN        |                  |      1 |        |            |    102K|00:00:03.66 |     565 |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |      1 |    102K|   193   (1)|    102K|00:00:00.02 |     181 |
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |      1 |    102K|  1499   (1)|    204K|00:00:00.03 |     384 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
- cardinality feedback used for this statement

Трейс оптимизатора (10053) показывает все три типа корректировок из описания технологии (TABLE, INDEX, GROUP BY):

OPT_ESTIMATE (GROUP_BY ROWS=102400.000000 )
OPT_ESTIMATE (TABLE "BACS" ROWS=102401.000000 )
OPT_ESTIMATE (INDEX_SCAN "BACS" "BACS_N1_IDX" MIN=102401.000000 )
OPT_ESTIMATE (INDEX_FILTER "BACS" "BACS_N1_IDX" ROWS=102401.000000 ) */

— скорректированные оценки оптимизатора (cardinality) при этом полностью совпадают с данными статистики выполнения из V$SQL_PLAN_MONITOR:

SQL_PLAN_HASH_VALUE PLAN_OPERATION    PLAN_OPTIONS    PLAN_OBJECT_NAME  PLAN_CARDINALITY OUTPUT_ROWS
------------------- ----------------- --------------- ----------------- ---------------- -----------
3778004472          SELECT STATEMENT                                                                1
3778004472          SORT              AGGREGATE                                         1           1
3778004472          VIEW                              VW_DAG_0                          2      102400
3778004472          HASH              GROUP BY                                          2      102400
3778004472          TABLE ACCESS      BY INDEX ROWID  BACS                              2      102401
3778004472          INDEX             RANGE SCAN      BACS_N1_IDX                       2      102401

Аргументы OPT_ESTIMATE при этом берутся из отдельных источников (доступных в отсутствие данных в V$SQL_PLAN_MONITOR), которые также, как и данные SQL Plan Monitoring зависят от значения парамера STATISTICS_LEVEL на уровне инстанса: STATISTICS_LEVEL=BASIC отключает BACS и CF — см. Christian Antognini Impact of STATISTICS_LEVEL on Cardinality Feedback and Adaptive Cursor Sharing

Использование CF (USE_FEEDBACK_STATS=Y) однозначно отображается в обзоре V$SQL_SHARED_CURSOR:

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- -------------------------
5cvvajnzf6h03      0 Y                  N                N                Optimizer mismatch(13)  |
5cvvajnzf6h03      1 N                  N                N

Cardinality Feedback: как отключить?

Хорошо описанный параметр _optimizer_use_feedback=false (true по умолчанию) может быть успешно использован для отключения CF на уровне сессии и системы:

SQL> alter session set "_optimizer_use_feedback" = false;

либо на уровне отдельного запроса в виде подсказки:

SQL> select /*+ opt_param('_optimizer_use_feedback' 'false')*/ ...

_fix_control 6699059

SQL> select bugno, description, optimizer_feature_enable, value, is_default from v$session_fix_control where bugno = 6699059;
BUGNO DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE       VALUE IS_DEFAULT
 ---------- ---------------------------------------------------------------- ------------------------- ---------- ----------
    6699059 enable cardinality feedback-based cursor sharing                 11.2.0.1                           1          1

— обычно используемый для разрешения CF, по слухам портированного в последние патчсеты 11.1.0.7.* также успешно отключает CF в Oracle 11.2 на уровне сессии и системы:

SQL> alter session set "_fix_control"="6699059:OFF";

На уровне запроса применение подсказки /*+ OPT_PARAM(‘_fix_control’ ‘6699059:0’)*/ даёт неоднозначные результаты и не может быть использовано для отключения CF

Удивительно, но установка параметра _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL=NONE отключает CF (также, как и ECS) для тех же «сложных» предикатов!

При дефолтном значении _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL:

SQL> @param_ _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL

NAME                                       VALUE                                    IS_DEF
------------------------------------------ ---------------------------------------- ------
_optimizer_extended_cursor_sharing_rel     SIMPLE                                   TRUE

SQL> select/*+ gather_plan_statistics CF*/ count(distinct n2) from bacs where instr(to_char(n1),'1') > 0 and c1 like '%1%';

COUNT(DISTINCTN2)
-----------------
102400

1 row selected.

PLAN_TABLE_OUTPUT
--------------------------
Plan hash value: 543507907

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |        | 18736 (100)|      1 |00:00:03.06 |   68271 |  68267 |
|   1 |  SORT AGGREGATE      |          |      1 |      1 |            |      1 |00:00:03.06 |   68271 |  68267 |
|   2 |   VIEW               | VW_DAG_0 |      1 |    511 | 18736   (1)|    102K|00:00:03.06 |   68271 |  68267 |
|   3 |    HASH GROUP BY     |          |      1 |    511 | 18736   (1)|    102K|00:00:03.05 |   68271 |  68267 |
|   4 |     TABLE ACCESS FULL| BACS     |      1 |    512 | 18518   (1)|    145K|00:00:02.95 |   68271 |  68267 |
-----------------------------------------------------------------------------------------------------------------

SQL> select/*+ gather_plan_statistics CF*/ count(distinct n2) from bacs where instr(to_char(n1),'1') > 0 and c1 like '%1%';

PLAN_TABLE_OUTPUT
--------------------------
Plan hash value: 543507907

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |        | 67857 (100)|      1 |00:00:03.03 |   68271 |  68267 |
|   1 |  SORT AGGREGATE      |          |      1 |      1 |            |      1 |00:00:03.03 |   68271 |  68267 |
|   2 |   VIEW               | VW_DAG_0 |      1 |    102K| 67857   (1)|    102K|00:00:03.03 |   68271 |  68267 |
|   3 |    HASH GROUP BY     |          |      1 |    102K| 67857   (1)|    102K|00:00:03.02 |   68271 |  68267 |
|   4 |     TABLE ACCESS FULL| BACS     |      1 |    145K| 18518   (1)|    145K|00:00:02.90 |   68271 |  68267 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
- cardinality feedback used for this statement

— CF успешно работает, что отражается:

  • в изменённом количестве ожидаемого количества строк E-Rows плана выполнения
  • в секции «Note» плана выполнения
  • в значении V$SQL_SHARED_CURSOR.USE_FEEDBACK_STATS:
SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
 ------------- ------ ------------------ ---------------- ---------------- -------------------------
 35vuppa4p6pyk      0 Y                  N                N                Optimizer mismatch(13)  |
 35vuppa4p6pyk      1 N                  N                N

При изменении значения _optimizer_extended_cursor_sharing_rel на уровне запроса:

SQL> select/*+ gather_plan_statistics opt_param('_optimizer_extended_cursor_sharing_rel' 'none') CF*/ count(distinct n2) from bacs where instr(to_char(n1),'1') > 0 and c1 like '%1%';
...
SQL> /
SQL> /
SQL> /

PLAN_TABLE_OUTPUT
--------------------------
Plan hash value: 543507907

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |        | 18736 (100)|      1 |00:00:03.02 |   68271 |  68267 |
|   1 |  SORT AGGREGATE      |          |      1 |      1 |            |      1 |00:00:03.02 |   68271 |  68267 |
|   2 |   VIEW               | VW_DAG_0 |      1 |    511 | 18736   (1)|    102K|00:00:03.02 |   68271 |  68267 |
|   3 |    HASH GROUP BY     |          |      1 |    511 | 18736   (1)|    102K|00:00:03.01 |   68271 |  68267 |
|   4 |     TABLE ACCESS FULL| BACS     |      1 |    512 | 18518   (1)|    145K|00:00:02.92 |   68271 |  68267 |
-----------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
...
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')

— в секции «Outline Data» плана можно увидеть, что оптимизатор успешно воспринял изменение параметра  _optimizer_extended_cursor_sharing_rel. Как следствие значения E-Rows в плане, данные системных обзоров v$sql, v$sql_shared_cursor подтверждают, что CF не используется:

SQL> @v$sql 3f81v8dc7sk6u

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
3f81v8dc7sk6u            0       543507907 N                 N                 Y                          4

SQL> @v$sql_shared_cursor 3f81v8dc7sk6u

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- ------------------------------------------------------------
3f81v8dc7sk6u      0 N                  N                N

Проблема Cardinality Feedback: низкая чувствительность

Под низкой чувствительностью CF я имею в виду необоснованное применение технологии на небольших объёмах данных, когда построить более оптимальный, отличный от существующего план выполнения достаточно сложно

Например, если взять ту же тестовую схему с небольшим количеством данных — всего 8 строк вместо 200k:

SQL> create table bacs(n1 number, n2 number, c1 char(2000));

Table created.

SQL> create index bacs_n1_idx on bacs(n1) nologging tablespace users;

Index created.

SQL> create index bacs_n2_idx on bacs(n2) nologging tablespace users;

Index created.

SQL> insert /*+ append */ into bacs
2  select level, 1, 1
3  from dual
4  connect by level <= 4
5  union all
6  select 1, level, 1
7  from dual
8  connect by level <= 4;

8 rows created.

И дважды выполнить тот же тестовый запрос:

SQL> select/*+ gather_plan_statistics cf4*/ count(distinct n2) from bacs where n1 = 1 and n2 >= 1;

COUNT(DISTINCTN2)
-----------------
4

1 row selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------

Plan hash value: 3674053097

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |     4 (100)|      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE       |                  |      1 |      1 |            |      1 |00:00:00.01 |       2 |
|   2 |   VIEW                | VW_DAG_0         |      1 |      2 |     4  (50)|      4 |00:00:00.01 |       2 |
|   3 |    HASH GROUP BY      |                  |      1 |      2 |     4  (50)|      4 |00:00:00.01 |       2 |
|   4 |     VIEW              | index$_join$_001 |      1 |      2 |     3  (34)|      5 |00:00:00.01 |       2 |
|   5 |      HASH JOIN        |                  |      1 |        |            |      5 |00:00:00.01 |       2 |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |      1 |      2 |     1   (0)|      5 |00:00:00.01 |       1 |
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |      1 |      2 |     2  (50)|      8 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------------

SQL> /

COUNT(DISTINCTN2)
-----------------
4

1 row selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------

Plan hash value: 3674053097

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |     4 (100)|      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE       |                  |      1 |      1 |            |      1 |00:00:00.01 |       2 |
|   2 |   VIEW                | VW_DAG_0         |      1 |      2 |     4  (50)|      4 |00:00:00.01 |       2 |
|   3 |    HASH GROUP BY      |                  |      1 |      2 |     4  (50)|      4 |00:00:00.01 |       2 |
|   4 |     VIEW              | index$_join$_001 |      1 |      2 |     3  (34)|      5 |00:00:00.01 |       2 |
|   5 |      HASH JOIN        |                  |      1 |        |            |      5 |00:00:00.01 |       2 |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |      1 |      2 |     1   (0)|      5 |00:00:00.01 |       1 |
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |      1 |      2 |     2  (50)|      8 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
- cardinality feedback used for this statement

Как видно, при повторном выполнении вполне успешно используется cardinality feedback:

SQL> @v$sql 69sxnafkttsbr
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
69sxnafkttsbr            0      3674053097 N                 N                 N                          1
69sxnafkttsbr            1      3674053097 N                 N                 Y                          1

SQL> @v$sql_shared_cursor 69sxnafkttsbr

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- ---------------------------------
69sxnafkttsbr      0 Y                  N                N                Optimizer mismatch(13)  |
69sxnafkttsbr      1 N                  N                N

При этом оба сгенерированных дочерних курсора (последний из которых является прямым порождением CF) используют абсолютно идентичные планы выполнения.

Трейс CBO не содержит корректирующих подсказок OPT_ESTIMATE. Насколько я понимаю, во время генерации плана выполнения оптимизатор отлично понимает / вычисляет отсутствие необходимости вносить в план какие-то изменения, но решение генерировать новый курсор (выполнять новый разбор запроса) принимается технологией CF  до момента генерации

Кроме низкой чувствительности, к существенным недостаткам CF относится упомянутая выше одноразовость применения — при изменении данных таблиц, даже после обновления статистики, CF не будет применяться повторно к существующему в shared pool курсору, однажды сгенерированному с использованием CF

Практические выводы

Adaptive Cursor Sharing (ACS) и Extended Cursor Sharing (ECS) представляют собой пару взаимосвязанных, последовательно применяемых технологий, совместно называемых Bind Aware Cursor Sharing (BASC)

Предварительное использование Adaptive Cursor Sharing (для оценки необходимости последующего применения ECS) может быть отключено, форсируя таким образом применение Extended Cursor Sharing для всех курсоров (удовлетворяющих условиям ECS):

  • параметром _OPTIMIZER_ADAPTIVE_CURSOR_SHARING=FALSE — на уровне системы / сессии / запроса
  • подсказкой /*+ BIND_AWARE*/ — на уровне отдельного запроса

— недостатком при отключении ACS может стать избыточная генерация курсоров

Adaptive Cursor Sharing использует данные Cardinality Feedback в качестве одного из источников информации при принятии решения о генерации нового bind-aware дочернего курсора, для чего применяется Extended Cursor Sharing

Extended Cursor Sharing использует стандартную статистику объектов бд (таблиц, индексов, гистограммы распределения значений столбцов,…) для генерации нового плана выполнения

Применение Extended Cursor Sharing (и, как следствие, ACS за отсутствием необходимости) может быть отключено на уровне запроса подсказкой /*+ NO_BIND_AWARE*/

Применение технологий Extended Cursor Sharing (и ACS) и Cardinality Feedback может быть отключено для широкого круга реляционных операторов, включая, но, вероятно, не ограничиваясь использованием операторов like и функций instr, substr установкой значения параметра _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL=NONE на уровне системы / сессии / запроса

Для отключения применения Cardinality Feedback могут быть использованы следующие параметры:

  • «_optimizer_use_feedback» = false — на уровне системы / сессии / запроса
  • «_fix_control»=»6699059:OFF» — на уровне системы / сессии

Непонятно, как форсировать применение Cardinality Feedback, возможно, в этом просто нет необходимости

Общим недостатком технологий BACS и CF является их одноразовое применение — что может быть проблемой для частовыполняющихся запросов к модифицируемым данным

Дополнительным легкозаметным недостатком CF является низкая чувствительность: избыточное применение технологии на небольших объёмах данных, в результате которого генерируются дочерние курсоры с полностью идентичными планами выполнения, что может приводить к необоснованному расходованию ресурсов памяти м ЦПУ

1 комментарий »

  1. Thanks

    комментарий от Dmitriy — 06.02.2014 @ 23:55 | Ответить


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 такие блоггеры, как: