Translate

Search This Blog

keep watch always on query causing extra I/O becuase of high HWM ..its high high high !!!

while I keep watch always on top resoucre intensive queries I observerd many time

HUGE I/O ON DEF$_AQERROR,THOUGH NO ROW in that CURRENTLY.
Since oracle checks for def error perioidically,it was wasting resource in our case.

for that Query oracle fires internally is below:

select q_name, state, delay, expiration, rowid, msgid,
dequeue_msgid, chain_no, local_order_no, enq_time, enq_tid,
step_no, priority, exception_qschema, exception_queue,
retry_count, corrid, time_manager_info
from SYSTEM.DEF$_AQERROR
where time_manager_info <= :1
and state != :2
for
update skip locked

currently there is no row in SYSTEM.DEF$_AQERROR table.
Though there were some errors seen in DEFERROR view a month ago due
confliction but we deleted them giving command like following for every error transaction.

.
execute dbms_defer_sys.delete_error(destination=>null,deferred_tran_id=>'1.26.1180542');
..
commit;


meanwhile we set parametr refresh_after_errors to true in all mv refreh group and when got all errors
removed from DEFERROR at master site we set back these parameter to default i.e
false.

Now my question was why did we still had reads from DEF$_ERRROR and that was comming in huge physical reads, the highest on instance.

Thouught:

that happening beacuse of HWM of DEF$_AQERROR was not reset by oracle's given error
removal procedure dbms_defer_sys.delete_error.

I took following action.

1. for all master groups
Suspend the master acivity at master site.
SQL> connect repadmin/repadmin
SQL> begin
dbms_repcat.suspend_master_activity(gname=>'REPDBA_MG');
end;
/

2.Truncate the table

SQL>connect SYSTEM/

SQL> truncate table def$aq_error;

3. Resume the master activity for all masterr groups.

SQL> connect repadmin/repadmin
SQL> begin
dbms_repcat.resume_master_activity(gname=>'REPDBA_MG');
end;
/



and I was done.

since then I never saw that query in top SQL