在 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.
