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.
Bom dia Portilho!
Muito obrigado por compartilhar 🙂
Abração,
Obrigado pelo comentário Wagner!
Grande abraço !