Oracle mechanics

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

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

11.2.0.3.ORCL112@SCOTT SQL> alter session set statistics_level=all;

Session altered.

SQL> create or replace view myview as select * from emp where sal > userenv('client_info');

View created.

SQL> exec dbms_application_info.set_client_info('0');

PL/SQL procedure successfully completed.

SQL> select * from myview;

...

14 rows selected.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','','all allstats advanced -alias -outline -projection last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4j640nxswh0tg, child number 0
-------------------------------------
select * from myview

Plan hash value: 3956160932

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |     14 |00:00:00.01 |       7 |      6 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |    38 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |      6 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">TO_NUMBER(USERENV('CLIENT_INFO')))

— «средневзвешенный» план предполагает получить E-Rows=1 строку, запрос получает 14 A-rows

Oracle проблему замечает и в обзоре v$sql_shared_cursor помечает курсор USE_FEEDBACK_STATS=Y для использования Cardinality Feedback в дальнем (скрипт shared_cu.sql):

SQL> @shared_cu 4j640nxswh0tg

   INST_ID SQL_ID         CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
---------- ------------- ------ ---------- ---------- ---------- ------------------ ---------------- ---------------- ---------------------
         1 4j640nxswh0tg      0 N          N          Y          Y                  N                N

При следующем выполнении независимо от изменения значения параметра обзора userenv(‘client_info’) (недоступном на этапе компиляции плана) по результатам последнего выполнения строится новый план:

SQL> exec dbms_application_info.set_client_info('1000');

PL/SQL procedure successfully completed.

SQL> select * from myview;

...

12 rows selected.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','','all allstats advanced -alias -outline -projection last'));

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

SQL_ID  4j640nxswh0tg, child number 1
-------------------------------------
select * from myview

Plan hash value: 3956160932

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |     12 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     14 |   532 |     3   (0)| 00:00:01 |     12 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">TO_NUMBER(USERENV('CLIENT_INFO')))

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

— неотличающийся от предыдущего за исключением скорректированных ожиданий E-Rows, которые в зависимости от изменяющихся параметров обзора могут опять отличаться от действительных данных A-Rows

Соответственно, появляется новый курсор со всеми признаками cardinality feedback:

SQL> @shared_cu 4j640nxswh0tg

INST_ID PLAN_HASH_VALUE  CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------- --------------- ------ ---------- ---------- ---------- ------------------ ---------------- ---------------- ----------------------
      1      3956160932      0 N          N          N          Y                  N                N        Optimizer mismatch(13)  |
      1      3956160932      1 N          N          Y          N                  N                N

Если не отключать, механизм cardinality feedback будет срабатывать достаточно часто, плодя курсоры на основании фактически случайной статистики выполнения.

Тот же эффект можно наблюдать при использовании для передачи значений параметров в обзор с использованием SYS_CONTEXT или публичных переменных пользовательского PL/SQL пакета — SQL не получает сведений о значениях параметров при переключении контекста (по крайней мере в 11g)

На практике для достаточно сложного запроса с использованием параметризованного обзора можно видеть:

SQL> select inst_id,
  2         child_number,
  3         plan_hash_value,
  4         OPTIMIZER_COST,
  5         SQL_PLAN_BASELINE,
  6         executions,
  7         round(elapsed_time / nvl(executions,1)/1000000) as elaspsed_by_exec,
  8         round(fetches / nvl(executions,1)) as fetches_by_exec,
  9         round(rows_processed / nvl(executions,1)) as rows_by_exec
 10    from gv$sql
 11   where sql_id = '9s7ppf88qzx2w'
 12   and executions is not null
 13  order by rows_processed / nvl(executions,1)
 14  /

INST_ID CHILD_NUMBER PLAN_HASH_VALUE OPTIMIZER_COST SQL_PLAN_BASELINE EXECUTIONS ELASPSED_BY_EXEC FETCHES_BY_EXEC ROWS_BY_EXEC
------- ------------ --------------- -------------- ----------------- ---------- ---------------- --------------- ------------
      2            1      1299736084          59250                          117                3               2            9
      2            4      1592936632          60729                            9                8               2           13
      1            4      1986663636          63365                            4              343               2           17
      2            2      3314949587          76779                            1               47               2           18
      2            3       307394549          77022                          167                5               3           26
      1            3      3913741014          63304                            9              185               4           32
      1            6      1986663636          63336                            7              317               4           37
      2            6      3314949587          81668                            6               30               5           44
      2            0      3998590328          59248                            1              247               6           71
      2            5      1592936632          60743                            2              101              29          278

SQL> @shared_cu "9s7ppf88qzx2w"

INST_ID PLAN_HASH_VALUE CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------- --------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- ------------------------------------------------------------
      1      3913741014     3 N          N          N          Y                  N                N                Optimizer mismatch(13)  |
      1      1986663636     4 N          N          N          Y                  N                N                Optimizer mismatch(13)  |
      1      1986663636     6 N          N          Y          N                  N                N                NLS Settings(0)  |
      2      3998590328     0 N          N          Y          Y                  N                N                Optimizer mismatch(12)  |
      2      1299736084     1 N          N          N          Y                  N                N                Optimizer mismatch(13)  |
      2      3314949587     2 N          N          N          Y                  N                N                Optimizer mismatch(13)  |
      2       307394549     3 N          N          Y          N                  N                N                NLS Settings(0)  |
      2      1592936632     4 N          N          N          Y                  N                N                Optimizer mismatch(13)  |
      2      1592936632     5 N          N          N          Y                  N                N                Optimizer mismatch(13)  |
      2      3314949587     6 N          N          N          Y                  N                N                Optimizer mismatch(13)  |

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

Можно зафиксировать предположительно удачный план с помощью SPM чтобы избавится по крайней мере от излишнего парсинга (hard parse) по причине cardinality feedback:

SQL> declare res number;
  2  begin
  3    res := dbms_spm.load_plans_from_cursor_cache(sql_id => '9s7ppf88qzx2w', plan_hash_value => '307394549' );
  4    res := DBMS_SPM.alter_sql_plan_baseline('SQL_66a57f45ad0a7744','SQL_PLAN_6d9bz8qqhnxu44584d26d','fixed','yes');
  5    res := DBMS_SPM.alter_sql_plan_baseline('SQL_66a57f45ad0a7744','SQL_PLAN_6d9bz8qqhnxu44584d26d','autopurge','no');
  6  end;
  7  /

PL/SQL procedure successfully completed

SQL> select inst_id,
  2         child_number,
  3         plan_hash_value,
  4         SQL_PLAN_BASELINE,
  5         executions,
  6         round(elapsed_time / nvl(executions,1)/1000000) as elaspsed_by_exec,
  7         round(fetches / nvl(executions,1)) as fetches_by_exec,
  8         round(rows_processed / nvl(executions,1)) as rows_by_exec
  9    from gv$sql
 10   where sql_id = '9s7ppf88qzx2w'
 11   and executions is not null
 12  order by rows_processed / nvl(executions,1)
 13  /

INST_ID CHILD_NUMBER PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELASPSED_BY_EXEC FETCHES_BY_EXEC ROWS_BY_EXEC
------- ------------ --------------- ------------------------------ ---------- ---------------- --------------- ------------
      2            2       307394549 SQL_PLAN_6d9bz8qqhnxu44584d26d          8                8               2           12
      1            2       307394549 SQL_PLAN_6d9bz8qqhnxu44584d26d         45                6               3           24
      2            1       307394549 SQL_PLAN_6d9bz8qqhnxu44584d26d         46                9               3           25
      1            1       307394549 SQL_PLAN_6d9bz8qqhnxu44584d26d        375               11               4           37
      2            3       307394549 SQL_PLAN_6d9bz8qqhnxu44584d26d          1              146               8           71 -- замедление выполнения при росте числа выбираемых строк

— кроме того, появляется некая видимость логики в соотношение ELASPSED_BY_EXEC / ROWS_BY_EXEC, которое становится пропорциональным и легко объяснимым :)

Однако проблема выбора оптимального плана для конкретного набора параметров обзора / запроса остаётся принципиально неразрешимой со стороны CBO, имхо

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

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

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