Oracle 等待事件：KNPC_ANQ_AWAITNONEMPTYQUEUE 高级复制队列推送

在 Oracle 数据库中，总有一些等待事件是你闻所未闻的，KNPC_ANQ_AWAITNONEMPTYQUEUE 就是这样一个。

在 AWR 报告中，您可能会遇到这样的 Top Event ：

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
latch: library cache                            95,690      20,270    212   58.7
CPU time                                                     5,840          16.9
knpc_anq_AwaitNonemptyQueue                         97       5,521  56923   16.0
db file sequential read                        127,944         580      5    1.7
log file parallel write                         75,287         407      5    1.2
          -------------------------------------------------------------
复制

更有甚者：

等待事件 knpc_anq_AwaitNonemptyQueue 是高级复制功能，推送过程中的等待，等待高级复制的推送过程。

这个等待是在 高级队列特性中，MOS 上只有一个相关的内容（高级复制在 12c 中不再支持了）。引用如下：
SYMPTOMS
While monitoring the database performance , we observe high values for wait events on knpc_anq_AwaitNonemptyQueue.

These event can be observed on Enterprise Manager or checking wait event views:

– Observe sid for sessions running push:

column dblink format a30
select /*+ ORDERED */ j.job, j.sid, d.dblink,
SUBSTR(TO_CHAR(J.THIS_DATE,'MM/DD/RRRR HH24:MI:SS'),1,20) START_DATE
from defschedule d, dba_jobs_running j
where j.job in (select job from dba_jobs
where upper(what) like '%DBMS_DEFER_SYS.PUSH%')
and j.job = d.job;
复制

– Check current wait event

select s.sid, s.serial#, s.event, s.username
from v$session s
where s.sid = <sid> and wait_time=0;
复制

– Check latest waits on v$session_history

SELECT ash.session_id,
  ash.event_count, ash_total.total_count,
  ash.event_count*100/ash_total.total_count percentage,
  'YES' busy,
  ash.event
FROM (SELECT SESSION_ID,
  SESSION_SERIAL#,
  EVENT,
  COUNT(sample_time) AS EVENT_COUNT
  FROM v$active_session_history
  WHERE sample_time > sysdate - 30/24/60
  AND SESSION_ID = <SID>
  GROUP BY session_id, session_serial#, event) ash,
  (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT
  FROM v$active_session_history
WHERE sample_time > sysdate - 30/24/60
AND SESSION_ID = <SID>) ash_total
ORDER BY percentage;
复制

CAUSE
Wait event knpc_anq_AwaitNonemptyQueue is a wait event, in which push process of Advanced Replication waits on meanwhile the delay_seconds specified in the push operation is completed.

This means if we define a push with delay_seconds as follows:

declare
  rc binary_integer;
begin
  rc := sys.dbms_defer_sys.push(destination=>'MYTARGET',
     execution_seconds=>1500,
     delay_seconds=>1200,
     parallelism=>1);
end;
复制

Once the push process has completed the push of deferred transaction to the other end, it will wait on knpc_anq_AwaitNonemptyQueue wait event till delay_seconds is achieved and the queue is empty

SOLUTION
Delay_seconds parameter in push is used to reduce execution overhead if PUSH is called from a tight loop.

To reduce knpc_anq_AwaitNonemptyQueue wait event, please consider a modification of delay_seconds parameter on push.