Oracle mechanics

17.08.2018

12.2 Materialized View Refresh Statistics Slowdown

Filed under: 12.2,Oracle — Игорь Усольцев @ 01:24
Tags: ,

Заметно замедлившайся рутинная операция обновления matview в версии Oracle 12.2 указывала, что выполняющая обновление сессия помимо необходимых и понятных операций DELETE+INSERT значительную часть времени проводила в дублирующих общую статистику DBMS_STATS подсчётах Cardinality всех подряд странного набора matview нашей базы:

  
SQL> select sql_id, sql_exec_id, count(*), min(sample_time), max(sample_time), dbms_lob.substr(sql_text,100)
  2    from gv$active_session_history
  3    left join dba_hist_sqltext using (sql_id)
  4   where session_id = 3634
  5     and inst_id = 1
  6     and session_serial# = 17328
  7   group by sql_id, sql_exec_id, dbms_lob.substr(sql_text,100)
  8  having count(*) > 3
  9  order by 4
 10  /

SQL_ID        SQL_EXEC_ID   COUNT(*) MIN(SAMPLE_TIME)            MAX(SAMPLE_TIME)            DBMS_LOB.SUBSTR(SQL_TEXT,100)
------------- ----------- ---------- --------------------------- --------------------------- ---------------------------------------------------------------------------------
9vxyq0gty0pr1    16777297          5 02-AUG-18 07.19.06.646 PM   02-AUG-18 07.19.10.650 PM   
g4r39jbgmud4u    16777246          7 02-AUG-18 07.19.11.651 PM   02-AUG-18 07.19.17.657 PM   select count(*) from "GOO"."MV_CONTRACTS"
a71s0k6vsaqmb    16777279         44 02-AUG-18 07.19.18.658 PM   02-AUG-18 07.20.01.746 PM   select count(*) from "GOO"."MV_ACT_1"
232du7f9qb4gx    16777298       1057 02-AUG-18 07.20.02.747 PM   02-AUG-18 07.37.39.986 PM   select count(*) from "GOO"."MV_EXTERNS"  -- *
aa4rs8fkjdjk5    16777296         63 02-AUG-18 07.37.40.988 PM   02-AUG-18 07.38.43.058 PM   select count(*) from "GOO"."MV_INVOICES" -- **
2532qjaq0bua5    16777219          4 02-AUG-18 07.38.44.059 PM   02-AUG-18 08.43.11.515 PM   call goo.custom_package.refresh_mv()
17mzq9tghb9sh    16777218        592 02-AUG-18 07.38.47.062 PM   02-AUG-18 07.48.39.756 PM   /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND  */ INTO "GOO"."MV8472"
aa4rs8fkjdjk5    16777297          4 02-AUG-18 07.48.55.772 PM   02-AUG-18 07.48.58.775 PM   select count(*) from "GOO"."MV_INVOICES" -- **
8cmhnwxc8bzwt    16777224          4 02-AUG-18 07.49.00.777 PM   02-AUG-18 07.49.03.780 PM   
232du7f9qb4gx    16777299        283 02-AUG-18 07.49.04.781 PM   02-AUG-18 07.53.47.067 PM   select count(*) from "GOO"."MV_EXTERNS" -- *
a5zjwn0xuj513    16777218        149 02-AUG-18 07.53.49.069 PM   02-AUG-18 07.56.17.269 PM   
ffu3smfquwm15    16777218        221 02-AUG-18 07.56.25.277 PM   02-AUG-18 08.00.06.547 PM   
8x6mz0wsqqm4r    16777218        122 02-AUG-18 08.00.09.550 PM   02-AUG-18 08.02.10.675 PM   
b9u2rhmtxntm3    16777276        374 02-AUG-18 08.02.34.699 PM   02-AUG-18 08.08.48.121 PM   select count(*) from "GOO"."MV_ORDERS"
fxt6q9rv52gcr    16777265        409 02-AUG-18 08.08.49.122 PM   02-AUG-18 08.15.37.578 PM   select count(*) from "GOO"."MV_CONSUMES"
0rz070d6h4dq8    16777265        487 02-AUG-18 08.15.38.579 PM   02-AUG-18 08.23.46.164 PM   select count(*) from "GOO"."MV_ACT_2"
 
16 rows selected

— при этом для некоторых matview Cardinality вычислялась с двойной точностью — (*)(**)

Беглый поиск указывает на Bug 26338953 — MVIEW refresh slowed down because of select count(*) queries (Doc ID 26338953.8), к которому есть соответствующий fix:

  
This fix improves refresh performance when statistics-collection level, as set by DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT, is TYPICAL.
With this fix, Cardinality Collection is turned on only when STATISTICS_LEVEL = ADVANCED.

Rediscovery Notes
 When stats collection, as set by as set by DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT is TYPICAL, "select count(*)" queries are used to collect cardinalities of tables and MVs. 
 This causes refresh slowdown.

— т.е. это улучшение/нововведение версии 12.2 — Materialized View Refresh Statistics кроме спорных преимуществ требует установки патча чтобы не было хуже

К счастью, нововведение просто и безболезненно отключается:

  
 -- To turn off refresh-stats collection:

SQL> exec dbms_mview_stats.set_system_default('COLLECTION_LEVEL', 'NONE');
 
PL/SQL procedure successfully completed
 
SQL> commit;
 
Commit complete

SQL> > select * from dba_mvref_stats_sys_defaults;
 
PARAMETER_NAME   VALUE
---------------- ----------------------------------------
COLLECTION_LEVEL NONE
RETENTION_PERIOD 365
Реклама

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

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

RSS feed for comments on this post. TrackBack URI

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

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: