Oracle mechanics

25.02.2012

Сложный запрос, использование Baseline и Bind-Aware Cursor Sharing

Составной запрос "матрёшечного" типаПланы выполнения любых запросов, например, сложно-составных «матрёшечного» типа, т.е. содержащих множественно-вложенные view, могут и часто должны существенно меняться после обновления 10.2 -> 11.2, что естественно и «баян». Интересно попробовать определить причины и исключить изменения в худшую сторону

Далее приводится интересный на мой взгляд случай диагностики таких изменений без использования трассировки оптимизатора, пример модификации плана проблемного  с использованием рекомендуемой Oracle технологии SQL Plan Management (SPM), и решение практических проблем, возникающих при использовании SPM baseline под влиянием другой передовой технологии Oracle 11g Bind-Aware Cursor Sharing (BASC)

Итак, «всё пропало — запрос медленно работает» => запрос, раньше выполнявшийся за секунды, запущен час назад и продолжает выполняеться :(

Поскольку запрос всё ещё выполняется, можно включить обычный SQL trace:

SQL> alter system set max_dump_file_size=unlimited;

System altered.
SQL> begin sys.dbms_support.start_trace_in_session(20, 38727, waits=>TRUE, binds=>TRUE ); end;

, который показывает бесконечную череду одноблочных чтений, выраженных ожиданиями read by other session, с указанием читаемого объекта бд obj#=35235, с незначительными вкраплениями сопутствующих кластерных ожиданий gc buffer busy acquire плюс соперничество за место в буферном кэше — latch: cache buffers chains:

WAIT #140540527344968: nam='gc buffer busy acquire' ela= 862 file#=401 block#=3831600 class#=1 obj#=35235 tim=1328265529045005
WAIT #140540527344968: nam='read by other session' ela= 609 file#=401 block#=3831601 class#=1 obj#=35235 tim=1328265529045705
WAIT #140540527344968: nam='read by other session' ela= 772 file#=401 block#=3831602 class#=1 obj#=35235 tim=1328265529046562
WAIT #140540527344968: nam='read by other session' ela= 7 file#=401 block#=3831603 class#=1 obj#=35235 tim=1328265529046662
WAIT #140540527344968: nam='read by other session' ela= 767 file#=401 block#=3831603 class#=1 obj#=35235 tim=1328265529047461
WAIT #140540527344968: nam='read by other session' ela= 6 file#=401 block#=3831604 class#=1 obj#=35235 tim=1328265529047559
WAIT #140540527344968: nam='read by other session' ela= 8301 file#=401 block#=3831604 class#=1 obj#=35235 tim=1328265529055892
WAIT #140540527344968: nam='read by other session' ela= 6 file#=401 block#=3831605 class#=1 obj#=35235 tim=1328265529055990
WAIT #140540527344968: nam='read by other session' ela= 1051 file#=401 block#=3831605 class#=1 obj#=35235 tim=1328265529057071
WAIT #140540527344968: nam='read by other session' ela= 703 file#=401 block#=3831606 class#=1 obj#=35235 tim=1328265529057862
WAIT #140540527344968: nam='latch: cache buffers chains' ela= 42 address=77179016032 number=155 tries=0 obj#=35235 tim=1328265529057973

Что подтверждается последующей обработкой всего трейс файла:

$ cat INST1_ora_10478.trc | wc -l
2225678                                                    -- всего строк в трейсе сессии ~ 2,2 млн

$ grep 'WAIT \#140540527344968' INST1_ora_10478.trc | wc -l
1932393                                                    -- из них 1,9 млн относятся к "плохому" запросу

$ grep 'read by other session' INST1_ora_10478.trc | wc -l
1678046                                                    -- почти 1,7 млн в ожидании "read by other session"

$ grep 'read' INST1_ora_10478.trc | wc -l
1911851                                                    -- 1,9 млн - чтения разного типа

$ grep 'obj\#\=35235' INST1_ora_10478.trc | wc -l
1906149                                                    -- 1,9 млн - читают obj#=35235

— один объект бд (таблица E_LINES с DATA_OBJECT_ID=35235) читается много и в одноблочном режиме

SQL trace даёт хорошие оценки количества ожиданий, для анализа запроса и плана выполнения можно использовать Active Session History, данные которой по окончании запроса совсем нелишне сохранить:

11.2.0.2.@SQL> create table ash_20_38727 tablespace users
 2   as
 3   select * from v$active_session_history ash
 4   where ash.session_id = 20 and session_serial# = 38727;

— как справедливо отметил Олег Коротков в докладе на Питерском семинаре RuOUG 2012 — данные в буфере ASH (V$ACTIVE_SESSION_HISTORY) значительно полнее, чем в исторической таблице DBA_HIST_ACTIVE_SESS_HISTORY:

11.2.0.2.@SQL> select sql_exec_start,
2         min(sample_time),
3         max(sample_time),
4         sql_id,
5         sql_plan_hash_value,
6         sql_plan_operation,
7         sql_plan_options,
8         sql_plan_line_id,
9         current_obj#,
10         count(*)
11    from sys.ash_20_38727
12   where sql_id = 'a7xju9dz0abdx' and SQL_EXEC_START = to_date('03.02.2012 11:34:07','dd.mm.yyyy hh24:mi:ss')
13   GROUP BY GROUPING SETS((sql_exec_start, sql_id, sql_plan_hash_value),
14                          (sql_exec_start, sql_id, sql_plan_hash_value, sql_plan_operation, sql_plan_options, sql_plan_line_id, current_obj#))
15  having count(*) > 10
16   order by count(*) desc
17  /

SQL_EXEC_START SQL_ID        SQL_PLAN_HASH_VALUE SQL_PLAN_OPERATION  SQL_PLAN_OPTIONS  SQL_PLAN_LINE_ID CURRENT_OBJ#   COUNT(*)
-------------- ------------- ------------------- ------------------- ----------------- ---------------- ------------ ----------
03.02.2012 11: a7xju9dz0abdx          2145566179                                                                           9889
03.02.2012 11: a7xju9dz0abdx          2145566179 TABLE ACCESS        BY INDEX ROWID                  42        35235       9510
03.02.2012 11: a7xju9dz0abdx          2145566179 INDEX               RANGE SCAN                      41        35240        250

11.2.0.2.@SQL> select sql_exec_start,
2         min(sample_time),
3         max(sample_time),
4         sql_id,
5         sql_plan_hash_value,
6         sql_plan_operation,
7         sql_plan_options,
8         sql_plan_line_id,
9         current_obj#,
10         count(*)
11    from dba_hist_active_sess_history
12   where sql_id = 'a7xju9dz0abdx' and SQL_EXEC_START = to_date('03.02.2012 11:34:07','dd.mm.yyyy hh24:mi:ss')
13   GROUP BY GROUPING SETS((sql_exec_start, sql_id, sql_plan_hash_value),
14                          (sql_exec_start, sql_id, sql_plan_hash_value, sql_plan_operation, sql_plan_options, sql_plan_line_id, current_obj#))
15  having count(*) > 10
16   order by count(*) desc
17  /

SQL_EXEC_START SQL_ID        SQL_PLAN_HASH_VALUE SQL_PLAN_OPERATION SQL_PLAN_OPTIONS  SQL_PLAN_LINE_ID CURRENT_OBJ#   COUNT(*)
-------------- ------------- ------------------- ------------------ ----------------- ---------------- ------------ ----------
03.02.2012 11: a7xju9dz0abdx          2145566179                                                                           989
03.02.2012 11: a7xju9dz0abdx          2145566179 TABLE ACCESS       BY INDEX ROWID                  42        35235        958
03.02.2012 11: a7xju9dz0abdx          2145566179 INDEX              RANGE SCAN                      41        35240         19

— в истории сохранилась лишь 10-я часть данных ASH. Тем не менее, этого достаточно для точного определения «проблемного» места в плане выполнения — SQL_PLAN_LINE_ID 42,41:

11.2.0.2.@SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('a7xju9dz0abdx','2145566179', 2598577434,'all allstats advanced last'));

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

SQL_ID a7xju9dz0abdx
--------------------

Plan hash value: 2145566179

---------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                  | E-Rows | Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                       |        |   442K(100)|          |
...
|  32 |   NESTED LOOPS                   |                       |        |            |          |
|  33 |    NESTED LOOPS                  |                       |   1233 |   170K  (1)| 00:34:08 |
|  34 |     HASH JOIN                    |                       |      5 |    11  (10)| 00:00:01 |
|  35 |      TABLE ACCESS FULL           | PARAMETERS            |      3 |     3   (0)| 00:00:01 |
|  36 |      MERGE JOIN CARTESIAN        |                       |    145 |     7   (0)| 00:00:01 |
|  37 |       TABLE ACCESS BY INDEX ROWID| ORGANIZATION_INFO     |      1 |     2   (0)| 00:00:01 |
|  38 |        INDEX RANGE SCAN          | ORGANIZATION_INFO_FK2 |      1 |     1   (0)| 00:00:01 |
|  39 |       BUFFER SORT                |                       |    217 |     5   (0)| 00:00:01 |
|  40 |        TABLE ACCESS FULL         | CODE_COMBINATIONS     |    217 |     5   (0)| 00:00:01 |
|  41 |     INDEX RANGE SCAN             | E_LINES_N1            |   1741K|  4997   (1)| 00:01:00 |--*
|  42 |    TABLE ACCESS BY INDEX ROWID   | E_LINES               |    249 | 78477   (1)| 00:15:42 |--**

— как видно из сравнения SQL трейса и плана выполнения, количество строк, получаемых при сканировании индекса E_LINES_N1* = 1741K рассчитано достаточно точно, а вот предполагаемое число строк, получаемых при операции TABLE ACCESS BY INDEX ROWID из таблицы E_LINES (1233) очевидно  недооценено.

Причиной плохой оценки может быть, например, неточность в оценке оптимизатором кол-ва строк, получаемых в результате картезианского произведения — MERGE JOIN CARTESIAN — см. Недорогое картезианское произведение при _optimizer_new_join_card_computation в 11.1, или какая-то другая причина/фича/улучшение, которые непросто точно определить без трейса оптимизатора, однако в этом случае можно обойтись без этого

Как я указал вначале, проблема относится к комплексному запросу, состоящему из множества вложенных друг в друга view (как куклы в матрёшке). При этом, по отдельности, эти обзоры выполняются быстро — все они из старого, уважаемого приложения Oracle Application / OEBS — «шлифовались веками» :) И поскольку приведённый выше проблемный фрагмент плана является следствием объединения обзоров, можно либо попробовать отключить механизм simple view merging, отвечающий за MERGE обзоров — объектов бд в простых случаях (без GROUP BY, DISTINCT, etc.), либо отключить полностью все операции query transformation, включающие в том числе view merging, либо вообще вернуть поведение оптимизатора к прежней версии с помощью параметра optimizer_features_enable = ‘10.2.0.4’ на уровне запроса

Проверка предположений с актуальными значениями связанных переменных из V$SQL_MONITOR (запрос возвращает 400к+ строк):

--+ opt_param('_simple_view_merging' 'false')
Elapsed: 00:01:58.01
Statistics
----------------------------
262169  recursive calls
0  db block gets
617861  consistent gets
927  physical reads

--+ no_query_transformation
Elapsed: 00:00:12.18
Statistics
----------------------------
262168  recursive calls
0  db block gets
475400  consistent gets
0  physical reads

--+ opt_param('optimizer_features_enable' '10.2.0.4')
Elapsed: 00:02:33.80
Statistics
----------------------------
262809  recursive calls
21  db block gets
564032  consistent gets
77373  physical reads

— учитывая, что «проблемный» запрос из приложения выполнялся более 4-х часов, результат получился вполне достойный, самый быстрый результат получен при отключение всех простых трансформаций запроса подсказкой no_query_transformation — запрос действительно большой, размер текста > 300 строк, использует несколько обзоров, план содержит до 100 операций,…

Полученный «быстрый» план в интересующей части:

---------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                  | E-Rows | Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                       |        |  1970K(100)|          |
...
|  35 |   HASH JOIN                      |                       |  81410 |   727K  (1)| 02:25:35 |
|  36 |    VIEW                          | CODE_COMBINATION_VIEW |    217 |     5   (0)| 00:00:01 |
|  37 |     FILTER                       |                       |        |            |          |
|  38 |      TABLE ACCESS FULL           | CODE_COMBINATIONS     |    217 |     5   (0)| 00:00:01 |
|  39 |    NESTED LOOPS                  |                       |        |            |          |
|  40 |     NESTED LOOPS                 |                       |  83212 |   727K  (1)| 02:25:35 |
|  41 |      HASH JOIN                   |                       |    277 |    54   (2)| 00:00:01 |
|  42 |       VIEW                       | CATEGORY_VIEW         |    217 |     6   (0)| 00:00:01 |
|  43 |        FILTER                    |                       |        |            |          |
|  44 |         TABLE ACCESS FULL        | CATEGORIES_TL         |    217 |     6   (0)| 00:00:01 |
|  45 |       TABLE ACCESS FULL          | E_HEADERS             |    277 |    47   (0)| 00:00:01 |
|  46 |      INDEX RANGE SCAN            | E_LINES_U1            |  35150 |    92   (0)| 00:00:02 |
|  47 |     TABLE ACCESS BY INDEX ROWID  | E_LINES               |    300 |  2627   (1)| 00:00:32 |

отличается от медленного:

  • сохранением структуры обзоров — view merging не производится
  • при этом для доступа к таблице E_LINES используются другой индекс
  • значительно меньшими оценками количества строк — E-Rows — судя по плохому плану, достаточно точными
  • более пессимистичными оценками времени выполнения и стоимости — очевидно, неправильными

Остаётся закрепить правильный план без изменения текста запроса, для чего рекомендкется использовать передовую и достаточно гибкую технологию SQL Plan Management:

Вариант 1-й с загрузкой плана из AWR-репозитория

1a) Создание «пустого» SQLSET, например, имени проблемного запроса «SQLSET_a7xju9dz0abdx»:

begin DBMS_SQLTUNE.CREATE_SQLSET('SQLSET_a7xju9dz0abdx'); end;
/

1b) Загрузка существующего запроса с «плохим» планом из AWR в SQLSET

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => 43820,                                                                             -- начальный снапшот для поиска
end_snap =>   43832,                                                                             -- финальный снапшот
basic_filter => 'sql_id='||CHR(39)||'a7xju9dz0abdx'||CHR(39)||' and plan_hash_value=2145566179', -- условие выбора
NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('SQLSET_a7xju9dz0abdx', baseline_ref_cursor);
end;
/

1c) Проверка

11.2.0.2.@SQL> SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='SQLSET_a7xju9dz0abdx';

NAME                           OWNER                          CREATED     STATEMENT_COUNT
------------------------------ ------------------------------ ----------- ---------------
SQLSET_a7xju9dz0abdx           SYS                            07.02.2012                1

11.2.0.2.@SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('SQLSET_a7xju9dz0abdx','a7xju9dz0abdx'));

– показывает «плохой» план, с которым был создан SQLSET

1d) Загрузка «плохого» плана из созданного SQLSET в BASELINE:

declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'SQLSET_a7xju9dz0abdx',
sqlset_owner => 'SYS',
fixed => 'NO',
enabled => 'YES');
end;
/

11.2.0.2.@SQL> select * from dba_sql_plan_baselines;

SQL_HANDLE            SQL_TEXT  PLAN_NAME                      ORIGIN      VERSION    ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- --------- ------------------------------ ----------- ---------- ------- -------- ----- ---------- ---------
SQL_a4d8fb4d5cc5d728  select .. SQL_PLAN_a9q7v9pfcbpt8fe202130 MANUAL-LOAD 11.2.0.2.0 YES     YES      NO    NO         YES      

Целью шагов 1a–1d было создание BASELINE, содержащего существующий запрос с «медленным» планом из репозитория AWR
То же самое можно сделать быстрее, если проблемный запрос находится в SHARED POOL, например так

Для след.шага пользователю, выполняющему запрос может понадобиться дополнительные привилегии:

SQL> grant ADMINISTER SQL MANAGEMENT OBJECT to &username;
SQL> grant execute on dbms_sqltune_util0 to &username;

Grant succeeded.

2) Выполняем тот же запрос с «правильным планом» (откорректированным с помощью подсказок, например), после чего ассоциируем «правильный план» с имеющимся BASELINE
Пример для запроса со связанными переменными:

declare
-- Определение связанных переменных
   P_START_DATE date := to_date('08/01/2011 00:00:00','mm/dd/yyyy hh24:mi:ss');
   ...
-- Тип для результатов проблемного запроса
   TYPE r_type IS RECORD (
   subkonto VARCHAR2 (4000),
      ...
   party_id number);
   TYPE tr_type IS TABLE OF r_type;
   tr_type_table tr_type;
-- Прочее
   v_sqltext VARCHAR2 (32000)  := 'select/*+ no_query_transformation*/ ...'; -- Запрос с исправленным планом
   v_sql_id VARCHAR2 (13);
   v_sql_plan_hash NUMBER;
   v_sql_short_test VARCHAR2 (1000) := substr(v_sqltext, 1, 1000);

begin

-- Имитация инициализации приложения
   dbms_application_info.set_module(&module,&action);
   ...

-- Выполнение проблемного запроса с "животворящим" хинтом
   EXECUTE IMMEDIATE v_sqltext
      BULK COLLECT INTO tr_type_table
      using P_GROUP_DOG_YES_NO, P_START_DATE, P_START_DATE, P_END_DATE, P_START_DATE, P_END_DATE, P_END_DATE, P_START_DATE...;

-- Получение SQL_ID, PLAN_HASH_VALUE оптимизированного запроса
   v_sql_id := sys.dbms_sqltune_util0.sqltext_to_sqlid ( v_sqltext || chr(0) );
   select plan_hash_value
      into v_sql_plan_hash
   from v$sql where sql_id = v_sql_id and rownum <= 1;

   DBMS_OUTPUT.put_line('Hinted SQL_ID = ''' || v_sql_id || '''; PLAN_HASH_VALUE = ' || v_sql_plan_hash || ' returned ' || tr_type_table.count || ' rows');

-- Связывание "правильного" плана (SQL_ID, PLAN_HASH_VALUE) с имеющимся BASELINE'ом по SQL_HANDLE
   v_sql_plan_hash := dbms_spm.load_plans_from_cursor_cache(sql_id => v_sql_id, plan_hash_value => v_sql_plan_hash, sql_handle => 'SQL_a4d8fb4d5cc5d728');
end;
/

Hinted SQL_ID = 'fptb8xrsb7pkc'; PLAN_HASH_VALUE = 2566703614 returned 437605 rows

3) (Опционально) можно удалить BASELINE с плохим планом:

declare res number;
 begin
res := DBMS_SPM.DROP_SQL_PLAN_BASELINE ('SQL_a4d8fb4d5cc5d728','SQL_PLAN_a9q7v9pfcbpt8fe202130');
 end;
/

4) (Опционально) можно навеки зафиксировать BASELINE с правильным планом:)

11.2.0.2.@SQL> declare res number;
 2 begin
 3    res := DBMS_SPM.alter_sql_plan_baseline('SQL_a4d8fb4d5cc5d728','SQL_PLAN_a9q7v9pfcbpt8c8976431','fixed','yes');
 4    res := DBMS_SPM.alter_sql_plan_baseline('SQL_a4d8fb4d5cc5d728','SQL_PLAN_a9q7v9pfcbpt8c8976431','autopurge','no');
 5 end;
 6 /

PL/SQL procedure successfully completed

После вышеперечисленных манипуляций в V$SQL можно видеть, что активные курсоры успешно используют созданный V$SQL.SQL_PLAN_BASELINE=’SQL_PLAN_a9q7v9pfcbpt8c8976431′, запрос выполняется быстро, новые технологии SPM успешно работают!

До этого момента всё было красиво по книжкам, однако через несколько дней можно обнаружить, что созданный sql plan baseline уже не используется запросом:

11.2.0.2.@SQL> select child_number,
2         optimizer_cost,
3         plan_hash_value,
4         is_bind_sensitive,
5         is_bind_aware,
6         is_shareable,
7         sql_plan_baseline,
8         round( ELAPSED_TIME / executions / 1000000, 3 ) ELAPSED_per_exec,
9         ROWS_PROCESSED / executions rows_per_exec
10    from v$sql
11   where SQL_ID = 'a7xju9dz0abdx'
12  /

CHILD_NUMBER OPTIMIZER_COST PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE SQL_PLAN_BASELINE ELAPSED_PER_EXEC ROWS_PER_EXEC
------------ -------------- --------------- ----------------- ------------- ------------ ----------------- ---------------- -------------
0                   1695908      2110721590 Y                 N             N                                        85,653             1
1                   1514937      2110721590 Y                 Y             N                                        10,956          1836
2                   1511435      2110721590 Y                 Y             Y                                         6,755             1

– запрос вновь использует не самый хороший план — при этом неважно, что запрос пока выполняется быстро — просто выбирается немного строк

В секции Notes плана появляется упоминание об использовании cardinality feedback:

11.2.0.2.@SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( 'a7xju9dz0abdx','1','all allstats advanced last'));
...
Note
-----
- cardinality feedback used for this statement

— налицо все признаки использования технологии BACS — см. Элементы технологий Bind Aware Cursor Sharing / Cardinality Feedback

Курсоры помечены как BIND AWARE и, судя по тому, что BASELINE уже не используется – влияние технология Bind-Aware Cursor Sharing оказалась сильнее, чем у sql plan baseline, несмотря на то, что в блоге Oracle Optimizer Team утверждалось обратное:

SPM and adaptive cursor sharing

When you have a SQL plan baseline for a statement, the SPM aware optimizer will select the best accepted plan as the optimal plan. This also applies for the hard parse of a bind sensitive statement. There may be multiple accepted plans, each of which is optimal for different bind sets. With SPM and ACS enabled, the SPM aware optimizer will select the best plan for the current bind set.

Thus, if a hard parse occurs, the normal SPM plan selection algorithm is used regardless of whether a statement is bind sensitive.

Кроме того, по имеющейся информации можно заключить, что решение о применении BASC Oracle принимает не позднее, чем узнаёт, что для запроса существует зафиксированный готовый baseline, в соответствии с планом которого должен по идее выполняться запрос

Для отключения BASC можно попробовать изменить набор подсказок в запросе с «хорошим» планом, который использовался для улучшения плана baseline – использованную подсказку /*+ no_query_transformation*/ заменить|добавить на /*+ no_query_transformation no_bind_aware*/:

declare
 -- Определение связанных переменных
 ...
 -- Тип для результатов проблемного запроса
 ...
 -- Прочее
 v_sqltext VARCHAR2 (32000) := 'select/*+ no_query_transformation no_bind_aware*/ ...

Как оказалось, Oracle оценивает содержимое (набор подсказок) baseline до того, как принимает решение о применении BIND-AWARE Cursor Sharing — что достаточно разумно, т.к. в противном случае надёжно зафиксировать план с помощью SPM было бы проблематично. Соответственно, после описанного отключения BASC в baseline добавлением подсказки no_bind_aware:

LAST_ACT CHILD COST    OBJECT_STATUS  IS_BIND_SENS IS_BIND_AWARE  IS_SHAREABLE SQL_PLAN_BASELINE EXECUTIONS ELAPSED_PER_EXEC ROWS_PER_EXEC
-------- ----- ------- -------------- ------------ -------------- ------------ ----------------- ---------- ---------------- -------------
13.02.12     0 1695908 INVALID_UNAUTH Y            N              N                                       2           85,653             1
13.02.12     1 1514937 INVALID_UNAUTH Y            Y              N                                       1           10,956          1836
14.02.12     2 1909302 VALID          N            N              Y            SQL_PLAN_a9q7v9pf          1            2,397         15816
16.02.12     3 2051151 VALID          N            N              Y            SQL_PLAN_a9q7v9pf          9           63,241          4387
17.02.12     4 1906301 VALID          N            N              Y            SQL_PLAN_a9q7v9pf          2            5,171           903
17.02.12     5 1910085 VALID          N            N              Y            SQL_PLAN_a9q7v9pf         27           65,821          2049

– все активные курсоры (IS_SHAREABLE=Y) начали успешно использовать модифицированный SQL_PLAN_BASELINE, и, судя по значению полей V$SQL.IS_BIND_AWARE=N и V$SQL.IS_BIND_SENSITIVE=N — уже не являются не только Bind Aware, но и зависимыми от значений связанных переменных, что в данном случае радует — Oracle воспринял сигнал)
+ в причинах создания новых активных курсоров CHILD_NUMBER=2,3,4,5 уже нет Bind mismatch, указывавшей на использование BACS

11.2.0.2.@SQL> @v$sql_shared_cursor a7xju9dz0abdx

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- ------------------------------------------------------------
a7xju9dz0abdx      0 N                  Y                N                Bind mismatch(25)  |  extended_cursor_sharing
a7xju9dz0abdx      1 N                  N                Y                Bind mismatch(33)  |
a7xju9dz0abdx      2 N                  N                N                Top Level RPI Cursor(0)  |
a7xju9dz0abdx      3 N                  N                N                Rolling Invalidate Window Exceeded(2)  |  already_processed
a7xju9dz0abdx      4 N                  N                N                Authorization Check failed(1)  |  NLS_language_handles_mismatch
a7xju9dz0abdx      5 N                  N                N                Authorization Check failed(1)  |  NLS_language_handles_mismatch

Таким образом получилось использовать рекомендуемую технологию SPM для создания BASELINE с фиксированным «правильным» планом выполнения

P.S. Параметры всё время имели значения по умолчанию:

11.2.0.2.@SQL> @param baseline

NAME                                  VALUE  IS_DEF  IS_MOD  DSC
------------------------------------- ------ ------- ------- -----------------------------------------------------------------
optimizer_capture_sql_plan_baselines  FALSE  TRUE    FALSE   automatic capture of SQL plan baselines for repeatable statements
optimizer_use_sql_plan_baselines      TRUE   TRUE    FALSE   use of SQL plan baselines for captured sql statements

P.P.S. Поздравляю всех с прошедшим Днём Советской Армии и Военно-Морского Флота!

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

  1. Спасибо, такой вопрос:
    могут ли быть аналогичные ситуации с формально закрепленным в spb запросом, но когда периодами все равно подхватывается плохой план, при этом у запроса с плохим планом не показывается как у вас в статье
    «Note
    ——
    — cardinality feedback used for this statement» ?

    комментарий от Плюс — 05.04.2013 @ 10:20 | Ответить

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

      ...
      SPM: statement found in SMB
      ...
      SPM: failed to reproduce the plan using the following info:
      ...

      Причины создания новых не-SPM курсоров обычно можно посмотреть в v$sql_shared_cursor

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


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