Oracle mechanics

16.12.2015

SQL Patch для решения стандартных проблем версии 12c

Filed under: Oracle,Oracle 12c,Oracle new features — Игорь Усольцев @ 22:56
Tags:

Как бы решая проблему High Version Count находим пациента:

SQL> select *
  2    from (select inst_id,
  3                 sql_id,
  4                 count(*),
  5                 sum(count(*)) over(partition by sql_id) as child_count
  6            from gv$sql
  7           group by inst_id, sql_id
  8           order by 4 desc, 1)
  9   where child_count > 200
 10  /

INST_ID SQL_ID          COUNT(*) CHILD_COUNT
------- ------------- ---------- -----------
...
      1 a40b55bnbjm5t        179         362
      2 a40b55bnbjm5t        183         362
...

Суммарный запрос по причинам нерасшаривания курсора указывает на Auto Reoptimization Mismatch(1) + Bind mismatch(25):

SQL> @shared_cu12s_noxml a40b55bnbjm5t 0
 
INST_ID PLAN_HASH_VALUE BIND_SENSE BIND_AWARE FEEDBACK_STATS OPT_STATS REOPT ADAPT BIND_EQ_FAIL ROLL REASON#1                        CURSOR_COUNT  PHV_COUNT FPHV_COUNT EXECS USERS_OPENING
------- --------------- ---------- ---------- -------------- --------- ----- ----- ------------ ---- ------------------------------- ------------ ---------- ---------- ----- -------------
      2      3280735784 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)           77          1          1    81             0
      1      3280735784 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)           68          1          1    89             0
      2      2157270316 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)           57          1          1    62             0
      1      2157270316 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)           38          1          1    51             0
      1       581405141 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)           27          1          1    46             0
      2      3504573888 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)           19          1          1    22             0
      2       581405141 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)           16          1          1    17             0
      1      3504573888 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)           15          1          1    24             0
      1        69587928 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            7          1          1    12             0
      1      2108961246 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            5          1          1     5             0
      2        69587928 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            3          1          1     3             0
      2        69587928 Y          N          N              Y         N           N            N    Bind mismatch(25)                          3          1          1    22             0
      1        69587928 Y          N          N              Y         N           N            N    Bind mismatch(25)                          3          1          1    28             0
      1      4198052751 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            3          1          1     5             0
      2        98969797 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            2          1          1     2             0
      1       982512016 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            2          1          1     3             0
      1      2186683962 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            2          1          1     3             0
      2      3504573888 Y          N          Y              N         Y     Y     N            N    Auto Reoptimization Mismatch(1)            1          1          1     1             0
      2      3361579179 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            1          1          1     1             0
      2      2108961246 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            1          1          1     1             0
      2       303721942 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            1          1          1     2             0
      2        69587928 Y          N          N              Y         N     Y     N            N    Bind mismatch(25)                          1          1          1     2             0
      1      3615919798 Y          Y          Y              N         Y           N            N    Optimizer mismatch(13)                     1          1          1     1             0
      2      4198052751 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            1          1          1     1             0
      1      3280735784 N          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            1          1          1    30             0
      1      2157270316 Y          N          N              N         N           N            N    Auto Reoptimization Mismatch(1)            1          1          1    36             0
      1      2157270316 N          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            1          1          1    15             0
      1      1803444552 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            1          1          1     2             0
      1      1340856798 Y          N          Y              N         Y           N            N    Auto Reoptimization Mismatch(1)            1          1          1     2             0
      1       581405141 Y          N          Y              N         Y     Y     N            N    Auto Reoptimization Mismatch(1)            1          1          1     1             0
      1        69587928 Y          Y          Y              N         Y           N            N    Optimizer mismatch(13)                     1          1          1     1             0
      1      3280735784 Y          Y          N              N         N           N            N    Bind mismatch(33)                          1          1          1    55             0

Статистика выполнения запроса по PHV/INSTance_id показывает зоопарк, порождённый 12c Adaptive Features (Reoptimization, Adaptive Plan), 11g Adaptive Cursor Sharing и, возможно, Automatic Dynamic Sampling:

SQL> @v$sqlstats2 a40b55bnbjm5t
 
--------------------------------------------------------------
SQL_ID=a40b55bnbjm5t Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
INST        EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT
---- ------------ -------------------- ------------------- ------------ --------------- -------------------- ------------ ---------- ---------- ---------- ----- -----
   1          174 2015-12-16/11:41:48  16.12.2015 11:41:47       223626      3280735784           1395092114         3979         70 Y          Y          Y     N
   1           41 2015-12-16/09:36:12  16.12.2015 09:36:13       143976        69587928           2737413572         4469         11 Y          Y          Y     N
   2           81 2015-12-16/11:48:23  16.12.2015 11:48:23       263647      3280735784           1395092114         3712         77 Y          N          Y     N
   2           25 2015-12-16/09:36:12  16.12.2015 09:36:12        59427        69587928           2737413572         5374          6 Y          N          Y     N
   1            1 2015-12-16/02:58:56  16.12.2015 02:59:05       796855      3615919798           1139126442         3388          1 Y          Y          Y     N
   1           46 2015-12-16/09:36:04  16.12.2015 09:36:04       333443       581405141           2536337209         3873         27 Y          N          Y     N
   1            3 2015-12-16/03:05:02  16.12.2015 03:05:05      3895281       982512016           3666806257         3038          2 Y          N          Y     N
   1            1 2015-12-15/17:19:27  15.12.2015 17:19:27       414844       581405141            410346051         3906          1 Y          N          Y     Y
   2            2 2015-12-15/07:46:25  15.12.2015 07:46:25       204110        98969797           3455202858         5530          2 Y          N          Y     N
   1           24 2015-12-16/10:09:32  16.12.2015 10:09:32       293641      3504573888           4080627704         3597         15 Y          N          Y     N
   2           22 2015-12-15/16:31:26  15.12.2015 16:31:25       259081      3504573888           4080627704         3972         19 Y          N          Y     N
   2            1 2015-12-15/09:31:09  15.12.2015 09:31:08       232077      3361579179           1179785443         2859          1 Y          N          Y     N
   1            3 2015-12-16/00:18:53  16.12.2015 00:18:52       163354      2186683962            253052925         3943          2 Y          N          Y     N
   2            1 2015-12-16/11:59:14  16.12.2015 11:59:14       296076      4198052751           3701883134         8434          1 Y          N          Y     N
   1            2 2015-12-16/00:53:35  16.12.2015 00:53:35       215555      1340856798           3408730371         4302          1 Y          N          Y     N
   2            1 2015-12-14/09:46:22  14.12.2015 09:46:22       191691      3504573888            974825204         3860          1 Y          N          Y     Y
   1          102 2015-12-16/11:42:14  16.12.2015 12:01:23       223826      2157270316           3469230004         5069         40 Y          N          Y     N
   1            2 2015-12-16/03:04:01  16.12.2015 03:04:02       614357      1803444552            368970186         3823          1 Y          N          Y     N
   2           62 2015-12-16/11:56:53  16.12.2015 11:56:53       259789      2157270316           3469230004         4832         57 Y          N          Y     N
   2            2 2015-12-15/10:34:24  15.12.2015 10:34:23       282004       303721942           3576708332         4477          1 Y          N          Y     N
   1            5 2015-12-16/11:21:27  16.12.2015 11:21:27       273020      2108961246           1018219024         5185          5 Y          N          Y     N
   2           17 2015-12-15/14:57:16  15.12.2015 14:57:15       213123       581405141           2536337209         3406         16 Y          N          Y     N
   2            2 2015-12-14/19:13:00  14.12.2015 19:29:23        49138        69587928             30109860         4577          1 Y          N          N     Y
   1            5 2015-12-16/10:09:22  16.12.2015 10:09:22       410442      4198052751           3701883134         5296          3 Y          N          Y     N
   2            1 2015-12-16/10:15:42  16.12.2015 10:15:42       213711      2108961246           1018219024         6007          1 Y          N          Y     N

К запросу никаких элементов SQL Plan Management (SPM) пока не прикручено:

SQL> @spm_check4sql_id a40b55bnbjm5t
 
no rows selected

Волшебный скрипт имени 12c, создающий для запроса специальный патч с лекарством от типичных заболеваний последней версии:)

SQL> @sqlpatch_12c a40b55bnbjm5t
 
PL/SQL procedure successfully completed
 
SIGNATURE             SPM_TYPE   SQL_HANDLE            PATCH_NAME              ORIGIN         VERSION    CREATED              ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ---------- --------------------- ----------------------- -------------- ---------- -------------------- ------- -------- ----- ---------- ---------
 12536705129238687964 SQL Patch  SQL_adfb52e48a16f0dc  NO_ADAPT_a40b55bnbjm5t  MANUAL-SQLTUNE 12.1.0.2.0 16.12.2015 12:06:54  YES     NO       NO    YES        NO
 
SQL_PATCH_HINTS
--------------------------------------------------------------------------------------------------------
opt_param('optimizer_adaptive_features' 'false') no_bind_aware OPT_PARAM('optimizer_dynamic_sampling' 0)

— создаёт SQL Patch с этими параметрами/подсказками

После тонкой очистки Shared Pool (на всех нодах):

SQL> @purge_cu a40b55bnbjm5t
 
PL/SQL procedure successfully completed

Имеем:

SQL> @v$sqlstats2 a40b55bnbjm5t
 
--------------------------------------------------------------
SQL_ID=a40b55bnbjm5t Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
INST EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE SQL_PATCH              OUTLINE_CATEGORY SQL_PROFILE
---- ----- -------------------- ------------------- ------------ --------------- -------------------- ------------ ------ ---------- ---------- ----- ----- ----------------- ---------------------- ---------------- -----------
   2     7 2015-12-16/13:00:15  16.12.2015 13:28:22        43406       982512016           3666806257         3039      1 N          N          N     N                       NO_ADAPT_a40b55bnbjm5t                  
   1    19 2015-12-16/13:11:23  16.12.2015 13:28:38        33025       982512016           3666806257         3039      1 N          N          N     N                       NO_ADAPT_a40b55bnbjm5t                  
 
SQL> @shared_cu12_noxml a40b55bnbjm5t 0
 
INST EXECS USERS_OPENING 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 REASON#1                        SQL_PATCH              IS_OBSOLETE
---- ----- ------------- -------------------- ------------------- ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ------------------------------- ---------------------- -----------
   1    19             1 2015-12-16/13:11:23  16.12.2015 13:28:38        33025 VALID               982512016           3666806257           3039     0 N          N          Y          N                  N                                                    Auto Reoptimization Mismatch(1) NO_ADAPT_a40b55bnbjm5t N
   2     7             0 2015-12-16/13:00:15  16.12.2015 13:28:22        43406 VALID               982512016           3666806257           3039     0 N          N          Y          N                  N                                                    Auto Reoptimization Mismatch(1) NO_ADAPT_a40b55bnbjm5t N

— драматическое сокращение кол-ва курсоров (CHILDS в первом запросе) + 4-х кратное уменьшение ср.времени выполнения ELA_PER_EXEC + стабильность плана выполнения

5 комментариев »

  1. Игорь, а какой смысл включать тогда Adaptive Features, чтобы потом искать пациентов и лечить их скриптом имени 12c ? Мы сразу переходим на 12.1.0.2 с отключенными адаптивными фичами и пр.

    комментарий от jimroll — 28.12.2015 @ 19:36 | Ответить

    • Очередной железный конь пришёл на смену крестьянской лошадке, в том смысле, что происходит очередная смена концепции: на смену CBO (выбор плана на основе вычисляемой стоимости, сменившей в свою очередь rule based подход) приходит динамическое управление планами в виде адаптивных фич 12c, adaptive cursor sharing 11g и прочего SQL Plan Management
      В случае вашего жёсткого OLTP отключение адаптивностей определённо оправдано, слов нет, но в целом — новую реальность неизбежно придётся изучать и осваивать, а лучше всего это делать на реальных системах, isn’t it?)

      С наступающим!

      комментарий от Игорь Усольцев — 31.12.2015 @ 13:44 | Ответить

      • Спасибо за идеи!

        И тебя с наступившим!

        комментарий от jimroll — 12.01.2016 @ 12:11 | Ответить

  2. Игорь, доброго времени суток, поделитесь пожалуйста скриптами из статьи :)

    комментарий от Anton Bushmelev — 20.10.2016 @ 02:21 | Ответить

    • Привет, Антон
      все скрипты давно выложены (и я стараюсь обновлять) по первой ссылке на странице Scripts — там где (скачать одним архивом)

      комментарий от Игорь Усольцев — 20.10.2016 @ 12:51 | Ответить


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