Oracle mechanics

06.04.2016

Изменение параметров SPMBaseline с помощью SQL Patch

Filed under: Oracle,SQL Tuning — Игорь Усольцев @ 23:17
Tags: ,

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

SQL> @v$sqlstats fxgzfhx4fr9rv
 
INST EXECS SQL_ID              PLAN ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC UIO_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC DISK_READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC PX_PER_EXEC
---- ----- ------------- ---------- ------------ ------------ ------------- ------------- ------------ ------------ ---------------- -------------- ------------------- ------------------ --------------- ---------------------- ------------- -----------
   2     5 fxgzfhx4fr9rv 1662599281      9728601      6012200        163289         43900       107579      3759166              228           5265                   0                  0               0                      0            13           0
   1     1 fxgzfhx4fr9rv 1494936070  27639543205   3917421000     769437420    2212170898   2758482394   3583471835             1029          17400                   0             125171         1068134               16019535          1888           0
 
SQL_EXEC_ID SQL_PLAN_HASH_VALUE ACTION             MAX_TEMP_GB MAX_PGA_GB MAX_PX_USED
----------- ------------------- ------------------ ----------- ---------- -----------
   16777225           792237607 Concurrent Request           0      0,029           0
   16777226          1494936070 Concurrent Request           0      0,225           0
   16777227          1494936070 Concurrent Request      72,007       0,19           0
   16777228          1494936070 Concurrent Request      39,393      0,879           0
   16777229          1494936070 Concurrent Request      14,435      0,704           0
   16777230          1494936070 Concurrent Request       2,726      0,878           0
   16777231          1494936070 Concurrent Request       1,894      0,879           0
   33554442          1662599281 Concurrent Request           0      0,054           0
   33554443          1662599281 Concurrent Request           0       0,05           0
   33554444          1662599281 Concurrent Request           0      0,042           0
   33554445          1662599281 Concurrent Request           0      0,039           0
   33554440          3996693969 Concurrent Request       3,127       1,13           0
   33554441          3996693969 Concurrent Request       3,142      1,145           0

— при этом повышенное потребление TEMP (и DIRECT_WRITES_PER_EXEC, соотв-но) было характерно для использования плана 1494936070, и не отмечается для плана 1662599281, т.е. зависело, в основном, от выбора плана выполнения и слабо зависело от прочих исходных данных и значений bind-ов — судя по BIND_SENSE(V$SQL.IS_BIND_SENSITIVE) и BIND_AWARE(V$SQL.IS_BIND_AWARE):

SQL> @shared_cu12_noxml fxgzfhx4fr9rv
 
INST EXECS USERS_OPENING FIRST_LOAD_TIME      LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON#1
---- ----- ------------- -------------------- -------------------- ------------------- ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ---------------- ---------------- ---- ----------------------
   2     4             0 2015-10-07/11:35:56  2015-10-08/14:36:49  08.10.2015 16:16:38     11949587 VALID              1662599281           3076625770         104983     0 N          N          Y          N                  N                       42        13        2        N                N                N    Optimizer mismatch(12)
   1     1             5 2015-10-07/11:44:24  2015-10-08/13:20:28  08.10.2015 17:03:13  27792588061 VALID              1494936070            483802718         125020     0 N          N          Y          N                  N                       40        14        2        N                N                N

Если фиксировать экономичный план с помощью Baseline:

SQL> @bl_create fxgzfhx4fr9rv 1662599281 "fxgzfhx4fr9rv_huge_TEMP_BL"
 
PL/SQL procedure successfully completed
 
SQL> @spm_check4sql_id fxgzfhx4fr9rv
 
SIGNATURE             SPM_TYPE          SQL_HANDLE           PATCH_NAME                     ORIGIN         VERSION    LAST_MODIFIED       LAST_EXECUTED LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ----------------- -------------------- ------------------------------ -------------- ---------- ------------------- ------------- ------------- ------- -------- ----- ---------- ---------
  4995016026245113471 SQL Plan Baseline SQL_4551dc9c73b9267f SQL_PLAN_4anfwmjtvk9mz65ef49bd MANUAL-LOAD    12.1.0.2.0 09.10.2015 10:01:59                             YES     YES      YES   YES        NO

и просмотреть содержимое скриптом SPB12.SQL*:

SQL> @spb12 SQL_PLAN_4anfwmjtvk9mz65ef49bd
 
OUTLINE_HINTS
--------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$6590669A")
...
Notes
--------------------------------------------------------------------------------
   sql_profile:
   sql_patch:
   baseline:
   outline:
   dyn_sampling:    2
   dop:
   dop_reason:
   card_feedback:
   perf_feedback:
   adaptive_plan:
   spd_used:        14
   spd_valid:       40
   gtt_sess_stat:
   db_version:      12.1.0.2
   ...

— можно видеть, как и из вывода предыдущего SHARED_CU12_NOXML.SQL, влияние SQL Plan Directives и Adaptive Dynamic Sampling на формирование различных, в т.ч. неудачных курсоров, что было бы желательно отключить для запроса в дальнейшем, особенно учитывая факты неустойчивости Baseline, который под влияние различных Adaptive Features легко может сменить статус на REPRODUCED = NO в дальнейшем

При желании изменить в зафиксированном плане соответствующие параметры оптимизатора, не меняя план как набор операций (т.е. оставляя неизменным PLAN_HASH_VALUE), SQL Plan Baseline можно попробовать дополнить/усилить SQL Patch-ем:

SQL> @sqlpatch+
&SQL_ID:         fxgzfhx4fr9rv
&SQL_PATCH_TEXT: opt_param(''optimizer_adaptive_features'' ''false'') opt_param(''optimizer_dynamic_sampling'' 9)
&SQL_PATCH_NAME: fxgzfhx4fr9rv huge TEMP
 
PL/SQL procedure successfully completed
 
SQL_ID        NAME                     CATEGORY  CREATED               STATUS   FMATCH
------------- ------------------------ --------- --------------------- -------- ------
fxgzfhx4fr9rv fxgzfhx4fr9rv huge TEMP  DEFAULT   09.10.2015 10:05:22   ENABLED  NO
 
SQL_PATCH_HINTS
------------------------------------------------------------------------------------------
opt_param('optimizer_adaptive_features' 'false') opt_param('optimizer_dynamic_sampling' 9)

Теперь к запросу привязано 2 элемента SQL Plan Management (SPM):

SQL> @spm_check4sql_id fxgzfhx4fr9rv
 
SIGNATURE             SPM_TYPE          SQL_HANDLE                     PATCH_NAME                     ORIGIN         VERSION    LAST_MODIFIED       LAST_EXECUTED LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ----------------- ------------------------------ ------------------------------ -------------- ---------- ------------------- ------------- ------------- ------- -------- ----- ---------- ---------
  4995016026245113471 SQL Patch         SQL_4551dc9c73b9267f           fxgzfhx4fr9rv huge TEMP        MANUAL-SQLTUNE 12.1.0.2.0 09.10.2015 10:05:22                             YES     NO       NO    YES        NO
  4995016026245113471 SQL Plan Baseline SQL_4551dc9c73b9267f           SQL_PLAN_4anfwmjtvk9mz65ef49bd MANUAL-LOAD    12.1.0.2.0 09.10.2015 10:01:59                             YES     YES      YES   YES        NO

и при след.выполнении оба этих компонента могут сработать:

SQL> @shared_cu12_noxml fxgzfhx4fr9rv
 
INST EXECS USERS_OPENING FIRST_LOAD_TIME      LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON#1                SQL_PLAN_BASELINE              SQL_PATCH               OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
---- ----- ------------- -------------------- -------------------- ------------------- ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ---------------- ---------------- ---- ----------------------- ------------------------------ ----------------------- ---------------- ----------- -----------
   2     2             0 2015-10-07/11:35:56  2015-10-09/19:21:00  09.10.2015 20:13:54     73450404 VALID              1662599281           3076625770         158158     0 N          N          Y          N                  N                                           9        N                N                N    Optimizer mismatch(12)  SQL_PLAN_4anfwmjtvk9mz65ef49bd fxgzfhx4fr9rv huge TEMP                              N

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('fxgzfhx4fr9rv',0,format => '+notes'))

Plan hash value: 1662599281
...
	Note
	-----
	   - dynamic statistics used: dynamic sampling (level=2)
	   - SQL patch "fxgzfhx4fr9rv huge TEMP" used for this statement
	   - SQL plan baseline SQL_PLAN_4anfwmjtvk9mz65ef49bd used for this statement

— сгенерировав в рез-те целевой план Plan hash value: 1662599281

Однако впоследствии «нежный» механизм SQL plan baseline-а может перестать работать в таком сочетании:

SQL> @shared_cu12_noxml fxgzfhx4fr9rv
 
INST EXECS USERS_OPENING FIRST_LOAD_TIME      LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON#1               SQL_PLAN_BASELINE              SQL_PATCH               OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
---- ----- ------------- -------------------- -------------------- ------------------- ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ---------------- ---------------- ---- ---------------------- ------------------------------ ----------------------- ---------------- ----------- -----------
   2     2             0 2015-10-07/11:35:56  2015-10-09/19:21:00  09.10.2015 20:13:54     73450404 VALID              1662599281           3076625770         158158     0 N          N          Y          N                  N                                           9        N                N                N    Optimizer mismatch(12) SQL_PLAN_4anfwmjtvk9mz65ef49bd fxgzfhx4fr9rv huge TEMP                              N
   1     2             0 2015-10-07/11:44:24  2015-10-09/20:15:55  09.10.2015 20:23:14    143625367 VALID              4194055133           2453675823         175154     1 N          N          Y          N                  N                                           9        N                N                N                                                          fxgzfhx4fr9rv huge TEMP                              N

, но если успеть пересоздать Baseline из курсора с PLAN_HASH_VALUE = 1662599281, использующего совместно Baseline и SQL Patch и всё ещё находящегося в Shared Pool (что важно сделать быстро ибо в AWR курсор с PLAN_HASH_VALUE = 1662599281 уже был сохранён ранее, до применения SPM методов):

SQL> @bl_drop SQL_PLAN_4anfwmjtvk9mz65ef49bd
 
PL/SQL procedure successfully completed

SQL> -- проверка SPM элементов для запроса
SQL> @spm_check4sql_id fxgzfhx4fr9rv
 
SIGNATURE             SPM_TYPE          SQL_HANDLE                     PATCH_NAME                     ORIGIN         VERSION    LAST_MODIFIED       LAST_EXECUTED LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ----------------- ------------------------------ ------------------------------ -------------- ---------- ------------------- ------------- ------------- ------- -------- ----- ---------- ---------
  4995016026245113471 SQL Patch         SQL_4551dc9c73b9267f           fxgzfhx4fr9rv huge TEMP        MANUAL-SQLTUNE 12.1.0.2.0 09.10.2015 10:05:22                             YES     NO       NO    YES        NO
 
SQL> @bl_create fxgzfhx4fr9rv 1662599281 "fxgzfhx4fr9rv_huge_TEMP_BL"

SQL> @bl12_hints SQL_PLAN_4anfwmjtvk9mz65ef49bd
 
OUTLINE_HINTS
--------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('optimizer_dynamic_sampling' 9)               -- напрямую из SQL Patch
OPT_PARAM('_optimizer_use_feedback' 'false')            -- производные OPT_PARAM('optimizer_adaptive_features' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')             -- --//--
OPT_PARAM('_optimizer_dsdir_usage_control' 0)           -- --//--
OPT_PARAM('_optimizer_adaptive_plans' 'false')          -- --//--
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') -- --//--
OPT_PARAM('_optimizer_gather_feedback' 'false')         -- --//--
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')    -- --//--
ALL_ROWS
...

— можно увидеть, что пересозданный Baseline в отличие от первоначального(*) дополнен изменёнными SQL Patch-ем параметрами оптимизатора, что, вероятно, и было причиной недолговечности/нестабильности первоначально созданного Baseline

В рез-те содержимое Baseline получается изменить, добавив требуемые параметры оптимизатора, но оставив неизменными и PLAN_HASH_VALUE, и, как следствие, наименование Baseline-а / DBA_SQL_PLAN_BASELINES.PLAN_NAME, являющееся с большой вероятностью HASH-функцией(plan_hash_value, sql_id)

SQL patch теперь утратил своё значение и может быть удалён:

SQL> exec dbms_sqldiag.drop_sql_patch('fxgzfhx4fr9rv huge TEMP')
 
PL/SQL procedure successfully completed
 
SQL> @spm_check4sql_id fxgzfhx4fr9rv
 
SIGNATURE             SPM_TYPE          SQL_HANDLE            PATCH_NAME                     ORIGIN       VERSION    LAST_MODIFIED       LAST_EXECUTED LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ----------------- --------------------- ------------------------------ ------------ ---------- ------------------- ------------- ------------- ------- -------- ----- ---------- ---------
  4995016026245113471 SQL Plan Baseline SQL_4551dc9c73b9267f  SQL_PLAN_4anfwmjtvk9mz65ef49bd MANUAL-LOAD  12.1.0.2.0 09.10.2015 10:01:59                             YES     YES      YES   YES        NO

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

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

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