Oracle mechanics

10.06.2013

SQL Plan Management в практических приложениях-2

По словам очевидцев, почти безобидный запрос периодически выполняется дольше таймаута приложения (магическим образом установленным ровно в 55 секунд):

11.2.0.3.@ SQL> var st char(9)
SQL> var en char(9)
SQL> exec :st := '23-MAY-13'; :en := '23-MAY-13'

PL/SQL procedure successfully completed.

SQL> select place_id, page_id, dt,
            sum(hits) hits,
            sum(partner_bonus_wo_nds) sum1,
            sum(partner_bonus) sum2,
            max(c_id) c_id
            from v_distr
            where dt >=:st and dt <= :en  -- dt типа DATE
            group by dt, place_id, page_id
/

...
140 rows selected.

Elapsed: 00:00:55.14

Статистика DBA_HIST_SQLSTAT подтверждает присутствие нестабильности и в плане, и, что существенно, в среднем времени выполнения ELA_PER_EXEC (us):

BEGIN_INTERVAL_TIME EXECS SQL_ID              PLAN       COST PARSE_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC ROWS_PER_EXEC IOWAITS_PER_EXEC
------------------- ----- ------------- ---------- ---------- -------------- ------------ ------------ ------------- ----------------
21-MAY-13 09.30.32.     2 g1jratsw6ujcz  530064972        377              1      5324814      3951900           138          1282659
21-MAY-13 08.00.22.     1 g1jratsw6ujcz 3720047673        366              1     47956141     34383773           131                0
21-MAY-13 08.00.22.     1 g1jratsw6ujcz 4175008718        375              1     41862114     37575287           142                0

И в очередной раз, несмотря на очевидное присутствие в запросе связанных переменных, V$SQL_SHARED_CURSOR указывает cardinality feedback (CF), как причину проблем:

 SQL> @shared_cu_sum g1jratsw6ujcz

INST   COUNT(*) IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE USE_FEEDBACK_STATS REASON1
---- ---------- ----------- ----------------- ------------- ------------ ------------------ ------------------------------------------------------------
   2         59 N           Y                 N             N            Y                  Optimizer mismatch(13)  |
   1         31 N           Y                 N             N            Y                  Optimizer mismatch(13)  |
   2         25 N           Y                 N             Y            N                  Top Level RPI Cursor(0)  |
   1         16 N           Y                 N             Y            N                  Rolling Invalidate Window Exceeded(2)  |  already_processed
   1          7 N           N                 N             N            Y                  Optimizer mismatch(13)  |
   1          1 N           Y                 N             Y            N

Кроме вносимой неопределённости с планами и таймаутами применение к запросу прогрессивной технологии CF добавляет ко времени выполнения ~5% CPU time:

SQL> select session_state, in_parse, in_hard_parse, in_sql_execution, in_bind, in_cursor_close, count(*)
  2  from gv$active_session_history where sql_id = 'g1jratsw6ujcz'
  3  group by session_state, in_parse, in_hard_parse, in_sql_execution, in_bind, in_cursor_close
  4  order by count(*) desc
  5  /

SESSION_STATE IN_PARSE IN_HARD_PARSE IN_SQL_EXECUTION IN_BIND IN_CURSOR_CLOSE   COUNT(*)
------------- -------- ------------- ---------------- ------- --------------- ----------
ON CPU        N        N             Y                N       N                      935
ON CPU        Y        Y             Y                N       N                       47 -- in_hard_parse
WAITING       N        N             Y                N       N                        9

— что ожидаемо в сответствии с Query Consistently Hard Parses More than Once Initially then Soft Parses [ID 1555541.1]

Для стабилизации плана я не использовал OUTLINES во-первых, из-за невозможности применить на уровне конкретного запроса без выставления переменной USE_STORED_OUTLINES триггерами на уровне сессии / системы, что негибко, к тому же Oracle считает OUTLINES устаревшей технологией, всячески стимулируя миграцию на SPM

Хотя на практике STORED OUTLINE в подобных случаях может быть не тривиально, но вполне эффективно ;)

Пробую зафиксировать план компонентами Oracle 11g SPM: рекомендованным Baseline и пока официально недокументированным SQL Patch — т.е. решить проблему формально, не касаясь приложения

Для удобства [пере]создания Baseline из кэшированного курсора подготовил процедуру CREATE_BSLINE.SQL

Итак, создаю Baseline для какого-то достаточно быстрого плана:

SQL> @CREATE_BSLINE g1jratsw6ujcz 791353497 "Halty''s task"

Baseline SQL_1b55d727681aae5f SQL_PLAN_1qpfr4xn1pbkzf2f27327 was [re]created
for SQL_ID=g1jratsw6ujcz, SQL_PLAN_HASH=791353497

Через некоторое время оказывается, что cardinality feedback «пересилил» Baseline, и стабильности так просто достичь не удастся:

SQL> @shared_cu g1jratsw6ujcz

EXECS PLAN_HASH_VALUE CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON1                SQL_PLAN_BASELINE
----- --------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- ---------------------- ------------------------------
    1       791353497     0 Y          N          N          Y                  N                N                Optimizer mismatch(13) SQL_PLAN_1qpfr4xn1pbkzf2f27327
    1       791353497     1 Y          N          N          Y                  N                N                Optimizer mismatch(13) SQL_PLAN_1qpfr4xn1pbkzf2f27327
    1       791353497     2 Y          N          N          Y                  N                N                Optimizer mismatch(13) SQL_PLAN_1qpfr4xn1pbkzf2f27327
    1      3232410561     3 Y          N          N          Y                  N                N                Optimizer mismatch(13)
    1      1158076004     4 Y          N          N          Y                  N                N                Optimizer mismatch(13)
    1      2608752593     5 Y          N          N          Y                  N                N                Optimizer mismatch(13)
    1      2578354639     6 Y          N          N          Y                  N                N                Optimizer mismatch(13)
   18      3749625992     7 Y          N          Y          N                  N                N                Optimizer mismatch(13)

В трейсе 10053 интересно заметить, как оптимизатор честно пытается воспроизвести найденный в SPM Baseline, в 2 раунда, однако терпит поражение:

[oracle@db1wepp trace]$ grep SPM inst1_ora_19825.trc
SPM: statement found in SMB
SPM: fixed planId''s of plan baseline are: 4075975463
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1969716976294735455
SPM: plan reproducibility round 1 (plan outline + session OFE)
...
SPM: failed to reproduce the plan using the following info:
...
SPM: plan reproducibility round 2 (hinted OFE only)
...
SPM: failed to reproduce the plan using the following info:
...
SPM: couldn''t reproduce any enabled+accepted plan so using the cost-based plan, planId = 2211978884 -- fiasco

Попутно в документации Cardinality Feedback — Frequently Asked Questions [ID 1344937.1] можно найти упоминание о влиянии extended statistics на использование CF:

At present cardinality feedback monitoring may be enabled in the following cases:

  • Tables with no statistics where dynamic sampling is not used

  • Multiple conjunctive or disjunctive filter predicates on a table and no extended statistic

  • Predicates containing complex operators that the optimizer cannot accurately compute selectivity estimates for

— однако по моим наблюдениям сам факт существование расширенной статистики на одной из таблиц запроса никак не влияет на функционирование CF, а добавление полного рекомендованного Oracle для этого запроса комплекта extended statistics может повлиять (и повлияет!) на другие запросы

Следующим тестируемым компонентом SPM будет SQL Patch с подсказкой, отключающей CF:

SQL> begin
  2    for reco in (select sql_fulltext from v$sqlarea where sql_id = 'g1jratsw6ujcz')
  3      loop
  4      dbms_sqldiag_internal.i_create_patch(
  5        sql_text => reco.sql_fulltext,
  6        hint_text => 'opt_param(''_optimizer_use_feedback'' ''false'')',
  7        name => 'NO_CF_SQL_Patch');
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed

Через некоторое время можно заметить, что несмотря на то, что патч применяется и успешно отключает CF (USE_FEEDBACK_STATS=N) он не может (и не должен) обеспечить стабильности плана выполнения:

SQL> @shared_cu g1jratsw6ujcz

INST    EXECS ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON1                                                                          SQL_PLAN_BASELINE              SQL_PATCH
---- -------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- -------------------------------------------------------------------------------- ------------------------------ ---------------
   1       30     22284470      4037137697            357     0 Y          N          Y          N                  N                N                Rolling Invalidate Window Exceeded(3)  |                                                                        NO_CF_SQL_Patch
   1       15      4264963      3232410561            374     1 Y          N          Y          N                  N                N                Optimizer mismatch(13)  |                                                                                       NO_CF_SQL_Patch
   2       31       752517      3232410561            371     0 Y          N          Y          N                  N                N                Rolling Invalidate Window Exceeded(2)  |  already_processed                                                     NO_CF_SQL_Patch
   2        7      4484411      3232410561            374     1 Y          N          Y          N                  N                N                Rolling Invalidate Window Exceeded(3)  |                                                                        NO_CF_SQL_Patch
   2       17     22700622      4037137697            356     2 Y          N          Y          N                  N                N                Optimizer mismatch(13)  |                                                                                       NO_CF_SQL_Patch

Ок, для стабильности можно создать Baseline «поверх» применяемого SQL Patch:

SQL> @CREATE_BSLINE g1jratsw6ujcz 3232410561 "Halty''s task"

Baseline SQL_1b55d727681aae5f SQL_PLAN_1qpfr4xn1pbkz784063b7 was [re]created
for SQL_ID=g1jratsw6ujcz, SQL_PLAN_HASH=3232410561

PL/SQL procedure successfully completed

SQL> -- проверим содержимое Baseline:
SQL> @bl_hints SQL_PLAN_1qpfr4xn1pbkz784063b7

OUTLINE_HINTS
--------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_use_feedback' 'false') -- подсказка из SQL Patch успешно загрузилась в Baseline
...

И первое время эта конструкция работала замечательно:

SQL> @shared_cu g1jratsw6ujcz

INST    EXECS ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON1                               SQL_PLAN_BASELINE              SQL_PATCH
---- -------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- ------------------------------------- ------------------------------ ---------------
   1       36       933579      3232410561            373     0 Y          N          Y          N                  N                N                Rolling Invalidate Window Exceeded(3) SQL_PLAN_1qpfr4xn1pbkz784063b7 NO_CF_SQL_Patch
   2       45       423751      3232410561            373     0 Y          N          Y          N                  N                N                Rolling Invalidate Window Exceeded(3) SQL_PLAN_1qpfr4xn1pbkz784063b7 NO_CF_SQL_Patch

, но через непродолжительное время созданный Baseline был отвергнут — DBA_SQL_PLAN_BASELINES.REPRODUCED = NO, на этот раз по причине Bind-Aware Cursor Sharing (BASC), несмотря на то, что до этого момента BASC для запроса не применялась ни разу — во всех предыдущих примерах — V$SQL.BIND_AWARE=N:

SQL> @shared_cu g1jratsw6ujcz

INST    EXECS ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON1                                                     SQL_PLAN_BASELINE SQL_PATCH
---- -------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- ----------------------------------------------------------- ----------------- ---------------
   1      222     15597635      4037137697            373     0 Y          N          Y          N                  N                N                Rolling Invalidate Window Exceeded(2)  |  already_processed                   NO_CF_SQL_Patch
   1      120     10847098       585681291            375     1 Y          N          N          N                  Y                N                Bind mismatch(25)  |  extended_cursor_sharing                                 NO_CF_SQL_Patch
   1        1     86901958       585681291            375     2 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   1        5     21468194       585681291            375     3 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   1        1     30608376       585681291            375     4 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   1        4     36077225       585681291            375     5 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   1        1     36968758       585681291            359     6 Y          Y          Y          N                  N                Y                Optimizer mismatch(13)  |                                                     NO_CF_SQL_Patch
   2      218     14373946      4037137697            356     0 Y          N          Y          N                  N                N                Rolling Invalidate Window Exceeded(2)  |  already_processed                   NO_CF_SQL_Patch
   2       18     12167367       585681291            375     1 Y          N          N          N                  Y                N                Bind mismatch(25)  |  extended_cursor_sharing                                 NO_CF_SQL_Patch
   2        3     27328684       585681291            359     2 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   2        4     20122736       585681291            375     3 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   2        5     25314815       585681291            375     4 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   2        3     20731730       585681291            375     5 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   2        2     26414938       585681291            375     6 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   2        2     26965639       585681291            375     7 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   2        1     38558427       585681291            375     9 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   2        1     27872183       585681291            375    10 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   2        1     36991876       585681291            375    11 Y          Y          N          N                  N                Y                Bind mismatch(33)  |                                                          NO_CF_SQL_Patch
   2       31     19606512       585681291            375    12 Y          Y          Y          N                  N                Y                Optimizer mismatch(13)  |                                                     NO_CF_SQL_Patch

В этом месте я попробовал усились SQL Patch подсказкой NO_BIND_AWARE и переделать Baseleine:

SQL> exec dbms_sqldiag.drop_sql_patch('NO_CF_SQL_Patch');

PL/SQL procedure successfully completed

SQL> begin
  2    for reco in (select sql_fulltext from v$sqlarea where sql_id = 'g1jratsw6ujcz')
  3      loop
  4      dbms_sqldiag_internal.i_create_patch(
  5        sql_text => reco.sql_fulltext,
  6        hint_text => 'opt_param(''_optimizer_use_feedback'' ''false'') no_bind_aware',
  7        name => 'NO_CF_N_BACS_SQL_Patch');
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed

SQL> @CREATE_BSLINE g1jratsw6ujcz 2375777697 "Halty''s task"

Baseline SQL_1b55d727681aae5f SQL_PLAN_1qpfr4xn1pbkz02963db6 was [re]created
for SQL_ID=g1jratsw6ujcz, SQL_PLAN_HASH=2375777697

— однако и эта конструкция не смогла обеспечить стабильности плана, опять же по причине мощнейшего CARDINALITY FEEDBACK

Припоминая, что подобное нестабильное поведение уже встречалось — Использование SPM в практических приложениях  — по причине использования в запросах параметризованных обзоров (parametrized view),
стоит попробовать определить причину нестабильности и в этом случае с помощью стандартной статистики выполнения запроса:

SQL> alter session set statistics_level=all;

Session altered.

SQL> var st char(9)
SQL> var en char(9)
SQL> exec :st := '23-MAY-13'; :en := '23-MAY-13'

PL/SQL procedure successfully completed.

SQL> select place_id, page_id, dt, sum(hits) hits,
  2         sum(partner_bonus_wo_nds) sum1,
  3         sum(partner_bonus) sum2,
  4         max(c_id) c_id
  5         from v_distr
  6         where dt >=:st and dt <= :en  
  7         group by dt, place_id, page_id  
  8  /

140 rows selected. 

Elapsed: 00:00:54.32  
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','','all iostats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
...
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |      1 |        |       |   380 (100)|          |    140 |00:00:52.66 |    1016K|   2430 |
|   1 |  HASH GROUP BY                 |                       |      1 |      1 |   165 |   380   (3)| 00:00:05 |    140 |00:00:52.66 |    1016K|   2430 |
|   2 |   NESTED LOOPS                 |                       |      1 |        |       |            |          |    179 |00:00:52.66 |    1016K|   2430 |
|   3 |    NESTED LOOPS                |                       |      1 |      1 |   165 |   379   (2)| 00:00:05 |     34M|00:00:27.17 |     542K|   1316 |
|   4 |     NESTED LOOPS               |                       |      1 |      1 |   143 |   343   (3)| 00:00:04 |    364 |00:00:00.26 |   63049 |      0 |
...
|*114 |     INDEX RANGE SCAN           | IDX_PARTNER_BUFFER_DT |    364 |      1 |       |    35   (0)| 00:00:01 |     34M|00:00:21.18 |     479K|   1316 |
|*115 |    TABLE ACCESS BY INDEX ROWID | T_PARTNER_BUFFER      |     34M|      1 |    22 |    36   (0)| 00:00:01 |    179 |00:00:17.03 |     474K|   1114 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

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

...
 114 - access("PCB"."DT">=:ST AND "PCB"."DT"<=:EN) ...

— из которой легко заметить, что основная часть времени тратится на доступ по индексу IDX_PARTNER_BUFFER_DT (по полю DT), что подтверждает предикат access(«PCB».»DT»>=:ST AND «PCB».»DT»<=:EN). Учитывая одинаковые значения связанных переменных :st и :en, за промежуток в одну секунду оптимизатор рассчитывает получить 1 строку, разумно используя индекс по дате, а реально получает ~10,000, что обусловлено структурой хранимых данных — в поле DT типа DATE хранятся интегрированные данные на начало каждых суток — фатически поле используется только для хранения даты без времени:

 SQL> SELECT (DT - LAG(DT) OVER (ORDER BY DT)) as date_interval, ROW_COUNT
  2    FROM (select dt, count(*) as ROW_COUNT
  3            from T_PARTNER_BUFFER
  4           group by dt)
  5    order by DT
  6  /

DATE_INTERVAL  ROW_COUNT
------------- ----------
                   24194
            1      25105
            1      24600
            1      23710
            1      22337
            1      22036
            1      21966
            1      22257
            1      22436
            1      22732
            1      25379
            1      26246
            1      26103
            1      25990
            1      25676
...

Учитывая, общее кол-во строк в таблице:

SQL> select num_rows from dba_tables where table_name = 'T_PARTNER_BUFFER';

  NUM_ROWS
----------
  39489084

, кол-во неповторяющихся значений столбца DT:

SQL> select num_distinct, num_nulls, to_char(density) from dba_tab_col_statistics where table_name = 'T_PARTNER_BUFFER' and column_name = 'DT';

NUM_DISTINCT  NUM_NULLS DENSITY
------------ ---------- -------
       13623          0 .000073

и относительную равномерность распределения данных по датам, Oracle просто негде получить / хранить информацию о дискретном распределении такого кол-ва значений, и даже улучшения в гистограммах Oracle 12c (2,048 buckets, Top Frequency & Hybrid histograms) не помогут

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

Для улучшения ситуации логично / достаточно просто изменить диапазон связанных при выполнении запроса:

SQL> alter session set nls_date_format='DD-MON-YY hh24:mi:ss';

Session altered.

SQL> var st char(18)
SQL> var en char(18)
SQL> exec :st := '23-MAY-13 00:00:00'; :en := '23-MAY-13 23:59:59'

PL/SQL procedure successfully completed.

SQL> select place_id, page_id, dt, sum(hits) hits,
...
  8  /

140 rows selected.

Elapsed: 00:00:01.61

SQL> /
SQL> /
...
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
...
Plan hash value: 1866388577

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------
...
|*116 |     INDEX RANGE SCAN                                     | T_PCB_IDX        |    55 |       |     1   (0)| 00:00:01 |
|*117 |    TABLE ACCESS BY INDEX ROWID                           | T_PARTNER_BUFFER |     1 |    22 |    38   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
...
 116 - access("PCB"."PLACE_ID"="P"."SEARCH_ID" AND "PCB"."PAGE_ID"=100005)
...
Note
-----
   - cardinality feedback used for this statement

SQL> @shared_cu 3upkv6bfy1b0w

EXECS ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST  CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  R REASON1                SQL_PLAN_BASELINE SQL_PATCH
----- ------------ --------------- -------------- ------ ---------- ---------- ---------- ------------------ ---------------- ---------------- - ---------------------- ----------------- ---------
    1      1525497      2319185598            374      0 N          N       N             Y                  N                N                N Optimizer mismatch(13)
    7       311839      1866388577            540      1 N          N       Y             N                  N                N                N

— в этом случае существующей статистики достаточно для того чтобы не использовать индекс по дате DT. Cardinality Feedback срабатывет, но предсказуемо — только один раз, выбирая дествительно лучший план со ср.временем выполнения 311 мс против 1,5 сек.

Другим вариантом решения средствами SPM, вообще не прикасаясь к коду приложения будет создание SQL Patch с подсказкой, ультимативно запрещающей использование индекса по дате — что для этого конкретного запроса с учётом «особенностей» хранения данных вполне оправдано:

SQL> begin
  2    for reco in (select sql_fulltext from v$sqlarea where sql_id = 'g1jratsw6ujcz')
  3      loop
  4      dbms_sqldiag_internal.i_create_patch(
  5        sql_text => reco.sql_fulltext,
  6        hint_text => 'no_index(V_DISTR.PCB IDX_PARTNER_DT) no_bind_aware opt_param(''_OPTIMIZER_USE_FEEDBACK'' ''FALSE'')',
  7        name => 'NO_IDX_BACS_CF_SQL_Patch');
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed

— что даёт требуемый и предсказуемый результат: удовлетворительное ср.время выполнения ~1 сек. В Patch добавлено отключение на уровне запроса CF & BACS  — на всякий случай, на воду подуть:

SQL> @shared_cu g1jratsw6ujcz

EXECS LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL_INVALID_MISMATCH REASON1                                                     SQL_PATCH
----- ---------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- --------------------- ----------------------------------------------------------- ------------------------
   15 02.06.2013 9:10:       946991       717535673            381     0 N          N          Y          N                  N                N                N                     Rolling Invalidate Window Exceeded(2)  |  already_processed NO_IDX_BACS_CF_SQL_Patch
    5 02.06.2013 11:10      1855070       717535673            380     1 N          N          Y          N                  N                N                Y                     Rolling Invalidate Window Exceeded(2)  |  already_processed NO_IDX_BACS_CF_SQL_Patch
   10 02.06.2013 16:40       941687       717535673            383     2 N          N          Y          N                  N                N                Y                     Rolling Invalidate Window Exceeded(2)  |  already_processed NO_IDX_BACS_CF_SQL_Patch
    3 02.06.2013 19:10      2787140       717535673            383     3 N          N          Y          N                  N                N                Y                     Rolling Invalidate Window Exceeded(2)  |  already_processed NO_IDX_BACS_CF_SQL_Patch
   20 03.06.2013 3:10:       756806       717535673            386     4 N          N          Y          N                  N                N                Y                     Rolling Invalidate Window Exceeded(2)  |  already_processed NO_IDX_BACS_CF_SQL_Patch
   81 04.06.2013 4:10:      1177688       717535673            380     5 N          N          Y          N                  N                N                Y                     Rolling Invalidate Window Exceeded(3)  |                    NO_IDX_BACS_CF_SQL_Patch
   16 04.06.2013 12:40       985332       717535673            383     6 N          N          Y          N                  N                N                Y                     Bind mismatch(33)  |                                        NO_IDX_BACS_CF_SQL_Patch

Можно заключить, что технологии Cardinality Feedback / Bind-Aware Cursor Sharing могут существенно и достаточно причудливо влиять на стабильность планов, в основном, в случаях «нестандартных» решений при разработке приложений.

Либо использование для передачи значений переменных параметризованные обзоры — что крайне не рекомендуется (и, видимо, не обрабатывается должным образом) Oracle.

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

2 комментария »

  1. Игорь, а почему при opt_param(»_optimizer_use_feedback» »false») no_bind_aware все равно применяется CF?

    комментарий от Аноним — 12.06.2013 @ 13:29 | Ответить

    • Параметр ”_optimizer_use_feedback”=”false” менялся не на уровне сессии (где такое изменение однозначно отключает CF), и даже не на уровне запроса в виде прямой подсказки, а через SPM Patch, который применяется не на первом этапе построения плана и, вероятно, является последним / достаточно слабым фактром влияния на применение CF. Своеобразная иерархия способов изменения параметра оптимизатора получается, как я понимаю
      В то же время стандартная пользовательская подсказка метода доступа NO_INDEX безотказно работает из SQL Patch, для чего, видимо SPM и предназаначен в первую очередь

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


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