Você usa o Statspack? Talvez você precise!

novembro 22, 2015 V. J.A. Tuning Tools

Muitos profissionais que administram os bancos de dados Oracle versões 10g em diante utilizam o relatório AWR sem saber que a sua utilização requer a aquisição de uma licença adicional (Diagnostic Pack License), esse equívoco se deve um pouco ao fato desse recurso ser instalado normalmente numa instalação padrão do banco, ou seja, se a empresa que você trabalha não possui essa licença, após a instalação padrão é necessário realizar um procedimento para desativar esse recurso.

Nesse artigo vamos mostrar como desativar o AWR e como instalar o Statspack como alternativa para geração de relatórios de desempenho do banco de dados Oracle.

Informação sobre licença

Caso você tenha alguma dúvida sobre a licença para utilização do AWR, consulte a documentação da Oracle:

10gR2 Management Pack Licensing

11gR1 Management Pack Licensing

Verificar utilização do AWR

Caso você tenha dúvidas se os recursos do AWR já foram utilizados em uma determinada base, basta consultar a visão “DBA_FEATURE_USAGE_STATISTICS”, esta visão permite que a Oracle identifique todas as Options que já foram utilizada pelo menos um vez no seu Banco de dados:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

dbauser@LAB11 > set lines 200 pages 999
dbauser@LAB11 > break on version skip 1
dbauser@LAB11 > select version, name, detected_usages, currently_used, first_usage_date, last_usage_date
2 from DBA_FEATURE_USAGE_STATISTICS
3 where detected_usages > 0
4 and (name like '%Repository%' or name like '%AWR%')
5 order by 1, 2;

VERSION NAME DETECTED_USAGES CURRE FIRST_USA LAST_USAG
------------ --------------------------------- --------------- ----- --------- ---------
11.2.0.4.0 AWR Baseline 96 TRUE 11-JAN-14 11-NOV-15
AWR Report 53 FALSE 18-JAN-14 28-OCT-15
Automatic Workload Repository 4 FALSE 15-JUL-14 03-MAR-15

dbauser@LAB11 >

Como desativar o AWR

Se a sua empresa não possui a licença “Diagnostic Pack License” o ideal é desativar o AWR para garantir que outros profissionais da empresa não utilizem essa “Option” por equívoco.

Somente as instalações de Oracle Enterprise Edition podem utilizar a option “Diagnostic Pack License”, se a sua base for Standard Edition, Standard Edition One ou Personal Edition a utilização dessa “Option” não é permitida.

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

A maneira mais simples para desativar as funções do AWR consiste em desativar o processo de captura das informações da memoria do banco e gravação no repositório (SNAPSHOT). Para fazer isso existe um pacote DBMS_WORKLOAD_REPOSITORY que esta disponível nas versões 10g, 11g e 12c, veja abaixo um exemplo de desativação da coleta de SNAPSHOTS do AWR:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

dbauser@LAB11 > col SNAP_INTERVAL for a30
dbauser@LAB11 > col RETENTION for a30
dbauser@LAB11 > select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ ------------------------------ ----------
78772905 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

dbauser@LAB11 > execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>0);

PL/SQL procedure successfully completed.

dbauser@LAB11 > col SNAP_INTERVAL for a30
dbauser@LAB11 > col RETENTION for a30
dbauser@LAB11 > select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ ------------------------------ ----------
78772905 +40150 00:00:00.0 +00008 00:00:00.0 DEFAULT

dbauser@LAB11 >

Instalação do STATSPACK

O STATSPACK é uma ferramenta de diagnóstico de desempenho, disponível desde a versão Oracle8i, podemos utilizá-lo para analise de problemas de desempenho da instância, estes problemas costumam afetar várias aplicações ao mesmo tempo, ou podemos utiliza-lo para identificar instruções SQL consumindo uma quantidade excessiva de recursos da instância e com um tempo de resposta insatisfatório para aplicação .

O STATSPACK pode ser utilizado tanto de forma proativa para monitorar a alteração de carga na instância, como reativa para investigar um problema de desempenho.

O procedimento de instalação que vamos descrever a seguir pode ser utilizado para qualquer versão de banco Oracle a partir da versão 8.1.7.

Criar a tablespace PERFSTAT

Durante o processo de instalação do utilitário STATSPACK será solicitado o nome de uma tablespace para armazenar o repositório com as informações que serão coletadas da instância. A seguir vamos criar uma tablespace especifica para o STATSPACK e vamos atribuir o nome PERFSTAT a ela, você pode definir qualquer nome valido para essa tablespace, estamos definindo o mesmo nome que será atribuído ao usuário do STATSPACK para facilitar a identificação.

1
2
3
4
5
6
7

dbauser@LAB11 > create tablespace PERFSTAT
2 datafile '/oradata/LAB11/datafile/perfstat_01.dbf'
3 size 100M autoextend on next 64M maxsize 2G;

Tablespace created.

dbauser@LAB11 >

Executar o script de criação

Próximo passo é a criação do usuário PERFSTAT e seus respectivos objetos, para isso vamos executar o script SPCREATE.sql que pode ser encontrado no diretório “$ORACLE_HOME/rdbms/admin”. Esse script vai executar na sequência 3 scripts:

SPCUSER.sql que vai criar o usuário PERFSTAT
SPCTAB.sql que vai cria as tabelas e sinônimos
SPCPKG.sql que vai cria os pacotes com os programas utilizados pelo STATSPACK

Durante o processo de execução desse script você será solicitado a informar 3 parâmetros:
1) Informar uma senha que será utilizada para o usuário PERFSTAT
2) Informar o nome de uma tablespace que será utilizada como repositório para os dados do STATSPACK, neste caso informe o nome da tablespace criada na primeira etapa
3) Informar o nome de uma tablespace temporária que deve ser escolhida entre os nomes listados antes dessa solicitação.

[oracle@odbsrv11 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@odbsrv11 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 19 18:33:45 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@LAB11 > start spcreate.sql

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

Enter value for perfstat_password: oracle2015
oracle2015

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
------------------------------ --------- ----------------------------
EXAMPLE PERMANENT
PERFSTAT PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT

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

Enter value for default_tablespace: PERFSTAT

Using tablespace PERFSTAT as PERFSTAT default tablespace.

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

...
...
...

Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

perfstat@LAB11 >

Ajustar o nível de coleta do STATSPACK

O STATSPACK possui 5 opções de nível de coleta de informações da instância, ao termino da instalação este valor é definido como 5, o nível 10 permite que seja feita a coleta do maior número de informações possível, não recomendamos que seja definido este nível na coleta normal do STATSPACK num ambiente de produção pois ele vai consumir mais recurso do banco para faze-lo.

A definição do nível 6, na maioria das situações, será suficiente para fazer analise de problemas de desempenho da instância, para listar a descrição de cada um dos níveis podemos utilizar a tabela “stats$level_description”.

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
27

dbauser@LAB11 > select * from PERFSTAT.STATS$LEVEL_DESCRIPTION;

SNAP_LEVEL DESCRIPTION
---------- --------------------------------------------------------------------
0 This level captures general statistics, including rollback segment,
row cache, SGA, system events, background events, session events,
system statistics, wait statistics, lock statistics, and Latch
information

5 This level includes capturing high resource usage SQL Statements,
along with all data captured by lower levels

6 This level includes capturing SQL plan and SQL plan usage
information for high resource usage SQL Statements, along with all
data captured by lower levels

7 This level captures segment level statistics, including logical and
physical reads, row lock, itl and buffer busy waits, along with all
data captured by lower levels

10 This level includes capturing Child Latch statistics, along with all
data captured by lower levels

5 rows selected.

dbauser@LAB11 >

Você pode alterar o nível padrão de coleta do STATSPACK utilizando a função STATSPACK.SNAP, e se o parametro “i_modify_parameter” for informado como “TRUE” a alteração no nível de coleta será válida para todas as próximas coletas.

1
2
3
4
5

dbauser@LAB11 > exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');

PL/SQL procedure successfully completed.

dbauser@LAB11 >

Programar a coleta de SNAPSHOTS

Feita a configuração do STATSPACK o próximo passo é programar um JOB para coletar um SNAPSHOT a cada 30 minutos, o intervalo de tempo entre uma coleta de SNAPSHOT e outra deve ser definido conforme a necessidade de cada instalação.

Para programar a coleta automática dos SNAPSHOTS vamos utilizar o pacote DBMS_SCHEDULER e para tal precisamos conceder o direito para executar o comando “CREATE JOB” ao usuário PERFSTAT. A seguir você pode visualizar um exemplo dos comandos:

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

dbauser@LAB11 > grant create job to perfstat;

Grant succeeded.

dbauser@LAB11 > conn perfstat/oracle2015@lab11;
Connected.
perfstat@LAB11 > BEGIN
2 DBMS_SCHEDULER.CREATE_SCHEDULE(
3 schedule_name => 'statspack_snap_30min',
4 repeat_interval => 'FREQ=MINUTELY;BYMINUTE=00,30'
5 );
6
7 DBMS_SCHEDULER.CREATE_JOB (
8 job_name => 'statspack_snapshot',
9 job_type => 'STORED_PROCEDURE',
10 job_action => 'statspack.snap',
11 schedule_name => 'statspack_snap_30min',
12 auto_drop => FALSE,
13 comments => 'Statspack collection'
14 );
15
16 DBMS_SCHEDULER.ENABLE('statspack_snapshot');
17 END;
18 /

PL/SQL procedure successfully completed.

perfstat@LAB11 > set lines 200 pages 200;
perfstat@LAB11 > col JOB_NAME for a20;
perfstat@LAB11 > col JOB_ACTION for a20;
perfstat@LAB11 > col SCHEDULE_NAME for a20;
perfstat@LAB11 > select JOB_NAME,
2 JOB_TYPE,
3 JOB_ACTION,
4 SCHEDULE_NAME,
5 ENABLED,
6 AUTO_DROP,
7 STATE,
8 TO_CHAR(NEXT_RUN_DATE,'YYYY-MM-DD HH24:MI') as NEXT_RUN
9 from USER_SCHEDULER_JOBS;

JOB_NAME JOB_TYPE JOB_ACTION SCHEDULE_NAME ENABL AUTO_ STATE NEXT_RUN
-------------------- ---------------- -------------------- -------------------- ----- ----- ---------- ----------------
STATSPACK_SNAPSHOT STORED_PROCEDURE statspack.snap STATSPACK_SNAP_30MIN TRUE FALSE SCHEDULED 2015-11-19 19:00

1 row selected.

perfstat@LAB11 >

Observação: Caso você esteja configurando o STATSPACK numa base inferior a 10g utilize o script “spauto.sql” que se encontra no diretório “$ORACLE_HOME/rdbms/admin”.

Programar a eliminação dos SNAPSHOTS

Alem de programar a coleta de SNAPSHOTS é muito importante programar a eliminação dos SNAPSHOTS antigos, pois se isso não for feito as informações coletadas poderão consumir todo o espaço da tablespace e o processo de coleta vai parar de executar por falta de espaço.

Defina uma quantidade de dias que deseja manter como histórico de informações para geração de relatórios de analise de desempenho e elimine os SNAPSHOTS que forem mais velhos que o período definido, no exemplo a seguir vamos programar a execução de um JOB todos os dias as 04:30 hs. e a função PURGE vai manter SNAPSHOTS com informações por 8 dias, ou seja todos os SNAPSHOTS que foram criados a mais de oito dias serão eliminados.

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
27
28
29
30
31
32
33
34
35
36
37

dbauser@LAB11 > conn PERFSTAT/oracle2015@lab11
Connected.
perfstat@LAB11 > BEGIN
2 DBMS_SCHEDULER.CREATE_JOB (
3 job_name => 'statspack_purge_snapshots',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'STATSPACK.PURGE(I_NUM_DAYS => 8);',
6 repeat_interval => 'FREQ=DAILY; BYHOUR=04; BYMINUTE=30',
7 auto_drop => FALSE,
8 comments => 'Statspack snapshots purge'
9 );
10 DBMS_SCHEDULER.ENABLE('statspack_purge_snapshots');
11 END;
12 /

PL/SQL procedure successfully completed.

perfstat@LAB11 > set lines 200 pages 200;
perfstat@LAB11 > col JOB_NAME for a30;
perfstat@LAB11 > col JOB_ACTION for a35;
perfstat@LAB11 > select JOB_NAME,
2 JOB_TYPE,
3 JOB_ACTION,
4 ENABLED,
5 AUTO_DROP,
6 STATE,
7 TO_CHAR(NEXT_RUN_DATE,'YYYY-MM-DD HH24:MI') as NEXT_RUN
8 from USER_SCHEDULER_JOBS;

JOB_NAME JOB_TYPE JOB_ACTION ENABLED AUTO_ STATE NEXT_RUN
------------------------------ -------------------- ----------------------------------- ---------- ----- --------------- ----------------
STATSPACK_SNAPSHOT STORED_PROCEDURE statspack.snap TRUE FALSE SCHEDULED 2015-11-19 21:00
STATSPACK_PURGE_SNAPSHOTS PLSQL_BLOCK STATSPACK.PURGE(I_NUM_DAYS => 8); TRUE FALSE SCHEDULED 2015-11-20 04:30

2 rows selected.

perfstat@LAB11 >

Gerar um relatório STATSPACK

A geração do relatório STATSPACK e muito simples, basta excutar o script “spreport.sql” que pode ser encontrado no diretório “$ORACLE_HOME/rdbms/admin”, durante a execução do script serão solicitadas três informações: o SNAP_ID inicial, o SNAP_ID final e um nome para o relatório.

O SNAP_ID inicial e SNAP_ID final corresponde ao intervalo de tempo para o qual o relatório STATSPACK será gerado, ou seja, todas as atividades da instância nesse período de tempo serão reportadas em forma de estatísticas para analise.

Antes de solicitar a informação do SNAP_ID inicial o script vai fornecer uma lista de todos os SNAP_ID e suas respectivas datas e horas de geração, escolha os SNAP_ID de acordo com o período que deseja que seja gerado o relatório.

A seguir podemos ver um exemplo de geração do relatório STATSPACK:

[oracle@odbsrv11 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@odbsrv11 admin]$ ls -l | grep spreport
-rw-r--r-- 1 oracle oinstall 1284 Apr 23 2001 spreport.sql
[oracle@odbsrv11 admin]$ sqlplus perfstat/oracle2015

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 19 22:20:40 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

perfstat@LAB11 > @spreport.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
78772905 LAB11 1 lab11

Elapsed: 00:00:00.05

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
78772905 1 LAB11 lab11 odbsrv11.loc
alhost

Using 78772905 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
lab11 LAB11 1 19 Nov 2015 19:16 6
11 19 Nov 2015 19:30 6
12 19 Nov 2015 20:00 6
13 19 Nov 2015 20:30 6
14 19 Nov 2015 21:00 6
15 19 Nov 2015 21:30 6
16 19 Nov 2015 22:00 6

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

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

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

Enter value for report_name: report_statspack_20151119_20_21.txt

Using the report name report_statspack_20151119_20_21.txt

STATSPACK report for

.....
.....
.....

End of Report ( report_statspack_20151119_20_21.txt )

perfstat@LAB11 >

Gerar um plano de execução

Caso você identifique uma ou mais instrução SQL e queira analisar o plano de execução, é possível gerar o plano de execução utilizando o script “sprepsql.sql” que se encontra no diretório “$ORACLE_HOME/rdbms/admin” e durante sua execução será solicitada a identificação da instrução SQL (Old Hash Value) que é informada no relatório STATSPACK.

A seguir um exemplo de extração desse relatório:

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390

[oracle@odbsrv11 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@odbsrv11 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 19 22:35:43 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @sprepsql.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2991626347 LAB11 1 LAB11

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
2991626347 1 LAB11 LAB11 odbsrv11

Using 2991626347 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
LAB11 1 LAB11 1 12 Nov 2015 17:13 7
2 12 Nov 2015 18:13 7
3 12 Nov 2015 19:13 7
4 12 Nov 2015 20:13 7
5 12 Nov 2015 21:13 7
11 12 Nov 2015 22:13 7
12 12 Nov 2015 23:13 7
13 13 Nov 2015 00:13 7
14 13 Nov 2015 01:13 7
15 13 Nov 2015 02:13 7
16 13 Nov 2015 03:13 7
17 13 Nov 2015 04:13 7
18 13 Nov 2015 05:13 7
19 13 Nov 2015 06:13 7
20 13 Nov 2015 07:13 7
21 13 Nov 2015 08:13 7
22 13 Nov 2015 09:13 7
23 13 Nov 2015 10:13 7
24 13 Nov 2015 11:13 7
25 13 Nov 2015 12:13 7
26 13 Nov 2015 13:13 7
27 13 Nov 2015 14:13 7
28 13 Nov 2015 15:13 7
29 13 Nov 2015 16:13 7
30 13 Nov 2015 17:13 7
31 13 Nov 2015 18:13 7
32 13 Nov 2015 19:13 7
33 13 Nov 2015 20:13 7

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

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

Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 116748235
Hash Value specified is: 116748235

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

Enter value for report_name: statsapck_report_23_25_116748235

Using the report name statsapck_report_23_25_116748235

STATSPACK SQL report for Old Hash Value: 116748235 Module: SQL*Plus

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
LAB11 2991626347 LAB11 1 11.2.0.4.0 YES odbsrv11

Start Id Start Time End Id End Time Duration(mins)
--------- ------------------- --------- ------------------- --------------
23 13-Nov-15 10:13:06 25 13-Nov-15 12:13:06 120.00

SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 1,177,852 65,436.2 5.06
Disk Reads: 190,545 10,585.8 14.20
Rows processed: 18 1.0
CPU Time(s/ms): 28 1,577.4
Elapsed Time(s/ms): 422 23,420.7
Sorts: -1 -.1
Parse Calls: 18 1.0
Invalidations: 0
Version count: 2
Sharable Mem(K): 361
Executions: 18

SQL Text
~~~~~~~~
SELECT :"SYS_B_00"||MAX(ROUND(( SUM (total_mb)- SUM (free_mb))/
SUM (max_mb)*:"SYS_B_01")) PCT FROM ( SELECT tablespace_name,
SUM (bytes)/:"SYS_B_02"/:"SYS_B_03" FREE_MB, :"SYS_B_04" TOT
AL_MB, :"SYS_B_05" MAX_MB FROM dba_free_space GROUP BY tab
lespace_name UNION ALL SELECT tablespace_name, :"SYS_B_06"
CURRENT_MB, SUM (bytes)/:"SYS_B_07"/:"SYS_B_08" TOTAL_MB,
SUM ( DECODE (maxbytes,:"SYS_B_09",bytes, maxbytes))/:"SYS_B_10
"/:"SYS_B_11" MAX_MB FROM dba_data_files GROUP BY tablespa
ce_name) GROUP BY tablespace_name

Known Optimizer Plan(s) for this Old Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id's
they were first found in the shared pool. A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id

First First Last Plan
Snap Id Snap Time Active Time Hash Value Cost
--------- --------------- --------------- ------------ ----------
1 12-Nov-15 17:13 18-Nov-15 00:12 874526296 112
11 12-Nov-15 22:13 12-Nov-15 21:33 3155862605 106
15 13-Nov-15 02:13 17-Nov-15 21:34 3933858862 112
164 18-Nov-15 01:13 19-Nov-15 02:42 2569970882 114
164 18-Nov-15 01:13 19-Nov-15 22:12 3933858862 114

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 874526296 -----| | | 112 |
|SORT AGGREGATE | | 1 | 49 | 112 |
| HASH GROUP BY | | 1 | 49 | 112 |
| VIEW | | 98 | 4K| 111 |
| UNION-ALL | | | | |
| HASH GROUP BY | | 96 | 2K| 106 |
| VIEW |DBA_FREE_SPACE | 7K| 176K| 105 |
| UNION-ALL | | | | |
| NESTED LOOPS | | 1 | 67 | 1 |
| NESTED LOOPS | | 1 | 46 | 1 |
| INDEX FULL SCAN |I_FILE2 | 95 | 665 | 1 |
| TABLE ACCESS CLUSTER |FET$ | 1 | 39 | 0 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 21 | 0 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| NESTED LOOPS | | 98 | 6K| 29 |
| NESTED LOOPS | | 98 | 6K| 29 |
| TABLE ACCESS FULL |TS$ | 96 | 2K| 29 |
| FIXED TABLE FIXED INDEX|X$KTFBFE (ind:1) | 1 | 39 | 0 |
| INDEX UNIQUE SCAN |I_FILE2 | 1 | 7 | 0 |
| HASH JOIN | | 7K| 819K| 49 |
| HASH JOIN | | 119 | 4K| 35 |
| HASH JOIN | | 120 | 1K| 6 |
| INDEX FULL SCAN |I_FILE2 | 95 | 665 | 1 |
| TABLE ACCESS FULL |RECYCLEBIN$ | 120 | 1K| 5 |
| TABLE ACCESS FULL |TS$ | 96 | 2K| 29 |
| FIXED TABLE FULL |X$KTFBUE | 100K| 6M| 14 |
| NESTED LOOPS | | 1 | 89 | 26 |
| NESTED LOOPS | | 30 | 89 | 26 |
| NESTED LOOPS | | 1 | 80 | 23 |
| NESTED LOOPS | | 1 | 73 | 23 |
| TABLE ACCESS FULL |UET$ | 1 | 52 | 23 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 21 | 0 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| INDEX UNIQUE SCAN |I_FILE2 | 1 | 7 | 0 |
| INDEX RANGE SCAN |RECYCLEBIN$_TS | 30 | | 1 |
| TABLE ACCESS BY INDEX RO|RECYCLEBIN$ | 1 | 9 | 3 |
| HASH GROUP BY | | 2 | 72 | 5 |
| VIEW |DBA_DATA_FILES | 2 | 72 | 4 |
| UNION-ALL | | | | |
| NESTED LOOPS | | 1 | 361 | 2 |
| NESTED LOOPS | | 1 | 344 | 1 |
| NESTED LOOPS | | 1 | 331 | 1 |
| FIXED TABLE FULL |X$KCCFN | 1 | 310 | 0 |
| TABLE ACCESS BY INDEX |FILE$ | 1 | 21 | 1 |
| INDEX UNIQUE SCAN |I_FILE1 | 1 | | 0 |
| FIXED TABLE FIXED INDEX|X$KCCFE (ind:1) | 1 | 13 | 0 |

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

| TABLE ACCESS CLUSTER |TS$ | 1 | 17 | 1 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| NESTED LOOPS | | 1 | 412 | 2 |
| NESTED LOOPS | | 1 | 399 | 2 |
| NESTED LOOPS | | 1 | 392 | 1 |
| NESTED LOOPS | | 1 | 375 | 0 |
| FIXED TABLE FULL |X$KCCFN | 1 | 310 | 0 |
| FIXED TABLE FIXED IND|X$KTFBHC (ind:1) | 1 | 65 | 0 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 17 | 1 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| TABLE ACCESS BY INDEX R|FILE$ | 1 | 7 | 1 |
| INDEX UNIQUE SCAN |I_FILE1 | 1 | | 0 |
| FIXED TABLE FIXED INDEX |X$KCCFE (ind:1) | 1 | 13 | 0 |
|SELECT STATEMENT |----- 3155862605 ----| | | 106 |
|SORT AGGREGATE | | 1 | 49 | 106 |
| HASH GROUP BY | | 1 | 49 | 106 |
| VIEW | | 98 | 4K| 105 |
| UNION-ALL | | | | |
| HASH GROUP BY | | 96 | 2K| 100 |
| VIEW |DBA_FREE_SPACE | 3K| 74K| 99 |
| UNION-ALL | | | | |
| NESTED LOOPS | | 1 | 67 | 1 |
| NESTED LOOPS | | 1 | 46 | 1 |
| INDEX FULL SCAN |I_FILE2 | 95 | 665 | 1 |
| TABLE ACCESS CLUSTER |FET$ | 1 | 39 | 0 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 21 | 0 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| NESTED LOOPS | | 98 | 6K| 29 |
| NESTED LOOPS | | 98 | 6K| 29 |
| TABLE ACCESS FULL |TS$ | 96 | 2K| 29 |
| FIXED TABLE FIXED INDEX|X$KTFBFE (ind:1) | 1 | 39 | 0 |
| INDEX UNIQUE SCAN |I_FILE2 | 1 | 7 | 0 |
| NESTED LOOPS | | 3K| 341K| 49 |
| HASH JOIN | | 25K| 2M| 48 |
| HASH JOIN | | 50 | 1K| 34 |
| TABLE ACCESS FULL |RECYCLEBIN$ | 50 | 450 | 5 |
| TABLE ACCESS FULL |TS$ | 96 | 2K| 29 |
| FIXED TABLE FULL |X$KTFBUE | 100K| 6M| 14 |
| INDEX UNIQUE SCAN |I_FILE2 | 1 | 7 | 0 |
| NESTED LOOPS | | 1 | 89 | 20 |
| NESTED LOOPS | | 1 | 68 | 20 |
| MERGE JOIN CARTESIAN | | 4K| 74K| 20 |
| TABLE ACCESS FULL |RECYCLEBIN$ | 50 | 450 | 5 |
| BUFFER SORT | | 95 | 665 | 15 |
| INDEX FAST FULL SCAN |I_FILE2 | 95 | 665 | 0 |
| TABLE ACCESS CLUSTER |UET$ | 1 | 52 | 0 |
| INDEX UNIQUE SCAN |I_FILE#_BLOCK# | 1 | | 0 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 21 | 0 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

| HASH GROUP BY | | 2 | 72 | 5 |
| VIEW |DBA_DATA_FILES | 2 | 72 | 4 |
| UNION-ALL | | | | |
| NESTED LOOPS | | 1 | 361 | 2 |
| NESTED LOOPS | | 1 | 344 | 1 |
| NESTED LOOPS | | 1 | 331 | 1 |
| FIXED TABLE FULL |X$KCCFN | 1 | 310 | 0 |
| TABLE ACCESS BY INDEX |FILE$ | 1 | 21 | 1 |
| INDEX UNIQUE SCAN |I_FILE1 | 1 | | 0 |
| FIXED TABLE FIXED INDEX|X$KCCFE (ind:1) | 1 | 13 | 0 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 17 | 1 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| NESTED LOOPS | | 1 | 412 | 2 |
| NESTED LOOPS | | 1 | 399 | 2 |
| NESTED LOOPS | | 1 | 392 | 1 |
| NESTED LOOPS | | 1 | 375 | 0 |
| FIXED TABLE FULL |X$KCCFN | 1 | 310 | 0 |
| FIXED TABLE FIXED IND|X$KTFBHC (ind:1) | 1 | 65 | 0 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 17 | 1 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| TABLE ACCESS BY INDEX R|FILE$ | 1 | 7 | 1 |
| INDEX UNIQUE SCAN |I_FILE1 | 1 | | 0 |
| FIXED TABLE FIXED INDEX |X$KCCFE (ind:1) | 1 | 13 | 0 |
|SELECT STATEMENT |----- 3933858862 ----| | | 112 |
|SORT AGGREGATE | | 1 | 49 | 112 |
| HASH GROUP BY | | 1 | 49 | 112 |
| VIEW | | 98 | 4K| 111 |
| UNION-ALL | | | | |
| HASH GROUP BY | | 96 | 2K| 106 |
| VIEW |DBA_FREE_SPACE | 3K| 72K| 105 |
| UNION-ALL | | | | |
| NESTED LOOPS | | 1 | 67 | 1 |
| NESTED LOOPS | | 1 | 46 | 1 |
| INDEX FULL SCAN |I_FILE2 | 95 | 665 | 1 |
| TABLE ACCESS CLUSTER |FET$ | 1 | 39 | 0 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 21 | 0 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| NESTED LOOPS | | 98 | 6K| 29 |
| NESTED LOOPS | | 98 | 6K| 29 |
| TABLE ACCESS FULL |TS$ | 96 | 2K| 29 |
| FIXED TABLE FIXED INDEX|X$KTFBFE (ind:1) | 1 | 39 | 0 |
| INDEX UNIQUE SCAN |I_FILE2 | 1 | 7 | 0 |
| NESTED LOOPS | | 3K| 335K| 49 |
| HASH JOIN | | 25K| 2M| 48 |
| HASH JOIN | | 77 | 2K| 34 |
| TABLE ACCESS FULL |RECYCLEBIN$ | 77 | 770 | 5 |
| TABLE ACCESS FULL |TS$ | 96 | 2K| 29 |
| FIXED TABLE FULL |X$KTFBUE | 100K| 6M| 14 |
| INDEX UNIQUE SCAN |I_FILE2 | 1 | 7 | 0 |

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

| NESTED LOOPS | | 1 | 90 | 26 |
| NESTED LOOPS | | 36 | 90 | 26 |
| NESTED LOOPS | | 1 | 80 | 23 |
| NESTED LOOPS | | 1 | 73 | 23 |
| TABLE ACCESS FULL |UET$ | 1 | 52 | 23 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 21 | 0 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| INDEX UNIQUE SCAN |I_FILE2 | 1 | 7 | 0 |
| INDEX RANGE SCAN |RECYCLEBIN$_TS | 36 | | 1 |
| TABLE ACCESS BY INDEX RO|RECYCLEBIN$ | 1 | 10 | 3 |
| HASH GROUP BY | | 2 | 72 | 5 |
| VIEW |DBA_DATA_FILES | 2 | 72 | 4 |
| UNION-ALL | | | | |
| NESTED LOOPS | | 1 | 361 | 2 |
| NESTED LOOPS | | 1 | 344 | 1 |
| NESTED LOOPS | | 1 | 331 | 1 |
| FIXED TABLE FULL |X$KCCFN | 1 | 310 | 0 |
| TABLE ACCESS BY INDEX |FILE$ | 1 | 21 | 1 |
| INDEX UNIQUE SCAN |I_FILE1 | 1 | | 0 |
| FIXED TABLE FIXED INDEX|X$KCCFE (ind:1) | 1 | 13 | 0 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 17 | 1 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| NESTED LOOPS | | 1 | 412 | 2 |
| NESTED LOOPS | | 1 | 399 | 2 |
| NESTED LOOPS | | 1 | 392 | 1 |
| NESTED LOOPS | | 1 | 375 | 0 |
| FIXED TABLE FULL |X$KCCFN | 1 | 310 | 0 |
| FIXED TABLE FIXED IND|X$KTFBHC (ind:1) | 1 | 65 | 0 |
| TABLE ACCESS CLUSTER |TS$ | 1 | 17 | 1 |
| INDEX UNIQUE SCAN |I_TS# | 1 | | 0 |
| TABLE ACCESS BY INDEX R|FILE$ | 1 | 7 | 1 |
| INDEX UNIQUE SCAN |I_FILE1 | 1 | | 0 |
| FIXED TABLE FIXED INDEX |X$KCCFE (ind:1) | 1 | 13 | 0 |
--------------------------------------------------------------------------------

End of Report

SQL>

Remover a instalação do STATSPACK

Para finalizar, se por alguma razão você não for mais utilizar o STATSPACK, poderá remove-lo utilizando o script “spdrop.sql” que pode ser encontrado no diretório “$ORACLE_HOME/rdbms/admin”, não se esqueça que você precisa de um usuário SYSDBA para executar esse script e ao final da execução desse script, remova os JOBS criados para automatizar os processos e elimine a tablespace do repositório do STATSPACK se ela era de uso exclusivo deste.

Conclusão

O STATSPACK pode suprir a maioria das informações fornecidas no relatório AWR, mas se além do STATSPACK você decidir adquirir algum software de Performance e Tuning, observe se ele não acessa alguma visão que faz parte da Option “Diagnostic Pack License” pois a utilização dessas visões também requerem a contratação da licença.

O software DBTimeWizard oferece funções de Diagnóstico para Tuning de Banco de dados e aplicações sem consultar as visões que fazem parte da Option “Diagnostic Pack License”.

Neste artigo procuramos abordar as principais funções do STATSPACK, mas se você tiver a necessidade de realizar algo que não foi coberto pelo artigo, consulte a documentação do STATSPACK.