Statspack – Rodrigo Silva e Silva

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

Publicado em 7 de março de 2019 by rodrigosilvaesilva

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;

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

Publicado em 10 de outubro de 2018 by rodrigosilvaesilva

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

Instalação Statspack / Coleta estatísticas completo

Publicado em 17 de abril de 2018 by rodrigosilvaesilva

Criar Tablespace

12:49:57 SYS@ORCL > create tablespace tools datafile '/u01/oracle/oradata/ORCL/tools.dbf' size 200m autoextend on next 100m;
Tablespace created.

1 2	12:49:57 SYS@ORCL > create tablespace tools datafile '/u01/oracle/oradata/ORCL/tools.dbf' size 200m autoextend on next 100m; Tablespace created.

Consultando espaço livre na tablespace.

select * from dba_free_space where tablespace_name = 'TOOLS';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
TOOLS                                  13        128  208666624      25472           13

select * from dba_free_space where tablespace_name = 'TOOLS';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

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

TOOLS 13 128 208666624 25472 13

Instalando o Statspack

@?/rdbms/admin/spcreate

1	@?/rdbms/admin/spcreate

Defina a senha do perfstat

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat

Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat

Definir a tablespace padrão

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS              STATSPACK DEFAULT TABLESPACE
------------------------------ --------------------- ----------------------------
PROD01_DATA                    PERMANENT
PROD01_INDX                    PERMANENT
SYSAUX                         PERMANENT             *
TOOLS                          PERMANENT
USERS                          PERMANENT
Elapsed: 00:00:00.00

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: TOOLS

Using tablespace TOOLS as PERFSTAT default tablespace.

Choose the Default tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store user data. Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installation FAILING, as

using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace. This is the tablespace

in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE

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

PROD01_DATA PERMANENT

PROD01_INDX PERMANENT

SYSAUX PERMANENT *

TOOLS PERMANENT

USERS PERMANENT

Elapsed: 00:00:00.00

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace: TOOLS

Using tablespace TOOLS as PERFSTAT default tablespace.

Definir a tablespace temporária para o usuário PERFSTAT

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE
------------------------------ --------------------- --------------------------
TEMP                           TEMPORARY             *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
...

...

...

12:53:33 SYS@ORCL > set echo off;
Creating Package STATSPACK...

Package created.

Elapsed: 00:00:07.92
No errors.
Creating Package Body STATSPACK...

Package body created.

Elapsed: 00:01:29.14
No errors.

<strong>NOTE:</strong>
<strong>SPCPKG complete. Please check spcpkg.lis for any errors.</strong>

Choose the Temporary tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store temporary data (e.g. for sort workareas). Specifying the SYSTEM

tablespace for the user's temporary tablespace will result in the

installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE

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

TEMP TEMPORARY *

Pressing <return> will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

NOTE:

SPCUSR complete. Please check spcusr.lis for any errors.

...

12:53:33 SYS@ORCL > set echo off;

Creating Package STATSPACK...

Package created.

Elapsed: 00:00:07.92

No errors.

Creating Package Body STATSPACK...

Package body created.

Elapsed: 00:01:29.14

No errors.

<strong>SPCPKG complete. Please check spcpkg.lis for any errors.</strong>

Coletando estatísticas manual.

execute statspack.snap;

1	execute statspack.snap;

Coletando estatísticas automática.

@?/rdbms/admin/spauto

1	@?/rdbms/admin/spauto

Listando os Snapshots

[oracle@ORCL]$ sqlplus perfstat/perfstat

select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database
order by 1, 2;

NAME         SNAP_ID Date/Time
--------- ---------- -------------------
ORCL               1 16.04.2018:13:00:04
ORCL               2 16.04.2018:14:00:18
ORCL               3 16.04.2018:15:00:29
ORCL              11 16.04.2018:16:00:11
ORCL              12 16.04.2018:17:00:26
ORCL              13 16.04.2018:18:00:26
ORCL              14 16.04.2018:19:00:11
ORCL              15 16.04.2018:20:00:11
ORCL              16 16.04.2018:21:00:20
ORCL              17 16.04.2018:22:00:05

10 rows selected.

[oracle@ORCL]$ sqlplus perfstat/perfstat

select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')

"Date/Time" from stats$snapshot,v$database

order by 1, 2;

NAME SNAP_ID Date/Time

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

ORCL 1 16.04.2018:13:00:04

ORCL 2 16.04.2018:14:00:18

ORCL 3 16.04.2018:15:00:29

ORCL 11 16.04.2018:16:00:11

ORCL 12 16.04.2018:17:00:26

ORCL 13 16.04.2018:18:00:26

ORCL 14 16.04.2018:19:00:11

ORCL 15 16.04.2018:20:00:11

ORCL 16 16.04.2018:21:00:20

ORCL 17 16.04.2018:22:00:05

10 rows selected.

Analisando relatório

@?/rdbms/admin/spreport

1	@?/rdbms/admin/spreport

Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL          ORCL                  1 16 Apr 2018 13:00     5
                                  2 16 Apr 2018 14:00     5
                                  3 16 Apr 2018 15:00     5
                                 11 16 Apr 2018 16:00     5
                                 12 16 Apr 2018 17:00     7
                                 13 16 Apr 2018 18:00     7

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11
Begin Snapshot Id specified: 11

Enter value for end_snap: 13
End   Snapshot Id specified: 13



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_11_13.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: analise lentidao

Listing all Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level Comment

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

ORCL ORCL 1 16 Apr 2018 13:00 5

2 16 Apr 2018 14:00 5

3 16 Apr 2018 15:00 5

11 16 Apr 2018 16:00 5

12 16 Apr 2018 17:00 7

13 16 Apr 2018 18:00 7

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 11

Begin Snapshot Id specified: 11

Enter value for end_snap: 13

End Snapshot Id specified: 13

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_11_13. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: analise lentidao

Report Gerado

Using the report name analise_lentidao

STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          1775857365 NBS                 1 16-Apr-18 15:11 12.2.0.1.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     bdora-granleste. Linux x86 64-bit           8     8       4         15.4

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:         33 17-Apr-18 14:00:00       92       3.4
  End Snap:         35 17-Apr-18 16:00:25       96       2.6
   Elapsed:     120.42 (mins) Av Act Sess:       0.7
   DB time:      80.42 (mins)      DB CPU:      53.39 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:     6,480M     6,432M   Std Block Size:         8K
     Shared Pool:     1,184M     1,232M       Log Buffer:     4,248K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                0.7                0.1        0.00        0.00
       DB CPU(s):                0.4                0.0        0.00        0.00
       Redo size:           26,156.2            1,868.5
   Logical reads:           14,586.2            1,042.0
   Block changes:              211.4               15.1
  Physical reads:                1.1                0.1
 Physical writes:                9.7                0.7
      User calls:              158.3               11.3
          Parses:              235.6               16.8
     Hard parses:               14.0                1.0
W/A MB processed:                0.1                0.0
          Logons:                0.3                0.0
        Executes:              570.9               40.8
       Rollbacks:               13.9                1.0
    Transactions:               14.0

Using the report name analise_lentidao

STATSPACK report for

Database DB Id Instance Inst Num Startup Time Release RAC

~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---

1775857365 NBS 1 16-Apr-18 15:11 12.2.0.1.0 NO

Host Name Platform CPUs Cores Sockets Memory (G)

~~~~ ---------------- ---------------------- ----- ----- ------- ------------

bdora-granleste. Linux x86 64-bit 8 8 4 15.4

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment

~~~~~~~~ ---------- ------------------ -------- --------- ------------------

Begin Snap: 33 17-Apr-18 14:00:00 92 3.4

End Snap: 35 17-Apr-18 16:00:25 96 2.6

Elapsed: 120.42 (mins) Av Act Sess: 0.7

DB time: 80.42 (mins) DB CPU: 53.39 (mins)

Cache Sizes Begin End

~~~~~~~~~~~ ---------- ----------

Buffer Cache: 6,480M 6,432M Std Block Size: 8K

Shared Pool: 1,184M 1,232M Log Buffer: 4,248K

Load Profile Per Second Per Transaction Per Exec Per Call

~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------

DB time(s): 0.7 0.1 0.00 0.00

DB CPU(s): 0.4 0.0 0.00 0.00

Redo size: 26,156.2 1,868.5

Logical reads: 14,586.2 1,042.0

Block changes: 211.4 15.1

Physical reads: 1.1 0.1

Physical writes: 9.7 0.7

User calls: 158.3 11.3

Parses: 235.6 16.8

Hard parses: 14.0 1.0

W/A MB processed: 0.1 0.0

Logons: 0.3 0.0

Executes: 570.9 40.8

Rollbacks: 13.9 1.0

Transactions: 14.0

Remover jobs agendados…