Ao analisar o Statspack me deparei com os seguintes eventos
|
1 2 3 4 5 6 7 8 9 10 |
Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ Data Guard: Gap Manager 180 10,800 60001 25.8 Data Guard: Timer 18 10,800 ###### 25.8 pman timer 3,600 10,799 3000 25.8 CPU time 6,565 15.7 db file parallel read 11,095 1,329 120 3.2 ------------------------------------------------------------- |
Causa
Este evento está sendo investigado bug: Bug 28523746
Solução
Adicionar as seguintes linhas de eventos ociosos manualmente
Oracle 12.2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
insert into STATS$IDLE_EVENT (event) values ('ASM cluster membership changes'); insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Comm SGA setup wait'); insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Servlet wait'); insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Surrogate wait'); insert into STATS$IDLE_EVENT (event) values ('Backup Appliance waiting for work'); insert into STATS$IDLE_EVENT (event) values ('Backup Appliance waiting restore start'); insert into STATS$IDLE_EVENT (event) values ('DNFS disp IO slave idle'); insert into STATS$IDLE_EVENT (event) values ('Data Guard: Gap Manager'); insert into STATS$IDLE_EVENT (event) values ('Data Guard: Timer'); insert into STATS$IDLE_EVENT (event) values ('Data Guard: controlfile update'); insert into STATS$IDLE_EVENT (event) values ('IMFS defer writes scheduler'); insert into STATS$IDLE_EVENT (event) values ('LMS CR slave timer'); insert into STATS$IDLE_EVENT (event) values ('OFS Receive Queue'); insert into STATS$IDLE_EVENT (event) values ('PL/SQL lock timer'); insert into STATS$IDLE_EVENT (event) values ('SCM slave idle'); insert into STATS$IDLE_EVENT (event) values ('Sharded Queues : Part Truncate idle'); insert into STATS$IDLE_EVENT (event) values ('Streams AQ: opt idle'); insert into STATS$IDLE_EVENT (event) values ('block compare coord process idle'); insert into STATS$IDLE_EVENT (event) values ('gopp msg'); insert into STATS$IDLE_EVENT (event) values ('nologging fetch slave idle'); insert into STATS$IDLE_EVENT (event) values ('pman timer'); insert into STATS$IDLE_EVENT (event) values ('recovery coordinator idle'); insert into STATS$IDLE_EVENT (event) values ('recovery logmerger idle'); insert into STATS$IDLE_EVENT (event) values ('recovery receiver idle'); insert into STATS$IDLE_EVENT (event) values ('recovery sender idle'); commit; |
Oracle 18.8
|
1 2 3 4 |
insert into STATS$IDLE_EVENT (event) values ('DBWR timer'); insert into STATS$IDLE_EVENT (event) values ('LogMiner merger: idle'); insert into STATS$IDLE_EVENT (event) values ('memoptimize write drain idle'); commit; |