Oracle Database

Looks like a Bug: Coleta de System Statistics NOWORKLOAD cancela a WORKLOAD

Posted by

No 12cR2, após coletar System Statistics do tipo NOWORKLOAD, as do tipo WORKLOAD são anuladas.

O teste abaixo foi executado em uma máquina virtual. A versão utilizada foi a 12.2.0.1, com o Release Update 12.2.0.1.171017.

16:22:01 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                           2445
IOSEEKTIM                              24
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

9 rows selected.

Elapsed: 00:00:00.00
16:22:20 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.06
16:26:19 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.61
16:36:34 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                           2445
IOSEEKTIM                              24
IOTFRSPEED                           4096
SREADTIM                             73.1
MREADTIM
CPUSPEED                             2735
MBRC
MAXTHR
SLAVETHR

9 rows selected.

Elapsed: 00:00:00.00
16:36:39 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS;


PL/SQL procedure successfully completed.

Elapsed: 00:16:02.70
16:52:50 SYS@ORCL> 16:52:50 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                           2448
IOSEEKTIM                             147
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

9 rows selected.

Elapsed: 00:00:00.00
16:57:45 SYS@ORCL>

Testei também em uma máquina real, e o comportamento continua similar.

19:35:01 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME				    PVAL1
------------------------------ ----------
CPUSPEEDNW			      630
IOSEEKTIM				9
IOTFRSPEED			     4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

9 rows selected.

Elapsed: 00:00:00.01
19:35:46 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.70
19:42:33 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.20
21:07:28 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME				    PVAL1
------------------------------ ----------
CPUSPEEDNW			      630
IOSEEKTIM				9
IOTFRSPEED			     4096
SREADTIM			     .131
MREADTIM			    2.093
CPUSPEED			      608
MBRC					0
MAXTHR				139150336
SLAVETHR			 16561152

9 rows selected.

Elapsed: 00:00:00.01
21:07:35 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:00:45.74
21:08:32 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME				    PVAL1
------------------------------ ----------
CPUSPEEDNW			      609
IOSEEKTIM			       10
IOTFRSPEED			     4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

9 rows selected.

Elapsed: 00:00:00.00
21:09:38 SYS@ORCL>

Como este banco utiliza arquitetura CDB, testei tmbém no PDB. e o comportamento continua.

21:09:38 SYS@ORCL> ALTER SESSION SET CONTAINER = PROD;

Session altered.

Elapsed: 00:00:00.16
04:18:58 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME				    PVAL1
------------------------------ ----------
CPUSPEEDNW			      623
IOSEEKTIM				8
IOTFRSPEED			    84627
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

9 rows selected.

Elapsed: 00:00:00.00
04:19:00 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.32
04:19:05 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
04:23:33 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME				    PVAL1
------------------------------ ----------
CPUSPEEDNW			      623
IOSEEKTIM				8
IOTFRSPEED			    84627
SREADTIM
MREADTIM			    1.391
CPUSPEED			      624
MBRC
MAXTHR				168835072
SLAVETHR			  9793536

9 rows selected.

Elapsed: 00:00:00.00
04:23:35 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:00:31.88
04:24:16 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME				    PVAL1
------------------------------ ----------
CPUSPEEDNW			      623
IOSEEKTIM				8
IOTFRSPEED			    83997
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

9 rows selected.

Elapsed: 00:00:00.01
04:24:45 SYS@ORCL>

Esse comportamento não acontecia no 11gR2 – após a coleta NOWORKLOAD, os dados da WORKLOAD continuam dispoíveis. Abaixo é um teste com a versão 11.2.0.4, em uma Máquina Virtual 

01:06:01 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     3074.07407
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

9 rows selected.

Elapsed: 00:00:00.03
01:06:34 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.05
01:06:53 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
01:08:00 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                             2296
CPUSPEEDNW                     3074.07407
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM                            8.696

9 rows selected.

Elapsed: 00:00:00.00
01:08:01 SYS@ORCL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:01:23.73
01:09:42 SYS@ORCL> SELECT PNAME, PVAL1 FROM AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                             2296
CPUSPEEDNW                           2421
IOSEEKTIM                              14
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM                            8.696

9 rows selected.

Elapsed: 00:00:00.00
01:10:05 SYS@ORCL>

Como consta na documentação do 12cR2, o otimizador prefere as estatísticas WORKLOAD, provavelmente por serem mais precisas.
“If you gather workload statistics, then Oracle Database uses them instead of noworkload statistics.”
Fonte: https://docs.oracle.com/database/122/TGSQL/gathering-optimizer-statistics.htm#TGSQL443

Portanto, sugiro utilizar apenas coletar a NOWORKLOAD uma vez, e em seguida a WORKLOAD, e executar as duas coletas novamente em caso de alteração de hardware.

2 comments

Deixe uma resposta para ricardo@nervinformatica.com.br Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.