CPU time – Rodrigo Silva e Silva

Após instalar o Statspack Oracle 11.2.0.4 me deparei com os seguintes eventos

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
LGWR worker group idle                          12,929        41.0
CPU time                                            12       6,923 ######   22.0
heartbeat redo informer                          3,604       3,604   1000   11.4
lreg timer                                       1,201       3,607   3003   11.4
AQPC idle                                          120       3,601  30008   11.4

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

----------------------------------------- ------------ ----------- ------ ------

LGWR worker group idle 12,929 41.0

CPU time 12 6,923 ###### 22.0

heartbeat redo informer 3,604 3,604 1000 11.4

lreg timer 1,201 3,607 3003 11.4

AQPC idle 120 3,601 30008 11.4

Resolvendo Atualização tabela de eventos Statspack

SQL> 
delete from STATS$IDLE_EVENT;
insert into STATS$IDLE_EVENT select name from V$EVENT_NAME where wait_class='Idle';
insert into STATS$IDLE_EVENT values('log file parallel write');
insert into STATS$IDLE_EVENT values('target log write size');
commit;

SQL>

delete from STATS$IDLE_EVENT;

insert into STATS$IDLE_EVENT select name from V$EVENT_NAME where wait_class='Idle';

insert into STATS$IDLE_EVENT values('log file parallel write');

insert into STATS$IDLE_EVENT values('target log write size');

commit;

Ao analisar o Statspack me deparei com os seguintes eventos

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

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

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;

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

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;

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;

Fonte: Oracle Support

Tag: CPU time

Oracle – Statspack Idle events (LGWR worker group idle CPU time heartbeat redo informer lreg timer AQPC idle)

Statspack Idle Events Data Guard: Timer, pman timer, Data Guard: Gap Manager, log file sync e CPU time