Oracle mechanics

30.09.2015

Об использовании Parallel Statement Queuing в процессе Complete Non-Atomic mview refresh

Filed under: Диагностика системы (instance),mview,Oracle — Игорь Усольцев @ 00:05
Tags: , ,

При выполнении DBMS_MVIEW.REFRESH(…, ‘C’, ATOMIC_REFRESH=>FALSE) можно видеть ожидание pipe get, что ожидаемо и описано в Complete Refresh Of Materialized View Hangs On Pipe Get (Doc ID 1193294.1), где, среди прочих «смелых» решений|solutions (удалять индексы перед обновлением, запретить job-ы на системном уровне, не использовать NONATOMIC REFRESH), рекомендуют использовать dbms_jobs вместо dbms_scheduler с помощью специального замысловатого event:)

$ oerr ora 10992
10992, 00000, "event to enable dbms_job instead of dbms_scheduler"
// *Cause:
// *Action:   enables dbms_job instead of dbms_scheduler
// *Comment:  set this event only under the supervision of Oracle Development
// LEVEL        ACTION
//---------------------------------------------------------------------------
//  0x0001      enable dbms_job instead of dbms_scheduler for MV refresh

Т.е. штатно DBMS_MVIEW.REFRESH после собственно обновления (TRUNCATE+INSERT /*+ APPEND*/) запускает одновременно несколько DBMS_SCHEDULER заданий с процедурами типа:

BEGIN
  SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST(INPIPE         => 'IDX_RB$IPIPE_10343_12'
                                         ,JOBID          =>  12
                                         ,OUTPIPE        => 'IDX_RB$PIPE_10343'
                                         ,CONT_AFTER_ERR =>  0
                                         ,DEGREE         =>  3); -- DOP для перестройки набора партиций/индексов с неясным
END;                                                             -- происхождением, не совпадает с DEGREE перестраиваемых индексов

для перестройки индексов и ждёт окончания их выполнения на pipe get

Job-ы эти, в свою очередь, как следует из DEGREE могут выполняються параллельно:

12.1.0.2@ SQL> select sql_id,
  2         sql_opname,
  3         REGEXP_SUBSTR(program, '\([J-P]') as PROGRAM,
  4         count(distinct session_id || '' || session_serial#) sid_count
  5    from gv$active_session_history
  6   where module = 'DBMS_SCHEDULER'
  7     and action like 'IDX_RB$J%'  -- характерный признак
  8     and sql_id is not null
  9   group by sql_id, sql_exec_id,
 10            sql_opname,
 11            REGEXP_SUBSTR(program, '\([J-P]')
 12   having count(*) >= 100 order by count(*) desc
 13  /
 
SQL_ID        SQL_OPNAME     PROGRAM   SID_COUNT
------------- -------------- -------- ----------
0g55737vg4dyf CREATE INDEX   (P                9
1y0ffdq0p9bgu CREATE INDEX   (P                9
801smtqpk9jhf CREATE INDEX   (P               10
7zmq59vygbzjg CREATE INDEX   (P               10
bq0x607gz3dud CREATE INDEX   (P                6
...

, показывая не всегда обычный для перестройки индексов набор ожиданий:

SQL> select session_state,
  2         event,
  3         REGEXP_SUBSTR(program, '\([J-P]') as PROGRAM,
  4         count(*)
  5    from gv$active_session_history
  6   where module = 'DBMS_SCHEDULER'
  7     and action like 'IDX_RB$J%'
  8   group by session_state,
  9            event,
 10            REGEXP_SUBSTR(program, '\([J-P]')
 11   having count(*) > 500
 12   order by count(*) desc
 13  /
 
SESSION_STATE EVENT                    PROGRAM    COUNT(*)
------------- ------------------------ -------- ----------
WAITING       db file scattered read   (P             5852 -- типично
WAITING       library cache lock       (J             2449 -- НЕтипично
WAITING       direct path read         (P             1714 -- типично
WAITING       direct path read         (J             1496 -- типично
WAITING       cursor: pin S wait on X  (P             1221 -- НЕтипично
WAITING       read by other session    (P              982 -- типично
WAITING       db file scattered read   (J              957 -- типично
ON CPU                                 (P              743
WAITING       library cache lock       (P              571 -- НЕтипично
...

И если cursor: pin S wait on X отражало, в общем, характерное для параллельного выполнения (точнее, этапа parallel parse) поведение:

SQL> @ash_sql_wait_tree "event in ('cursor: pin S wait on X') and module = 'DBMS_SCHEDULER' and action like 'IDX_RB$J%'" 0 "where sample_time between to_date('10.09.2015 14:35:00','dd.mm.yyyy hh24:mi:ss') and to_date('10.09.2015 14:39:00','dd.mm.yyyy hh24:mi:ss')"
 
LVL INST_ID BLOCKING_TREE EVENT                   WAITS_COUNT EXECS_COUNT AVG_WAIT_TIME_MS SESS_COUNT MODULE          ACTION             PLSQL_OBJECT_ID DATA_OBJECT BLOCK_SID       SQL_ID        SQL_OPNAME  
--- ------- ------------- ----------------------- ----------- ----------- ---------------- ---------- --------------- ------------------ --------------- ----------- --------------- ------------- -------------
  1       1 (P...)        cursor: pin S wait on X          36           1              969          5 DBMS_SCHEDULER  IDX_RB$J_10343_26  ..              ..          VALID i#1       6sgz8sqg4twzp CREATE INDEX
  1       1 (P...)        cursor: pin S wait on X          33           1              973          4 DBMS_SCHEDULER  IDX_RB$J_10343_12  ..              ..          VALID i#1       bqz780pwtvrys CREATE INDEX
  1       1 (P...)        cursor: pin S wait on X          33           1              967          5 DBMS_SCHEDULER  IDX_RB$J_10343_11  ..              ..          VALID i#1       8hf9ch9azq3nt CREATE INDEX
  1       1 (P...)        cursor: pin S wait on X          30           1              953          5 DBMS_SCHEDULER  IDX_RB$J_10343_26  ..              ..          VALID i#1       4swhf7nn7ptv1 CREATE INDEX
...
  2       1   (P...)      On CPU / runqueue                61           0                0          3 DBMS_SCHEDULER  IDX_RB$J_10343_11  ..              ..          NOT IN WAIT i#  8hf9ch9azq3nt ALTER INDEX 
  2       1   (P...)      On CPU / runqueue                55           0                0          3 DBMS_SCHEDULER  IDX_RB$J_10343_12  ..              ..          NOT IN WAIT i#  bqz780pwtvrys ALTER INDEX 
  2       1   (P...)      On CPU / runqueue                50           0                0          3 DBMS_SCHEDULER  IDX_RB$J_10343_26  ..              ..          NOT IN WAIT i#  4swhf7nn7ptv1 ALTER INDEX 
...
  3       1     (P...)    On CPU / runqueue                47           0                0          2 DBMS_SCHEDULER  IDX_RB$J_10343_11  ..              ..          NOT IN WAIT i#  8hf9ch9azq3nt ALTER INDEX 
  3       1     (P...)    gc buffer busy acquire           24           0                0          1 DBMS_SCHEDULER  IDX_RB$J_10343_10  ..              ..          UNKNOWN i#      fz7q40x1wtdp9 ALTER INDEX 
  3       1     (P...)    On CPU / runqueue                24           0                0          1 DBMS_SCHEDULER  IDX_RB$J_10343_10  ..              ..          NOT IN WAIT i#  fz7q40x1wtdp9 ALTER INDEX 
  3       1     (P...)    db file sequential read          24           0              204          1 DBMS_SCHEDULER  IDX_RB$J_10343_10  ..              ..          NO HOLDER i#    fz7q40x1wtdp9 ALTER INDEX 
...
  4       1       (P...)  On CPU / runqueue                12           0                0          2 DBMS_SCHEDULER  IDX_RB$J_10343_11  ..              ..          NOT IN WAIT i#  8hf9ch9azq3nt ALTER INDEX 
  4       1       (P...)  On CPU / runqueue                 4           0                0          1 DBMS_SCHEDULER  IDX_RB$J_10343_10  ..              ..          NOT IN WAIT i#  fz7q40x1wtdp9 ALTER INDEX 
  4       1       (P...)  gc buffer busy acquire            4           0                0          1 DBMS_SCHEDULER  IDX_RB$J_10343_10  ..              ..          UNKNOWN i#      fz7q40x1wtdp9 ALTER INDEX 
  4       1       (P...)  db file sequential read           4           0              204          1 DBMS_SCHEDULER  IDX_RB$J_10343_10  ..              ..          NO HOLDER i#    fz7q40x1wtdp9 ALTER INDEX 
...

, то ожидание library cache lock одновременной перестройки разных партиций одного индекса при online наблюдении показывало различные замысловатые картины:

SQL> @kgllockpin
 
HANDLER          KGLLKTYPE  NAMESP           KGLOBTYD KGLNAOBJ         MODE_HELD  MODE_REQ   SECS_IN_WAIT EVENT                   SECONDS_IN_WAIT  SID    SERIAL  SADDR            PROGRAM                      SQL_ID        SQL_TEXT
---------------- ---------- ---------------- -------- ---------------- ---------- ---------- ------------ ----------------------- ---------------  ------ ------- ---------------- ---------------------------- ------------- ----------------------------------------------------------------------------------------------------
00000001294490E0 Lock       TABLE/PROCEDURE  TABLE    MV_SOME_MATVIEW  None       Exclusive  3            library cache lock                    3  869    50611   0000001931A2CFC0 oracle@my_db_host.ru (J008)  52trrjj5sqxsj SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */
00000001294490E0 Lock       TABLE/PROCEDURE  TABLE    MV_SOME_MATVIEW  Null       None       3            library cache lock                    3  869    50611   0000001931A2CFC0 oracle@my_db_host.ru (J008)  52trrjj5sqxsj SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */
00000001294490E0 Pin        TABLE/PROCEDURE  TABLE    MV_SOME_MATVIEW  Share      None       3            library cache lock                    3  869    50611   0000001931A2CFC0 oracle@my_db_host.ru (J008)  52trrjj5sqxsj SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */
00000001294490E0 Lock       TABLE/PROCEDURE  TABLE    MV_SOME_MATVIEW  Share      None       2            db file sequential read               2  1211   6586    0000001951AE0218 oracle@my_db_host.ru (J010)  csu2fwmkwtacf ALTER INDEX "BIEE"."MV_SOME_MATVIEW_FIRM" REBUILD  PARTITION "SYS_P11685" PARALLEL 3

, типа блокировок рекурсивными запросами операции INDEX REBUILD на объекте library cache типа TABLE, порой с необычными требованиями Exclusive Lock для читающих запроса типа Dynamic Sampling (?):

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */
 SUM(C1)
  FROM (SELECT /*+ qb_name("innerQuery")  */
         1 AS C1
          FROM (SELECT 0
                  FROM "BIEE"."MV_SOME_MATVIEW" "MV_SOME_MATVIEW") "VW_DIS_1") innerQuery

, который вряд ли является истинной причиной Exclusive Lock и проявляет скорее особенности отображения рекурсивных запросов операции INDEX REBUILD

А вот по ASH-статистике library cache lock для таких рекурсивных job-ов хорошо заметно:

SQL> @ash_sql_wait_tree_temp "module = 'DBMS_SCHEDULER' and action like 'IDX_RB$J%' and sample_time > trunc(sysdate)+3/24+15/24/60 and event = 'library cache lock'" 0
 
LVL INST_ID BLOCKING_TREE EVENT              WAITS_COUNT EXECS_COUNT AVG_WAIT_TIME_MS SESS_COUNT MODULE         ACTION              PLSQL_OBJECT_ID                          DATA_OBJECT                                   BLOCK_SID       SQL_ID        SQL_OPNAME    SQL_PLAN_OPERATION      SQL_TEXT
--- ------- ------------- ------------------ ----------- ----------- ---------------- ---------- -------------- ------------------- ---------------------------------------- --------------------------------------------- --------------- ------------- ------------- ----------------------- --------------------------------------------------------------------------------------------
  1       1 (J...)        library cache lock          10           1            84015          1 DBMS_SCHEDULER IDX_RB$J_11454_52   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  SYS.USER$.                                    VALID i#1       1j5rbc3rfphpg CREATE INDEX  CREATE INDEX STATEMENT  ALTER INDEX "BIEE"."MV_SOME_MATVIEW_2_FIRM" REBUILD  PARTITION "SYS_P12879" PARALLEL 3
  1       1 (J...)        library cache lock           8           0            97186          1 DBMS_SCHEDULER IDX_RB$J_11454_33   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            GLOBAL i#       gc5qfqq01x25d LOCK TABLE                            
  1       1 (J...)        library cache lock           6           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_49   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            VALID i#1       drry4fw34xqdd CREATE INDEX  CREATE INDEX STATEMENT  
  1       1 (J...)        library cache lock           6           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_56   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  BIEE.MV_SOME_MATVIEW_2_FIRM.SYS_P12977        VALID i#1       7zgkgdtwrt3yu CREATE INDEX  CREATE INDEX STATEMENT  
  1       1 (J...)        library cache lock           6           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_25   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  BIEE.MV_SOME_MATVIEW_2.SYS_P12512             VALID i#1       5uhhhg9jtxrns CREATE INDEX  CREATE INDEX STATEMENT  
  1       1 (J...)        library cache lock           5           0             1109          1 DBMS_SCHEDULER IDX_RB$J_11454_8    SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            GLOBAL i#       5n3v6aqmf26uh LOCK TABLE                            
  1       1 (J...)        library cache lock           5           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_23   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            VALID i#1       c6t8md78c59jt CREATE INDEX  CREATE INDEX STATEMENT  
  1       1 (J...)        library cache lock           4           1           224613          1 DBMS_SCHEDULER IDX_RB$J_11454_20   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            VALID i#1       35j35pvqb5xp4 CREATE INDEX  CREATE INDEX STATEMENT  
  1       1 (J...)        library cache lock           3           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_5    SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            VALID i#1       dfbqcpfzr0p15 CREATE INDEX  CREATE INDEX STATEMENT  ALTER INDEX "BIEE"."MV_SOME_MATVIEW_2_TRMNGR" REBUILD  PARTITION "SYS_P13150" PARALLEL 3
  1       1 (J...)        library cache lock           3           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_4    SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  BIEE.MV_SOME_MATVIEW_2_OCLIENT_ID.SYS_P13439  VALID i#1       88p60cra2904y CREATE INDEX  CREATE INDEX STATEMENT  ALTER INDEX "BIEE"."MV_SOME_MATVIEW_2_OCLIENT_ID" REBUILD  PARTITION "SYS_P13439" PARALLEL 2
  1       1 (J...)        library cache lock           3           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_21   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            VALID i#1       9a1ajrj6sm6ky CREATE INDEX  CREATE INDEX STATEMENT  ALTER INDEX "BIEE"."MV_SOME_MATVIEW_2_MANAGER" REBUILD  PARTITION "SYS_P13075" PARALLEL 2
  1       1 (J...)        library cache lock           2           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_45   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            VALID i#1       b22zqfbgv5cw6 CREATE INDEX  CREATE INDEX STATEMENT  
  1       1 (J...)        library cache lock           2           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_41   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            VALID i#1       ak42shdfuuzg1 CREATE INDEX  CREATE INDEX STATEMENT  
  1       1 (J...)        library cache lock           2           0              972          1 DBMS_SCHEDULER IDX_RB$J_11454_28   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            GLOBAL i#       5uhyvhwxu5uxp LOCK TABLE                            
  1       1 (J...)        library cache lock           2           1           449590          1 DBMS_SCHEDULER IDX_RB$J_11454_17   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            VALID i#1       4a67h6bn8p09t CREATE INDEX  CREATE INDEX STATEMENT  ALTER INDEX "BIEE"."MV_SOME_MATVIEW_2_MANAGER" REBUILD  PARTITION "SYS_P13072" PARALLEL 2
  1       1 (J...)        library cache lock           2           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_38   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            VALID i#1       2c6tkz1yr4gmb CREATE INDEX  CREATE INDEX STATEMENT  ALTER INDEX "BIEE"."MV_SOME_MATVIEW_2_MGR_GRP" REBUILD  PARTITION "SYS_P13289" PARALLEL 2
  1       1 (J...)        library cache lock           2           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_58   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  ..                                            VALID i#1       gv8msj18mhuby CREATE INDEX  CREATE INDEX STATEMENT  
  2       1   (J...)      resmgr:pq queued           168           1                0          1 DBMS_SCHEDULER IDX_RB$J_11454_59   SYS.DBMS_I_INDEX_UTL.REBUILD_INDEX_LIST  BIEE.MV_SOME_MATVIEW_2_MGR_GRP.SYS_P13344     VALID i#1       9cycsq908h09s CREATE INDEX  CREATE INDEX STATEMENT  ALTER INDEX "BIEE"."MV_SOME_MATVIEW_2_TRMNGR" REBUILD  PARTITION "SYS_P13210" PARALLEL 3
  3       1     (J...)    On CPU / runqueue            6           1                0          1                                    SYS.DBMS_REFRESH.REFRESH                 BIEE.MV_SOME_SALES_FIRM.                      NOT IN WAIT i#  avxmz09d1usr8 INSERT        LOAD TABLE CONVENTIONAL INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "BIEE"."MV_SOME_SALES" SELECT
  3       1     (J...)    On CPU / runqueue            3           1                0          1                                    SYS.DBMS_REFRESH.REFRESH                 ..                                            NOT IN WAIT i#  avxmz09d1usr8 INSERT        LOAD TABLE CONVENTIONAL INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "BIEE"."MV_SOME_SALES" SELECT
  3       1     (J...)    On CPU / runqueue            1           1                0          1                                    SYS.DBMS_REFRESH.REFRESH                 BIEE.MV_SOME_SALES.                           NOT IN WAIT i#  avxmz09d1usr8 INSERT        LOAD TABLE CONVENTIONAL INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "BIEE"."MV_SOME_SALES" SELECT

— что основными блокерами являются сессии, ожидающие resmgr:pq queued, что характерно для Parallel Statement Queuing, который, действительно применялся при обновлении матвью в виде:

SQL> select job_action from dba_scheduler_jobs where job_name = 'J_RFSH_MV_SOME_MATVIEW';
 
JOB_ACTION
-----------------------------------------------------------------------------
begin
  EXECUTE IMMEDIATE 'alter session set "_parallel_statement_queuing" = TRUE';
  DBMS_MVIEW.REFRESH('MV_SOME_MATVIEW', 'C', ATOMIC_REFRESH=>FALSE);
end;

и периодически в результате длительных ожиданий library cache lock приводил к ошибке типа ORA-04021:

23-SEP-15 03.15.16.619844 AM +03:00

*** 2015-09-23 03:15:16.616
ORA-12012: ошибка при автоисполнении задания "BIEE"."J_RFSH_MV_SOME_MATVIEW"
ORA-20001: Job failed: ORA-04021: таймаут произошел во время ожидания блокировки объекта
ORA-06512: на  "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: на  "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: на  "SYS.DBMS_SNAPSHOT", line 3017
...

— где The timeout normally occurs after 5 minutesHow to Analyze Library Cache Timeout with Associated: ORA-04021 ‘timeout occurred while waiting to lock object %s%s%s%s%s.’ Errors (Doc ID 1486712.1)

При такого рода ошибках в процессе перестройки индексов после Complete Non-Atomic mview refresh DBA_MVIEWS.LAST_REFRESH_END_TIME успешно обновляется, т.е. матвью может рассматриваться условно обновлённым с ошибками в DBA_SCHEDULER_JOB_RUN_DETAILS

Вероятность такого рода проблем в дополнение к описанному ранее в общем случае ненадёжному поведению Parallel Statement Queuing делает смысл применение этого механизма в процессе полного неатомарного обновления сомнительным удовольствием

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

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

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