{"id":92,"date":"2015-11-22T21:33:13","date_gmt":"2015-11-22T23:33:13","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=92"},"modified":"2019-10-10T23:00:33","modified_gmt":"2019-10-11T02:00:33","slug":"voce-usa-o-statspack-talvez-voce-precise","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/voce-usa-o-statspack-talvez-voce-precise\/","title":{"rendered":"Voc\u00ea usa o Statspack? Talvez voc\u00ea precise!"},"content":{"rendered":"

Muitos profissionais que administram os bancos de dados Oracle vers\u00f5es 10g em diante utilizam o relat\u00f3rio AWR sem saber que a sua utiliza\u00e7\u00e3o requer \u00a0a aquisi\u00e7\u00e3o de uma licen\u00e7a adicional (Diagnostic Pack License), esse equ\u00edvoco\u00a0se deve um pouco ao fato desse recurso ser instalado normalmente numa instala\u00e7\u00e3o padr\u00e3o do banco, ou seja, se a empresa que voc\u00ea trabalha n\u00e3o possui essa licen\u00e7a, ap\u00f3s a instala\u00e7\u00e3o padr\u00e3o \u00e9\u00a0necess\u00e1rio realizar um procedimento\u00a0para desativar esse recurso.<\/span><\/p>\n

Nesse artigo vamos mostrar como desativar o AWR e como instalar o Statspack como alternativa para gera\u00e7\u00e3o de relat\u00f3rios de desempenho do banco de dados Oracle.<\/span><\/p>\n

Informa\u00e7\u00e3o sobre licen\u00e7a<\/span><\/h2>\n

Caso voc\u00ea tenha alguma d\u00favida sobre a licen\u00e7a para utiliza\u00e7\u00e3o do AWR, consulte a documenta\u00e7\u00e3o da Oracle:<\/span><\/p>\n

10gR2 Management Pack Licensing<\/a><\/p>\n

11gR1 Management Pack Licensing<\/a><\/p>\n

Verificar utiliza\u00e7\u00e3o do AWR<\/span><\/h2>\n

Caso voc\u00ea tenha\u00a0d\u00favidas se os\u00a0recursos do AWR j\u00e1 foram utilizados em uma determinada base, basta consultar a vis\u00e3o “DBA_FEATURE_USAGE_STATISTICS”, esta vis\u00e3o permite que a Oracle identifique todas as Options que j\u00e1 foram utilizada pelo menos um vez no seu Banco de dados:<\/span><\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span> set lines 200<\/span> pages 999<\/span>
\ndbauser@<\/span>LAB11 ><\/span> break<\/span> on version skip 1<\/span>
\ndbauser@<\/span>LAB11 ><\/span> select version,<\/span> name,<\/span> detected_usages,<\/span> currently_used,<\/span> first_usage_date,<\/span> last_usage_date
\n  2<\/span>    from DBA_FEATURE_USAGE_STATISTICS
\n  3<\/span>   where detected_usages ><\/span> 0<\/span>
\n  4<\/span>     and (<\/span>name like '%Repository%'<\/span> or name like '%AWR%'<\/span>)<\/span>
\n  5<\/span>   order by 1<\/span>,<\/span> 2<\/span>;<\/span>
\n
\nVERSION      NAME                              DETECTED_USAGES CURRE FIRST_USA LAST_USAG
\n------------<\/span> ---------------------------------<\/span> ---------------<\/span> -----<\/span> ---------<\/span> ---------<\/span>
\n11.<\/span>2.<\/span>0.<\/span>4.<\/span>0   AWR Baseline                                   96<\/span> TRUE<\/span>  11<\/span>-<\/span>JAN-<\/span>14<\/span> 11<\/span>-<\/span>NOV-<\/span>15<\/span>
\n             AWR Report                                     53<\/span> FALSE<\/span> 18<\/span>-<\/span>JAN-<\/span>14<\/span> 28<\/span>-<\/span>OCT-<\/span>15<\/span>
\n             Automatic Workload Repository                   4<\/span> FALSE<\/span> 15<\/span>-<\/span>JUL-<\/span>14<\/span> 03<\/span>-<\/span>MAR-<\/span>15<\/span>
\n
\n
\ndbauser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Como desativar o AWR<\/span><\/h2>\n

Se a sua empresa n\u00e3o possui a licen\u00e7a “Diagnostic Pack License” o ideal \u00e9 desativar o AWR para garantir que outros profissionais da empresa n\u00e3o utilizem essa “Option” por equ\u00edvoco.<\/span><\/p>\n

Somente as instala\u00e7\u00f5es de Oracle Enterprise Edition podem utilizar a option\u00a0“Diagnostic Pack License”, se a sua base for Standard Edition,\u00a0 Standard Edition One ou\u00a0Personal Edition a utiliza\u00e7\u00e3o dessa “Option” n\u00e3o \u00e9 permitida.<\/span><\/p>\n

A maneira mais simples para desativar as fun\u00e7\u00f5es do AWR consiste em desativar o processo de captura das informa\u00e7\u00f5es da memoria do banco\u00a0e grava\u00e7\u00e3o no reposit\u00f3rio (SNAPSHOT). Para fazer isso existe um pacote DBMS_WORKLOAD_REPOSITORY que esta dispon\u00edvel nas vers\u00f5es 10g, 11g e 12c,\u00a0veja abaixo um exemplo de desativa\u00e7\u00e3o da coleta de SNAPSHOTS do AWR:<\/span><\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span> col SNAP_INTERVAL for<\/span> a30
\ndbauser@<\/span>LAB11 ><\/span> col RETENTION for<\/span> a30
\ndbauser@<\/span>LAB11 ><\/span> select *<\/span> from dba_hist_wr_control;<\/span>
\n
\n      DBID SNAP_INTERVAL                  RETENTION                      TOPNSQL
\n----------<\/span> ------------------------------<\/span> ------------------------------<\/span> ----------<\/span>
\n  78772905<\/span> +<\/span>00000<\/span> 01<\/span>:<\/span>00<\/span>:<\/span>00.0<\/span>              +<\/span>00008<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.0<\/span>              DEFAULT<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span> execute DBMS_WORKLOAD_REPOSITORY.<\/span>MODIFY_SNAPSHOT_SETTINGS(<\/span>interval=><\/span>0<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span> col SNAP_INTERVAL for<\/span> a30
\ndbauser@<\/span>LAB11 ><\/span> col RETENTION for<\/span> a30
\ndbauser@<\/span>LAB11 ><\/span> select *<\/span> from dba_hist_wr_control;<\/span>
\n
\n      DBID SNAP_INTERVAL                  RETENTION                      TOPNSQL
\n----------<\/span> ------------------------------<\/span> ------------------------------<\/span> ----------<\/span>
\n  78772905<\/span> +<\/span>40150<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.0<\/span>              +<\/span>00008<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.0<\/span>              DEFAULT<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Instala\u00e7\u00e3o do STATSPACK<\/span><\/h2>\n

O STATSPACK \u00e9 uma ferramenta de diagn\u00f3stico de desempenho, dispon\u00edvel desde a vers\u00e3o Oracle8i, podemos utiliz\u00e1-lo para analise de problemas de desempenho da inst\u00e2ncia, estes problemas costumam afetar v\u00e1rias aplica\u00e7\u00f5es ao mesmo tempo, ou podemos utiliza-lo para identificar instru\u00e7\u00f5es SQL consumindo uma quantidade excessiva de recursos da inst\u00e2ncia e com um tempo de resposta insatisfat\u00f3rio para aplica\u00e7\u00e3o .<\/span><\/p>\n

O STATSPACK pode ser utilizado tanto de forma proativa para monitorar a altera\u00e7\u00e3o de carga na inst\u00e2ncia, como reativa para investigar um problema de desempenho.<\/span><\/p>\n

O procedimento de instala\u00e7\u00e3o que vamos descrever a seguir pode ser utilizado para qualquer vers\u00e3o de banco Oracle a partir da vers\u00e3o 8.1.7.<\/span><\/p>\n

 <\/p>\n

Criar a tablespace PERFSTAT<\/span><\/h3>\n

Durante o processo de instala\u00e7\u00e3o do utilit\u00e1rio STATSPACK ser\u00e1 solicitado o nome de uma tablespace para armazenar o reposit\u00f3rio com as informa\u00e7\u00f5es que ser\u00e3o coletadas da inst\u00e2ncia. A seguir vamos criar uma tablespace especifica para o STATSPACK e vamos atribuir o nome PERFSTAT a ela, voc\u00ea pode definir qualquer nome valido para essa tablespace, estamos definindo o mesmo nome que ser\u00e1 atribu\u00eddo ao usu\u00e1rio do STATSPACK para facilitar a identifica\u00e7\u00e3o.<\/p>\n

1
2
3
4
5
6
7
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span>  create tablespace PERFSTAT
\n  2<\/span>    datafile '\/oradata\/LAB11\/datafile\/perfstat_01.dbf'<\/span>
\n  3<\/span>    size 100M autoextend on next<\/span><\/a> 64M maxsize 2G;<\/span>
\n
\nTablespace created.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Executar o script de cria\u00e7\u00e3o<\/span><\/h3>\n

Pr\u00f3ximo passo \u00e9 a cria\u00e7\u00e3o do usu\u00e1rio PERFSTAT e seus respectivos objetos, para isso vamos executar o script SPCREATE.sql que pode ser encontrado no diret\u00f3rio “$ORACLE_HOME\/rdbms\/admin”. Esse script vai executar na sequ\u00eancia 3 scripts: <\/span><\/p>\n

SPCUSER.sql que vai criar o usu\u00e1rio PERFSTAT<\/span>
\nSPCTAB.sql que vai cria as tabelas e sin\u00f4nimos <\/span>
\nSPCPKG.sql que vai cria os pacotes com os programas utilizados pelo STATSPACK<\/span><\/p>\n

Durante o processo de execu\u00e7\u00e3o desse script voc\u00ea ser\u00e1 solicitado a informar 3 par\u00e2metros:<\/span>
\n1) Informar uma senha que ser\u00e1 utilizada para o usu\u00e1rio PERFSTAT<\/span>
\n2) Informar o nome de uma tablespace que ser\u00e1 utilizada como reposit\u00f3rio para os dados do STATSPACK, neste caso informe o nome da tablespace criada na primeira etapa<\/span>
\n3) Informar o nome de uma tablespace tempor\u00e1ria que deve ser escolhida entre os nomes listados antes dessa solicita\u00e7\u00e3o.<\/span><\/p>\n

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
<\/div><\/td>
[<\/span>oracle@<\/span>odbsrv11 ~]<\/span>$ cd $ORACLE_HOME<\/span>\/<\/span>rdbms\/<\/span>admin
\n[<\/span>oracle@<\/span>odbsrv11 admin]<\/span>$ sqlplus \/<\/span> as<\/span> sysdba
\n
\nSQL*<\/span>Plus:<\/span> Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 Production on Thu Nov 19<\/span> 18<\/span>:<\/span>33<\/span>:<\/span>45<\/span> 2015<\/span>
\n
\nCopyright (<\/span>c)<\/span> 1982<\/span>,<\/span> 2013<\/span>,<\/span> Oracle.<\/span>  All rights reserved.<\/span>
\n
\n
\nConnected to:<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> Production
\nWith the Partitioning,<\/span> OLAP,<\/span> Data Mining and Real Application Testing options
\n
\nsys@<\/span>LAB11 ><\/span> start spcreate.<\/span>sql
\n
\nChoose the PERFSTAT user's password
\n-----------------------------------
\nNot specifying a password will result in the installation FAILING
\n
\nEnter value for perfstat_password: oracle2015
\noracle2015
\n
\n
\nChoose the Default tablespace for the PERFSTAT user
\n---------------------------------------------------
\nBelow is the list of online tablespaces in this database which can
\nstore user data.  Specifying the SYSTEM tablespace for the user'<\/span>s
\ndefault<\/span> tablespace will result in the installation FAILING,<\/span> as<\/span>
\nusing SYSTEM<\/span><\/a> for<\/span> performance data is not supported.<\/span>
\n
\nChoose the PERFSTAT users's default tablespace.  This is the tablespace
\nin which the STATSPACK tables and indexes will be created.
\n
\nTABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
\n------------------------------ --------- ----------------------------
\nEXAMPLE                        PERMANENT
\nPERFSTAT                       PERMANENT
\nSYSAUX                         PERMANENT *
\nUSERS                          PERMANENT
\n
\nPressing <return> will result in STATSPACK'<\/span>s recommended default<\/span>
\ntablespace (<\/span>identified by *<\/span>)<\/span> being used.<\/span>
\n
\nEnter value for<\/span> default_tablespace:<\/span> PERFSTAT
\n
\nUsing tablespace PERFSTAT as<\/span> PERFSTAT default<\/span> tablespace.<\/span>
\n
\n
\nChoose the Temporary tablespace for<\/span> the PERFSTAT user
\n-----------------------------------------------------<\/span>
\nBelow is the list<\/span><\/a> of online tablespaces in this database which can
\nstore temporary data (<\/span>e.<\/span>g.<\/span> for<\/span> sort<\/span><\/a> workareas)<\/span>.<\/span>  Specifying the SYSTEM<\/span><\/a>
\ntablespace for<\/span> the user's temporary tablespace will result in the
\ninstallation FAILING, as using SYSTEM for workareas is not supported.
\n
\nChoose the PERFSTAT user'<\/span>s Temporary tablespace.<\/span>
\n
\nTABLESPACE_NAME                CONTENTS  DB DEFAULT<\/span> TEMP TABLESPACE
\n------------------------------<\/span> ---------<\/span> --------------------------<\/span>
\nTEMP                           TEMPORARY *<\/span>
\n
\nPressing <<\/span>return><\/span> will result in the database's default Temporary
\ntablespace (identified by *) being used.
\n
\nEnter value for temporary_tablespace: TEMP
\n
\nUsing tablespace TEMP as PERFSTAT temporary tablespace.
\n
\n
\n... Creating PERFSTAT user
\n
\n...
\n...
\n...
\n
\nCreating Package STATSPACK...
\n
\nPackage created.
\n
\nNo errors.
\nCreating Package Body STATSPACK...
\n
\nPackage body created.
\n
\nNo errors.
\n
\nNOTE:
\nSPCPKG complete. Please check spcpkg.lis for any errors.
\n
\nperfstat@LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Ajustar o n\u00edvel de coleta do STATSPACK<\/span><\/h3>\n

O STATSPACK possui 5 op\u00e7\u00f5es de n\u00edvel de coleta de informa\u00e7\u00f5es da inst\u00e2ncia, ao termino da instala\u00e7\u00e3o este valor \u00e9 definido como 5, o n\u00edvel 10 permite que seja feita a coleta do maior n\u00famero de informa\u00e7\u00f5es poss\u00edvel, n\u00e3o recomendamos que seja definido este n\u00edvel na coleta normal do STATSPACK num ambiente de produ\u00e7\u00e3o pois ele vai consumir mais recurso do banco para faze-lo.<\/span><\/p>\n

A defini\u00e7\u00e3o do n\u00edvel 6, na maioria das situa\u00e7\u00f5es, ser\u00e1 suficiente para fazer analise de problemas de desempenho da inst\u00e2ncia, para listar a descri\u00e7\u00e3o de cada um dos n\u00edveis podemos utilizar a tabela “stats$level_description”.<\/span><\/p>\n

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
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span> select *<\/span> from PERFSTAT.<\/span>STATS$LEVEL_DESCRIPTION<\/span>;<\/span>
\n
\nSNAP_LEVEL DESCRIPTION
\n----------<\/span> --------------------------------------------------------------------<\/span>
\n         0<\/span> This level captures general statistics,<\/span> including rollback segment,<\/span>
\n           row cache,<\/span> SGA,<\/span> system<\/span><\/a> events,<\/span> background events,<\/span> session events,<\/span>
\n           system<\/span><\/a> statistics,<\/span> wait statistics,<\/span> lock statistics,<\/span> and Latch
\n           information
\n
\n         5<\/span> This level includes capturing high resource usage SQL Statements,<\/span>
\n           along with all data captured by lower levels
\n
\n         6<\/span> This level includes capturing SQL plan and SQL plan usage
\n           information for<\/span> high resource usage SQL Statements,<\/span> along with all
\n           data captured by lower levels
\n
\n         7<\/span> This level captures segment level statistics,<\/span> including logical and
\n           physical reads,<\/span> row lock,<\/span> itl and buffer busy waits,<\/span> along with all
\n           data captured by lower levels
\n
\n        10<\/span> This level includes capturing Child Latch statistics,<\/span> along with all
\n           data captured by lower levels
\n
\n
\n5<\/span> rows selected.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Voc\u00ea pode alterar o n\u00edvel padr\u00e3o de coleta do STATSPACK utilizando a fun\u00e7\u00e3o STATSPACK.SNAP,\u00a0e se o parametro “i_modify_parameter” for informado como “TRUE” a altera\u00e7\u00e3o no n\u00edvel de coleta\u00a0ser\u00e1 v\u00e1lida para todas as pr\u00f3ximas coletas.<\/span><\/p>\n

1
2
3
4
5
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span> exec<\/span><\/a> statspack.<\/span>snap(<\/span>i_snap_level =><\/span> 6<\/span>,<\/span> i_modify_parameter =><\/span> 'true'<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Programar a coleta de SNAPSHOTS<\/span><\/h3>\n

Feita a configura\u00e7\u00e3o do STATSPACK o pr\u00f3ximo passo \u00e9 programar um JOB\u00a0para coletar um SNAPSHOT a\u00a0cada 30 minutos, o intervalo de tempo entre uma coleta de SNAPSHOT e outra deve ser definido conforme a necessidade de cada instala\u00e7\u00e3o.<\/span><\/p>\n

Para programar a coleta autom\u00e1tica dos SNAPSHOTS vamos utilizar o pacote DBMS_SCHEDULER e para tal precisamos\u00a0conceder o direito para executar o comando “CREATE JOB” ao usu\u00e1rio PERFSTAT. A seguir voc\u00ea pode visualizar um\u00a0exemplo dos comandos:<\/span><\/p>\n

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
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span> grant create job to perfstat;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span> conn perfstat\/<\/span>oracle2015@<\/span>lab11;<\/span>
\nConnected.<\/span>
\nperfstat@<\/span>LAB11 ><\/span> BEGIN
\n  2<\/span>    DBMS_SCHEDULER.<\/span>CREATE_SCHEDULE(<\/span>
\n  3<\/span>     schedule_name =><\/span> 'statspack_snap_30min'<\/span>,<\/span>
\n  4<\/span>     repeat_interval =><\/span> 'FREQ=MINUTELY;BYMINUTE=00,30'<\/span>
\n  5<\/span>    )<\/span>;<\/span>
\n  6<\/span>
\n  7<\/span>    DBMS_SCHEDULER.<\/span>CREATE_JOB (<\/span>
\n  8<\/span>      job_name =><\/span> 'statspack_snapshot'<\/span>,<\/span>
\n  9<\/span>      job_type =><\/span> 'STORED_PROCEDURE'<\/span>,<\/span>
\n 10<\/span>      job_action =><\/span> 'statspack.snap'<\/span>,<\/span>
\n 11<\/span>      schedule_name =><\/span> 'statspack_snap_30min'<\/span>,<\/span>
\n 12<\/span>      auto_drop =><\/span> FALSE<\/span>,<\/span>
\n 13<\/span>      comments =><\/span> 'Statspack collection'<\/span>
\n 14<\/span>    )<\/span>;<\/span>
\n 15<\/span>
\n 16<\/span>    DBMS_SCHEDULER.<\/span>ENABLE(<\/span>'statspack_snapshot'<\/span>)<\/span>;<\/span>
\n 17<\/span>  END<\/span><\/a>;<\/span>
\n 18<\/span>  \/<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nperfstat@<\/span>LAB11 ><\/span> set lines 200<\/span> pages 200<\/span>;<\/span>
\nperfstat@<\/span>LAB11 ><\/span> col JOB_NAME for<\/span> a20;<\/span>
\nperfstat@<\/span>LAB11 ><\/span> col JOB_ACTION for<\/span> a20;<\/span>
\nperfstat@<\/span>LAB11 ><\/span> col SCHEDULE_NAME for<\/span> a20;<\/span>
\nperfstat@<\/span>LAB11 ><\/span> select JOB_NAME,<\/span>
\n  2<\/span>         JOB_TYPE,<\/span>
\n  3<\/span>         JOB_ACTION,<\/span>
\n  4<\/span>         SCHEDULE_NAME,<\/span>
\n  5<\/span>         ENABLED,<\/span>
\n  6<\/span>         AUTO_DROP,<\/span>
\n  7<\/span>         STATE,<\/span>
\n  8<\/span>         TO_CHAR(<\/span>NEXT_RUN_DATE,<\/span>'YYYY-MM-DD HH24:MI'<\/span>)<\/span> as<\/span> NEXT_RUN
\n  9<\/span>    from USER_SCHEDULER_JOBS;<\/span>
\n
\nJOB_NAME             JOB_TYPE         JOB_ACTION           SCHEDULE_NAME        ENABL AUTO_ STATE      NEXT_RUN
\n--------------------<\/span> ----------------<\/span> --------------------<\/span> --------------------<\/span> -----<\/span> -----<\/span> ----------<\/span> ----------------<\/span>
\nSTATSPACK_SNAPSHOT   STORED_PROCEDURE statspack.<\/span>snap       STATSPACK_SNAP_30MIN TRUE<\/span>  FALSE<\/span> SCHEDULED  2015<\/span>-<\/span>11<\/span>-<\/span>19<\/span> 19<\/span>:<\/span>00<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nperfstat@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Observa\u00e7\u00e3o: Caso voc\u00ea esteja configurando o STATSPACK numa base inferior a 10g utilize o script “spauto.sql” \u00a0que se\u00a0encontra no diret\u00f3rio “$ORACLE_HOME\/rdbms\/admin”.<\/span><\/p>\n

Programar a elimina\u00e7\u00e3o dos SNAPSHOTS<\/span><\/h3>\n

Alem de programar a coleta de SNAPSHOTS \u00e9 muito importante programar a elimina\u00e7\u00e3o dos SNAPSHOTS antigos, pois\u00a0se isso n\u00e3o for feito as informa\u00e7\u00f5es coletadas poder\u00e3o consumir todo o espa\u00e7o da tablespace e o processo de coleta vai parar de executar por falta de espa\u00e7o.<\/span><\/p>\n

Defina uma quantidade de dias que deseja manter como hist\u00f3rico de informa\u00e7\u00f5es para gera\u00e7\u00e3o de relat\u00f3rios de analise de desempenho e elimine os SNAPSHOTS que forem mais velhos que o per\u00edodo definido, no exemplo a seguir\u00a0vamos programar a execu\u00e7\u00e3o de um JOB todos os dias\u00a0as 04:30 hs. e a fun\u00e7\u00e3o PURGE vai manter SNAPSHOTS com informa\u00e7\u00f5es\u00a0<\/span>por 8 dias, ou seja todos os SNAPSHOTS que foram criados a mais de oito dias ser\u00e3o eliminados.<\/span><\/p>\n

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
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span> conn PERFSTAT\/<\/span>oracle2015@<\/span>lab11
\nConnected.<\/span>
\nperfstat@<\/span>LAB11 ><\/span> BEGIN
\n  2<\/span>    DBMS_SCHEDULER.<\/span>CREATE_JOB (<\/span>
\n  3<\/span>      job_name            =><\/span> 'statspack_purge_snapshots'<\/span>,<\/span>
\n  4<\/span>      job_type             =><\/span> 'PLSQL_BLOCK'<\/span>,<\/span>
\n  5<\/span>      job_action           =><\/span> 'STATSPACK.PURGE(I_NUM_DAYS => 8);'<\/span>,<\/span>
\n  6<\/span>      repeat_interval     =><\/span> 'FREQ=DAILY; BYHOUR=04; BYMINUTE=30'<\/span>,<\/span>
\n  7<\/span>      auto_drop           =><\/span> FALSE<\/span>,<\/span>
\n  8<\/span>      comments            =><\/span> 'Statspack snapshots purge'<\/span>
\n  9<\/span>    )<\/span>;<\/span>
\n 10<\/span>    DBMS_SCHEDULER.<\/span>ENABLE(<\/span>'statspack_purge_snapshots'<\/span>)<\/span>;<\/span>
\n 11<\/span>  END<\/span><\/a>;<\/span>
\n 12<\/span>  \/<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nperfstat@<\/span>LAB11 ><\/span> set lines 200<\/span> pages 200<\/span>;<\/span>
\nperfstat@<\/span>LAB11 ><\/span> col JOB_NAME for<\/span> a30;<\/span>
\nperfstat@<\/span>LAB11 ><\/span> col JOB_ACTION for<\/span> a35;<\/span>
\nperfstat@<\/span>LAB11 ><\/span> select JOB_NAME,<\/span>
\n  2<\/span>         JOB_TYPE,<\/span>
\n  3<\/span>         JOB_ACTION,<\/span>
\n  4<\/span>         ENABLED,<\/span>
\n  5<\/span>         AUTO_DROP,<\/span>
\n  6<\/span>         STATE,<\/span>
\n  7<\/span>         TO_CHAR(<\/span>NEXT_RUN_DATE,<\/span>'YYYY-MM-DD HH24:MI'<\/span>)<\/span> as<\/span> NEXT_RUN
\n  8<\/span>    from USER_SCHEDULER_JOBS;<\/span>
\n
\nJOB_NAME                       JOB_TYPE             JOB_ACTION                          ENABLED    AUTO_ STATE           NEXT_RUN
\n------------------------------<\/span> --------------------<\/span> -----------------------------------<\/span> ----------<\/span> -----<\/span> ---------------<\/span> ----------------<\/span>
\nSTATSPACK_SNAPSHOT             STORED_PROCEDURE     statspack.<\/span>snap                      TRUE<\/span>       FALSE<\/span> SCHEDULED       2015<\/span>-<\/span>11<\/span>-<\/span>19<\/span> 21<\/span>:<\/span>00<\/span>
\nSTATSPACK_PURGE_SNAPSHOTS      PLSQL_BLOCK          STATSPACK.<\/span>PURGE(<\/span>I_NUM_DAYS =><\/span> 8<\/span>)<\/span>;<\/span>   TRUE<\/span>       FALSE<\/span> SCHEDULED       2015<\/span>-<\/span>11<\/span>-<\/span>20<\/span> 04<\/span>:<\/span>30<\/span>
\n
\n2<\/span> rows selected.<\/span>
\n
\nperfstat@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Gerar um relat\u00f3rio STATSPACK<\/span><\/h3>\n

A gera\u00e7\u00e3o do relat\u00f3rio STATSPACK e muito simples, basta excutar o script “spreport.sql” que\u00a0<\/span>pode ser encontrado no diret\u00f3rio “$ORACLE_HOME\/rdbms\/admin”, durante a execu\u00e7\u00e3o do script ser\u00e3o\u00a0solicitadas tr\u00eas informa\u00e7\u00f5es: o SNAP_ID inicial, o SNAP_ID final e um nome para o relat\u00f3rio.<\/span><\/p>\n

O SNAP_ID inicial e SNAP_ID final corresponde ao intervalo de tempo para o qual o relat\u00f3rio <\/span>STATSPACK ser\u00e1 gerado, ou seja, todas as atividades da inst\u00e2ncia nesse per\u00edodo de tempo ser\u00e3o\u00a0reportadas em forma de estat\u00edsticas para analise.<\/span><\/p>\n

Antes de solicitar a informa\u00e7\u00e3o do SNAP_ID inicial o script vai fornecer uma lista de todos os SNAP_ID e suas respectivas datas e horas de gera\u00e7\u00e3o, escolha os SNAP_ID de acordo com o per\u00edodo que deseja que seja gerado o relat\u00f3rio.<\/span><\/p>\n

A seguir podemos ver um exemplo de gera\u00e7\u00e3o do relat\u00f3rio STATSPACK:<\/span><\/p>\n

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
<\/div><\/td>
[<\/span>oracle@<\/span>odbsrv11 ~]<\/span>$ cd $ORACLE_HOME<\/span>\/<\/span>rdbms\/<\/span>admin
\n[<\/span>oracle@<\/span>odbsrv11 admin]<\/span>$ ls -<\/span>l |<\/span> grep spreport
\n-<\/span>rw-<\/span>r--<\/span>r--<\/span> 1<\/span> oracle oinstall    1284<\/span> Apr 23<\/span>  2001<\/span> spreport.<\/span>sql
\n[<\/span>oracle@<\/span>odbsrv11 admin]<\/span>$ sqlplus perfstat\/<\/span>oracle2015
\n
\nSQL*<\/span>Plus:<\/span> Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 Production on Thu Nov 19<\/span> 22<\/span>:<\/span>20<\/span>:<\/span>40<\/span> 2015<\/span>
\n
\nCopyright (<\/span>c)<\/span> 1982<\/span>,<\/span> 2013<\/span>,<\/span> Oracle.<\/span>  All rights reserved.<\/span>
\n
\n
\nConnected to:<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> Production
\nWith the Partitioning,<\/span> OLAP,<\/span> Data Mining and Real Application Testing options
\n
\nperfstat@<\/span>LAB11 ><\/span> @<\/span>spreport.<\/span>sql
\n
\nCurrent<\/span><\/a> Instance
\n~~~~~~~~~~~~~~~~
\n
\n   DB Id    DB Name      Inst Num Instance
\n-----------<\/span> ------------<\/span> --------<\/span> ------------<\/span>
\n   78772905<\/span> LAB11               1<\/span> lab11
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span>
\n
\n
\nInstances in this Statspack schema
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\n
\n   DB Id    Inst Num DB Name      Instance     Host
\n-----------<\/span> --------<\/span> ------------<\/span> ------------<\/span> ------------<\/span>
\n   78772905<\/span>        1<\/span> LAB11        lab11        odbsrv11.<\/span>loc
\n                                               alhost
\n
\nUsing   78772905<\/span> for<\/span> database Id
\nUsing          1<\/span> for<\/span> instance number
\n
\n
\nSpecify the number of days of snapshots to choose from
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\nEntering the number of days (<\/span>n)<\/span> will result in the most recent
\n(<\/span>n)<\/span> days of snapshots being listed.<\/span>  Pressing <<\/span>return><\/span> without
\nspecifying a number lists all completed snapshots.<\/span>
\n
\n
\n
\nListing all Completed Snapshots
\n
\n                                                       Snap
\nInstance     DB Name        Snap Id   Snap Started    Level Comment
\n------------<\/span> ------------<\/span> ---------<\/span> -----------------<\/span> -----<\/span> --------------------<\/span>
\nlab11        LAB11                1<\/span> 19<\/span> Nov 2015<\/span> 19<\/span>:<\/span>16<\/span>     6<\/span>
\n                                 11<\/span> 19<\/span> Nov 2015<\/span> 19<\/span>:<\/span>30<\/span>     6<\/span>
\n                                 12<\/span> 19<\/span> Nov 2015<\/span> 20<\/span>:<\/span>00<\/span>     6<\/span>
\n                                 13<\/span> 19<\/span> Nov 2015<\/span> 20<\/span>:<\/span>30<\/span>     6<\/span>
\n                                 14<\/span> 19<\/span> Nov 2015<\/span> 21<\/span>:<\/span>00<\/span>     6<\/span>
\n                                 15<\/span> 19<\/span> Nov 2015<\/span> 21<\/span>:<\/span>30<\/span>     6<\/span>
\n                                 16<\/span> 19<\/span> Nov 2015<\/span> 22<\/span>:<\/span>00<\/span>     6<\/span>
\n
\n
\n
\nSpecify the Begin and End<\/span><\/a> Snapshot Ids
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\nEnter value for<\/span> begin_snap:<\/span> 12<\/span>
\nBegin Snapshot Id specified:<\/span> 12<\/span>
\n
\nEnter value for<\/span> end_snap:<\/span> 14<\/span>
\nEnd<\/span><\/a>   Snapshot Id specified:<\/span> 14<\/span>
\n
\n
\n
\nSpecify the Report Name
\n~~~~~~~~~~~~~~~~~~~~~~~
\nThe default<\/span> report file<\/span><\/a> name is sp_12_14.<\/span>  To use<\/span> this name,<\/span>
\npress <<\/span>return><\/span> to continue<\/span>,<\/span> otherwise enter an alternative.<\/span>
\n
\nEnter value for<\/span> report_name:<\/span> report_statspack_20151119_20_21.<\/span>txt
\n
\nUsing the report name report_statspack_20151119_20_21.<\/span>txt
\n
\nSTATSPACK report for<\/span>
\n
\n.....<\/span>
\n.....<\/span>
\n.....<\/span>
\n
\nEnd<\/span><\/a> of Report (<\/span> report_statspack_20151119_20_21.<\/span>txt )<\/span>
\n
\nperfstat@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Gerar um plano de execu\u00e7\u00e3o<\/span><\/h3>\n

Caso voc\u00ea identifique uma ou mais instru\u00e7\u00e3o SQL e queira analisar o plano de execu\u00e7\u00e3o,\u00a0<\/span>\u00e9 poss\u00edvel gerar o plano de execu\u00e7\u00e3o utilizando o script “sprepsql.sql” que se\u00a0<\/span>encontra no diret\u00f3rio “$ORACLE_HOME\/rdbms\/admin” e durante sua execu\u00e7\u00e3o ser\u00e1 solicitada\u00a0<\/span>a identifica\u00e7\u00e3o da instru\u00e7\u00e3o SQL (Old Hash Value) que \u00e9 informada no relat\u00f3rio STATSPACK.<\/span><\/p>\n

A seguir um exemplo de extra\u00e7\u00e3o desse relat\u00f3rio:<\/span><\/p>\n

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
<\/div><\/td>
[<\/span>oracle@<\/span>odbsrv11 ~]<\/span>$ cd $ORACLE_HOME<\/span>\/<\/span>rdbms\/<\/span>admin
\n[<\/span>oracle@<\/span>odbsrv11 admin]<\/span>$ sqlplus \/<\/span> as<\/span> sysdba
\n
\nSQL*<\/span>Plus:<\/span> Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 Production on Thu Nov 19<\/span> 22<\/span>:<\/span>35<\/span>:<\/span>43<\/span> 2015<\/span>
\n
\nCopyright (<\/span>c)<\/span> 1982<\/span>,<\/span> 2013<\/span>,<\/span> Oracle.<\/span>  All rights reserved.<\/span>
\n
\n
\nConnected to:<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\nWith the Partitioning,<\/span> Real Application Clusters,<\/span> Automatic Storage Management,<\/span> OLAP,<\/span>
\nData Mining and Real Application Testing options
\n
\nSQL><\/span> @<\/span>sprepsql.<\/span>sql
\n
\nCurrent<\/span><\/a> Instance
\n~~~~~~~~~~~~~~~~
\n
\n   DB Id    DB Name      Inst Num Instance
\n-----------<\/span> ------------<\/span> --------<\/span> ------------<\/span>
\n 2991626347<\/span> LAB11               1<\/span> LAB11  
\n
\n
\n
\nInstances in this Statspack schema
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\n
\n   DB Id    Inst Num DB Name      Instance     Host
\n-----------<\/span> --------<\/span> ------------<\/span> ------------<\/span> ------------<\/span>
\n 2991626347<\/span>        1<\/span> LAB11        LAB11        odbsrv11
\n                                               
\nUsing 2991626347<\/span> for<\/span> database Id
\nUsing          1<\/span> for<\/span> instance number
\n
\n
\nSpecify the number of days of snapshots to choose from
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\nEntering the number of days (<\/span>n)<\/span> will result in the most recent
\n(<\/span>n)<\/span> days of snapshots being listed.<\/span>  Pressing <<\/span>return><\/span> without
\nspecifying a number lists all completed snapshots.<\/span>
\n
\n
\n
\nListing all Completed Snapshots
\n
\n                               Snap                    Snap
\nInstance     DB Name             Id   Snap Started    Level Comment
\n------------<\/span> ------------<\/span> ---------<\/span> -----------------<\/span> -----<\/span> --------------------<\/span>
\nLAB11  1<\/span>     LAB11                1<\/span> 12<\/span> Nov 2015<\/span> 17<\/span>:<\/span>13<\/span>     7<\/span>
\n                                  2<\/span> 12<\/span> Nov 2015<\/span> 18<\/span>:<\/span>13<\/span>     7<\/span>
\n                                  3<\/span> 12<\/span> Nov 2015<\/span> 19<\/span>:<\/span>13<\/span>     7<\/span>
\n                                  4<\/span> 12<\/span> Nov 2015<\/span> 20<\/span>:<\/span>13<\/span>     7<\/span>
\n                                  5<\/span> 12<\/span> Nov 2015<\/span> 21<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 11<\/span> 12<\/span> Nov 2015<\/span> 22<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 12<\/span> 12<\/span> Nov 2015<\/span> 23<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 13<\/span> 13<\/span> Nov 2015<\/span> 00<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 14<\/span> 13<\/span> Nov 2015<\/span> 01<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 15<\/span> 13<\/span> Nov 2015<\/span> 02<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 16<\/span> 13<\/span> Nov 2015<\/span> 03<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 17<\/span> 13<\/span> Nov 2015<\/span> 04<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 18<\/span> 13<\/span> Nov 2015<\/span> 05<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 19<\/span> 13<\/span> Nov 2015<\/span> 06<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 20<\/span> 13<\/span> Nov 2015<\/span> 07<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 21<\/span> 13<\/span> Nov 2015<\/span> 08<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 22<\/span> 13<\/span> Nov 2015<\/span> 09<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 23<\/span> 13<\/span> Nov 2015<\/span> 10<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 24<\/span> 13<\/span> Nov 2015<\/span> 11<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 25<\/span> 13<\/span> Nov 2015<\/span> 12<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 26<\/span> 13<\/span> Nov 2015<\/span> 13<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 27<\/span> 13<\/span> Nov 2015<\/span> 14<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 28<\/span> 13<\/span> Nov 2015<\/span> 15<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 29<\/span> 13<\/span> Nov 2015<\/span> 16<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 30<\/span> 13<\/span> Nov 2015<\/span> 17<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 31<\/span> 13<\/span> Nov 2015<\/span> 18<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 32<\/span> 13<\/span> Nov 2015<\/span> 19<\/span>:<\/span>13<\/span>     7<\/span>
\n                                 33<\/span> 13<\/span> Nov 2015<\/span> 20<\/span>:<\/span>13<\/span>     7<\/span>
\n 
\n
\n
\nSpecify the Begin and End<\/span><\/a> Snapshot Ids
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\nEnter value for<\/span> begin_snap:<\/span> 23<\/span>
\nBegin Snapshot Id specified:<\/span> 23<\/span>
\n
\nEnter value for<\/span> end_snap:<\/span> 25<\/span>
\nEnd<\/span><\/a>   Snapshot Id specified:<\/span> 25<\/span>
\n
\n
\n
\nSpecify the old (<\/span>i.<\/span>e.<\/span> pre-<\/span>10g)<\/span> Hash<\/span><\/a> Value
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\nEnter value for<\/span> hash_value:<\/span> 116748235<\/span>
\nHash<\/span><\/a> Value specified is:<\/span> 116748235<\/span>
\n
\n
\n
\nSpecify the Report Name
\n~~~~~~~~~~~~~~~~~~~~~~~
\nThe default<\/span> report file<\/span><\/a> name is sp_23_25_116748235.<\/span>  To use<\/span> this name,<\/span>
\npress <<\/span>return><\/span> to continue<\/span>,<\/span> otherwise enter an alternative.<\/span>
\n
\nEnter value for<\/span> report_name:<\/span> statsapck_report_23_25_116748235
\n
\nUsing the report name statsapck_report_23_25_116748235
\n
\n
\nSTATSPACK SQL report for<\/span> Old Hash<\/span><\/a> Value:<\/span> 116748235<\/span>  Module:<\/span> SQL*<\/span>Plus
\n
\nDB Name         DB Id    Instance     Inst Num Release     RAC Host
\n------------<\/span> -----------<\/span> ------------<\/span> --------<\/span> -----------<\/span> ---<\/span> ----------------<\/span>
\nLAB11         2991626347<\/span> LAB11               1<\/span> 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0  YES odbsrv11
\n
\n Start Id     Start Time<\/span><\/a>         End<\/span><\/a> Id      End<\/span><\/a> Time<\/span><\/a>       Duration(<\/span>mins)<\/span>
\n---------<\/span> -------------------<\/span> ---------<\/span> -------------------<\/span> --------------<\/span>
\n       23<\/span> 13<\/span>-<\/span>Nov-<\/span>15<\/span> 10<\/span>:<\/span>13<\/span>:<\/span>06<\/span>         25<\/span> 13<\/span>-<\/span>Nov-<\/span>15<\/span> 12<\/span>:<\/span>13<\/span>:<\/span>06<\/span>          120.00<\/span>
\n
\nSQL Statistics
\n~~~~~~~~~~~~~~
\n-><\/span> CPU<\/span> and Elapsed Time<\/span><\/a> are in seconds (<\/span>s)<\/span> for<\/span> Statement Total and in
\n   milliseconds (<\/span>ms)<\/span> for<\/span> Per Execute
\n                                                       %<\/span> Snap
\n                     Statement Total      Per Execute   Total
\n                     ---------------<\/span>  ---------------<\/span>  ------<\/span>
\n        Buffer Gets:<\/span>                  1<\/span>,<\/span>177<\/span>,<\/span>852<\/span>         65<\/span>,<\/span>436.2<\/span>    5.06<\/span>
\n         Disk Reads:<\/span>                    190<\/span>,<\/span>545<\/span>         10<\/span>,<\/span>585.8<\/span>   14.20<\/span>
\n     Rows processed:<\/span>                         18<\/span>              1.0<\/span>
\n     CPU Time<\/span><\/a>(<\/span>s\/<\/span>ms)<\/span>:<\/span>                         28<\/span>          1<\/span>,<\/span>577.4<\/span>
\n Elapsed Time<\/span><\/a>(<\/span>s\/<\/span>ms)<\/span>:<\/span>                        422<\/span>         23<\/span>,<\/span>420.7<\/span>
\n              Sorts:<\/span>                         -<\/span>1<\/span>              -<\/span>.1<\/span>
\n        Parse Calls:<\/span>                         18<\/span>              1.0<\/span>
\n      Invalidations:<\/span>                          0<\/span>
\n      Version count<\/span><\/a>:<\/span>                          2<\/span>
\n    Sharable Mem(<\/span>K)<\/span>:<\/span>                        361<\/span>
\n         Executions:<\/span>                         18<\/span>
\n
\nSQL Text
\n~~~~~~~~
\nSELECT :<\/span>"SYS_B_00"<\/span>||<\/span>MAX<\/span><\/a>(<\/span>ROUND<\/span><\/a>(<\/span>(<\/span> SUM (<\/span>total_mb)<\/span>-<\/span> SUM (<\/span>free_mb)<\/span>)<\/span>\/<\/span>
\nSUM (<\/span>max_mb)<\/span>*:<\/span>"SYS_B_01"<\/span>)<\/span>)<\/span> PCT FROM (<\/span>    SELECT tablespace_name,<\/span>
\n SUM (<\/span>bytes)<\/span>\/:<\/span>"SYS_B_02"<\/span>\/:<\/span>"SYS_B_03"<\/span> FREE_MB,<\/span>    :<\/span>"SYS_B_04"<\/span> TOT
\nAL_MB,<\/span> :<\/span>"SYS_B_05"<\/span> MAX_MB    FROM dba_free_space    GROUP BY tab
\nlespace_name    UNION ALL    SELECT tablespace_name,<\/span> :<\/span>"SYS_B_06"<\/span>
\n CURRENT_MB,<\/span>    SUM (<\/span>bytes)<\/span>\/:<\/span>"SYS_B_07"<\/span>\/:<\/span>"SYS_B_08"<\/span> TOTAL_MB,<\/span>
\n SUM (<\/span> DECODE (<\/span>maxbytes,:<\/span>"SYS_B_09"<\/span>,<\/span>bytes,<\/span> maxbytes)<\/span>)<\/span>\/:<\/span>"SYS_B_10
\n"<\/span>\/:<\/span>"SYS_B_11"<\/span> MAX_MB    FROM dba_data_files    GROUP BY tablespa
\nce_name)<\/span> GROUP BY tablespace_name
\n
\nKnown Optimizer Plan(<\/span>s)<\/span> for<\/span> this Old Hash<\/span><\/a> Value
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\nShows all known Optimizer Plans for<\/span> this database instance,<\/span> and the Snap Id's
\nthey were first found in the shared pool.  A Plan Hash Value will appear
\nmultiple times if the cost has changed
\n-> ordered by Snap Id
\n
\n  First        First           Last           Plan
\n Snap Id     Snap Time      Active Time    Hash Value        Cost
\n--------- --------------- --------------- ------------ ----------
\n        1 12-Nov-15 17:13 18-Nov-15 00:12    874526296       112
\n       11 12-Nov-15 22:13 12-Nov-15 21:33   3155862605       106
\n       15 13-Nov-15 02:13 17-Nov-15 21:34   3933858862       112
\n      164 18-Nov-15 01:13 19-Nov-15 02:42   2569970882       114
\n      164 18-Nov-15 01:13 19-Nov-15 22:12   3933858862       114
\n
\nPlans in shared pool between Begin and End Snap Ids
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\nShows the Execution Plans found in the shared pool between the begin and end
\nsnapshots specified.  The values for Rows, Bytes and Cost shown below are those
\nwhich existed at the time the first-ever snapshot captured this plan - these
\nvalues often change over time, and so may not be indicative of current values
\n-> Rows indicates Cardinality, PHV is Plan Hash Value
\n-> ordered by Plan Hash Value
\n
\n--------------------------------------------------------------------------------
\n| Operation                      | PHV\/Object Name     |  Rows | Bytes|   Cost |
\n--------------------------------------------------------------------------------
\n|SELECT STATEMENT                |----- 874526296 -----|       |      |    112 |
\n|SORT AGGREGATE                  |                     |     1 |   49 |    112 |
\n| HASH GROUP BY                  |                     |     1 |   49 |    112 |
\n|  VIEW                          |                     |    98 |    4K|    111 |
\n|   UNION-ALL                    |                     |       |      |        |
\n|    HASH GROUP BY               |                     |    96 |    2K|    106 |
\n|     VIEW                       |DBA_FREE_SPACE       |     7K|  176K|    105 |
\n|      UNION-ALL                 |                     |       |      |        |
\n|       NESTED LOOPS             |                     |     1 |   67 |      1 |
\n|        NESTED LOOPS            |                     |     1 |   46 |      1 |
\n|         INDEX FULL SCAN        |I_FILE2              |    95 |  665 |      1 |
\n|         TABLE ACCESS CLUSTER   |FET$                 |     1 |   39 |      0 |
\n|          INDEX UNIQUE SCAN     |I_TS#                |     1 |      |      0 |
\n|        TABLE ACCESS CLUSTER    |TS$                  |     1 |   21 |      0 |
\n|         INDEX UNIQUE SCAN      |I_TS#                |     1 |      |      0 |
\n|       NESTED LOOPS             |                     |    98 |    6K|     29 |
\n|        NESTED LOOPS            |                     |    98 |    6K|     29 |
\n|         TABLE ACCESS FULL      |TS$                  |    96 |    2K|     29 |
\n|         FIXED TABLE FIXED INDEX|X$KTFBFE (ind:1)     |     1 |   39 |      0 |
\n|        INDEX UNIQUE SCAN       |I_FILE2              |     1 |    7 |      0 |
\n|       HASH JOIN                |                     |     7K|  819K|     49 |
\n|        HASH JOIN               |                     |   119 |    4K|     35 |
\n|         HASH JOIN              |                     |   120 |    1K|      6 |
\n|          INDEX FULL SCAN       |I_FILE2              |    95 |  665 |      1 |
\n|          TABLE ACCESS FULL     |RECYCLEBIN$          |   120 |    1K|      5 |
\n|         TABLE ACCESS FULL      |TS$                  |    96 |    2K|     29 |
\n|        FIXED TABLE FULL        |X$KTFBUE             |   100K|    6M|     14 |
\n|       NESTED LOOPS             |                     |     1 |   89 |     26 |
\n|        NESTED LOOPS            |                     |    30 |   89 |     26 |
\n|         NESTED LOOPS           |                     |     1 |   80 |     23 |
\n|          NESTED LOOPS          |                     |     1 |   73 |     23 |
\n|           TABLE ACCESS FULL    |UET$                 |     1 |   52 |     23 |
\n|           TABLE ACCESS CLUSTER |TS$                  |     1 |   21 |      0 |
\n|            INDEX UNIQUE SCAN   |I_TS#                |     1 |      |      0 |
\n|          INDEX UNIQUE SCAN     |I_FILE2              |     1 |    7 |      0 |
\n|         INDEX RANGE SCAN       |RECYCLEBIN$_TS       |    30 |      |      1 |
\n|        TABLE ACCESS BY INDEX RO|RECYCLEBIN$          |     1 |    9 |      3 |
\n|    HASH GROUP BY               |                     |     2 |   72 |      5 |
\n|     VIEW                       |DBA_DATA_FILES       |     2 |   72 |      4 |
\n|      UNION-ALL                 |                     |       |      |        |
\n|       NESTED LOOPS             |                     |     1 |  361 |      2 |
\n|        NESTED LOOPS            |                     |     1 |  344 |      1 |
\n|         NESTED LOOPS           |                     |     1 |  331 |      1 |
\n|          FIXED TABLE FULL      |X$KCCFN              |     1 |  310 |      0 |
\n|          TABLE ACCESS BY INDEX |FILE$                |     1 |   21 |      1 |
\n|           INDEX UNIQUE SCAN    |I_FILE1              |     1 |      |      0 |
\n|         FIXED TABLE FIXED INDEX|X$KCCFE (ind:1)      |     1 |   13 |      0 |
\n
\nPlans in shared pool between Begin and End Snap Ids
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\nShows the Execution Plans found in the shared pool between the begin and end
\nsnapshots specified.  The values for Rows, Bytes and Cost shown below are those
\nwhich existed at the time the first-ever snapshot captured this plan - these
\nvalues often change over time, and so may not be indicative of current values
\n-> Rows indicates Cardinality, PHV is Plan Hash Value
\n-> ordered by Plan Hash Value
\n
\n|        TABLE ACCESS CLUSTER    |TS$                  |     1 |   17 |      1 |
\n|         INDEX UNIQUE SCAN      |I_TS#                |     1 |      |      0 |
\n|       NESTED LOOPS             |                     |     1 |  412 |      2 |
\n|        NESTED LOOPS            |                     |     1 |  399 |      2 |
\n|         NESTED LOOPS           |                     |     1 |  392 |      1 |
\n|          NESTED LOOPS          |                     |     1 |  375 |      0 |
\n|           FIXED TABLE FULL     |X$KCCFN              |     1 |  310 |      0 |
\n|           FIXED TABLE FIXED IND|X$KTFBHC (ind:1)     |     1 |   65 |      0 |
\n|          TABLE ACCESS CLUSTER  |TS$                  |     1 |   17 |      1 |
\n|           INDEX UNIQUE SCAN    |I_TS#                |     1 |      |      0 |
\n|         TABLE ACCESS BY INDEX R|FILE$                |     1 |    7 |      1 |
\n|          INDEX UNIQUE SCAN     |I_FILE1              |     1 |      |      0 |
\n|        FIXED TABLE FIXED INDEX |X$KCCFE (ind:1)      |     1 |   13 |      0 |
\n|SELECT STATEMENT                |----- 3155862605 ----|       |      |    106 |
\n|SORT AGGREGATE                  |                     |     1 |   49 |    106 |
\n| HASH GROUP BY                  |                     |     1 |   49 |    106 |
\n|  VIEW                          |                     |    98 |    4K|    105 |
\n|   UNION-ALL                    |                     |       |      |        |
\n|    HASH GROUP BY               |                     |    96 |    2K|    100 |
\n|     VIEW                       |DBA_FREE_SPACE       |     3K|   74K|     99 |
\n|      UNION-ALL                 |                     |       |      |        |
\n|       NESTED LOOPS             |                     |     1 |   67 |      1 |
\n|        NESTED LOOPS            |                     |     1 |   46 |      1 |
\n|         INDEX FULL SCAN        |I_FILE2              |    95 |  665 |      1 |
\n|         TABLE ACCESS CLUSTER   |FET$                 |     1 |   39 |      0 |
\n|          INDEX UNIQUE SCAN     |I_TS#                |     1 |      |      0 |
\n|        TABLE ACCESS CLUSTER    |TS$                  |     1 |   21 |      0 |
\n|         INDEX UNIQUE SCAN      |I_TS#                |     1 |      |      0 |
\n|       NESTED LOOPS             |                     |    98 |    6K|     29 |
\n|        NESTED LOOPS            |                     |    98 |    6K|     29 |
\n|         TABLE ACCESS FULL      |TS$                  |    96 |    2K|     29 |
\n|         FIXED TABLE FIXED INDEX|X$KTFBFE (ind:1)     |     1 |   39 |      0 |
\n|        INDEX UNIQUE SCAN       |I_FILE2              |     1 |    7 |      0 |
\n|       NESTED LOOPS             |                     |     3K|  341K|     49 |
\n|        HASH JOIN               |                     |    25K|    2M|     48 |
\n|         HASH JOIN              |                     |    50 |    1K|     34 |
\n|          TABLE ACCESS FULL     |RECYCLEBIN$          |    50 |  450 |      5 |
\n|          TABLE ACCESS FULL     |TS$                  |    96 |    2K|     29 |
\n|         FIXED TABLE FULL       |X$KTFBUE             |   100K|    6M|     14 |
\n|        INDEX UNIQUE SCAN       |I_FILE2              |     1 |    7 |      0 |
\n|       NESTED LOOPS             |                     |     1 |   89 |     20 |
\n|        NESTED LOOPS            |                     |     1 |   68 |     20 |
\n|         MERGE JOIN CARTESIAN   |                     |     4K|   74K|     20 |
\n|          TABLE ACCESS FULL     |RECYCLEBIN$          |    50 |  450 |      5 |
\n|          BUFFER SORT           |                     |    95 |  665 |     15 |
\n|           INDEX FAST FULL SCAN |I_FILE2              |    95 |  665 |      0 |
\n|         TABLE ACCESS CLUSTER   |UET$                 |     1 |   52 |      0 |
\n|          INDEX UNIQUE SCAN     |I_FILE#_BLOCK#       |     1 |      |      0 |
\n|        TABLE ACCESS CLUSTER    |TS$                  |     1 |   21 |      0 |
\n|         INDEX UNIQUE SCAN      |I_TS#                |     1 |      |      0 |
\n
\nPlans in shared pool between Begin and End Snap Ids
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\nShows the Execution Plans found in the shared pool between the begin and end
\nsnapshots specified.  The values for Rows, Bytes and Cost shown below are those
\nwhich existed at the time the first-ever snapshot captured this plan - these
\nvalues often change over time, and so may not be indicative of current values
\n-> Rows indicates Cardinality, PHV is Plan Hash Value
\n-> ordered by Plan Hash Value
\n
\n|    HASH GROUP BY               |                     |     2 |   72 |      5 |
\n|     VIEW                       |DBA_DATA_FILES       |     2 |   72 |      4 |
\n|      UNION-ALL                 |                     |       |      |        |
\n|       NESTED LOOPS             |                     |     1 |  361 |      2 |
\n|        NESTED LOOPS            |                     |     1 |  344 |      1 |
\n|         NESTED LOOPS           |                     |     1 |  331 |      1 |
\n|          FIXED TABLE FULL      |X$KCCFN              |     1 |  310 |      0 |
\n|          TABLE ACCESS BY INDEX |FILE$                |     1 |   21 |      1 |
\n|           INDEX UNIQUE SCAN    |I_FILE1              |     1 |      |      0 |
\n|         FIXED TABLE FIXED INDEX|X$KCCFE (ind:1)      |     1 |   13 |      0 |
\n|        TABLE ACCESS CLUSTER    |TS$                  |     1 |   17 |      1 |
\n|         INDEX UNIQUE SCAN      |I_TS#                |     1 |      |      0 |
\n|       NESTED LOOPS             |                     |     1 |  412 |      2 |
\n|        NESTED LOOPS            |                     |     1 |  399 |      2 |
\n|         NESTED LOOPS           |                     |     1 |  392 |      1 |
\n|          NESTED LOOPS          |                     |     1 |  375 |      0 |
\n|           FIXED TABLE FULL     |X$KCCFN              |     1 |  310 |      0 |
\n|           FIXED TABLE FIXED IND|X$KTFBHC (ind:1)     |     1 |   65 |      0 |
\n|          TABLE ACCESS CLUSTER  |TS$                  |     1 |   17 |      1 |
\n|           INDEX UNIQUE SCAN    |I_TS#                |     1 |      |      0 |
\n|         TABLE ACCESS BY INDEX R|FILE$                |     1 |    7 |      1 |
\n|          INDEX UNIQUE SCAN     |I_FILE1              |     1 |      |      0 |
\n|        FIXED TABLE FIXED INDEX |X$KCCFE (ind:1)      |     1 |   13 |      0 |
\n|SELECT STATEMENT                |----- 3933858862 ----|       |      |    112 |
\n|SORT AGGREGATE                  |                     |     1 |   49 |    112 |
\n| HASH GROUP BY                  |                     |     1 |   49 |    112 |
\n|  VIEW                          |                     |    98 |    4K|    111 |
\n|   UNION-ALL                    |                     |       |      |        |
\n|    HASH GROUP BY               |                     |    96 |    2K|    106 |
\n|     VIEW                       |DBA_FREE_SPACE       |     3K|   72K|    105 |
\n|      UNION-ALL                 |                     |       |      |        |
\n|       NESTED LOOPS             |                     |     1 |   67 |      1 |
\n|        NESTED LOOPS            |                     |     1 |   46 |      1 |
\n|         INDEX FULL SCAN        |I_FILE2              |    95 |  665 |      1 |
\n|         TABLE ACCESS CLUSTER   |FET$                 |     1 |   39 |      0 |
\n|          INDEX UNIQUE SCAN     |I_TS#                |     1 |      |      0 |
\n|        TABLE ACCESS CLUSTER    |TS$                  |     1 |   21 |      0 |
\n|         INDEX UNIQUE SCAN      |I_TS#                |     1 |      |      0 |
\n|       NESTED LOOPS             |                     |    98 |    6K|     29 |
\n|        NESTED LOOPS            |                     |    98 |    6K|     29 |
\n|         TABLE ACCESS FULL      |TS$                  |    96 |    2K|     29 |
\n|         FIXED TABLE FIXED INDEX|X$KTFBFE (ind:1)     |     1 |   39 |      0 |
\n|        INDEX UNIQUE SCAN       |I_FILE2              |     1 |    7 |      0 |
\n|       NESTED LOOPS             |                     |     3K|  335K|     49 |
\n|        HASH JOIN               |                     |    25K|    2M|     48 |
\n|         HASH JOIN              |                     |    77 |    2K|     34 |
\n|          TABLE ACCESS FULL     |RECYCLEBIN$          |    77 |  770 |      5 |
\n|          TABLE ACCESS FULL     |TS$                  |    96 |    2K|     29 |
\n|         FIXED TABLE FULL       |X$KTFBUE             |   100K|    6M|     14 |
\n|        INDEX UNIQUE SCAN       |I_FILE2              |     1 |    7 |      0 |
\n
\nPlans in shared pool between Begin and End Snap Ids
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\nShows the Execution Plans found in the shared pool between the begin and end
\nsnapshots specified.  The values for Rows, Bytes and Cost shown below are those
\nwhich existed at the time the first-ever snapshot captured this plan - these
\nvalues often change over time, and so may not be indicative of current values
\n-> Rows indicates Cardinality, PHV is Plan Hash Value
\n-> ordered by Plan Hash Value
\n
\n|       NESTED LOOPS             |                     |     1 |   90 |     26 |
\n|        NESTED LOOPS            |                     |    36 |   90 |     26 |
\n|         NESTED LOOPS           |                     |     1 |   80 |     23 |
\n|          NESTED LOOPS          |                     |     1 |   73 |     23 |
\n|           TABLE ACCESS FULL    |UET$                 |     1 |   52 |     23 |
\n|           TABLE ACCESS CLUSTER |TS$                  |     1 |   21 |      0 |
\n|            INDEX UNIQUE SCAN   |I_TS#                |     1 |      |      0 |
\n|          INDEX UNIQUE SCAN     |I_FILE2              |     1 |    7 |      0 |
\n|         INDEX RANGE SCAN       |RECYCLEBIN$_TS       |    36 |      |      1 |
\n|        TABLE ACCESS BY INDEX RO|RECYCLEBIN$          |     1 |   10 |      3 |
\n|    HASH GROUP BY               |                     |     2 |   72 |      5 |
\n|     VIEW                       |DBA_DATA_FILES       |     2 |   72 |      4 |
\n|      UNION-ALL                 |                     |       |      |        |
\n|       NESTED LOOPS             |                     |     1 |  361 |      2 |
\n|        NESTED LOOPS            |                     |     1 |  344 |      1 |
\n|         NESTED LOOPS           |                     |     1 |  331 |      1 |
\n|          FIXED TABLE FULL      |X$KCCFN              |     1 |  310 |      0 |
\n|          TABLE ACCESS BY INDEX |FILE$                |     1 |   21 |      1 |
\n|           INDEX UNIQUE SCAN    |I_FILE1              |     1 |      |      0 |
\n|         FIXED TABLE FIXED INDEX|X$KCCFE (ind:1)      |     1 |   13 |      0 |
\n|        TABLE ACCESS CLUSTER    |TS$                  |     1 |   17 |      1 |
\n|         INDEX UNIQUE SCAN      |I_TS#                |     1 |      |      0 |
\n|       NESTED LOOPS             |                     |     1 |  412 |      2 |
\n|        NESTED LOOPS            |                     |     1 |  399 |      2 |
\n|         NESTED LOOPS           |                     |     1 |  392 |      1 |
\n|          NESTED LOOPS          |                     |     1 |  375 |      0 |
\n|           FIXED TABLE FULL     |X$KCCFN              |     1 |  310 |      0 |
\n|           FIXED TABLE FIXED IND|X$KTFBHC (ind:1)     |     1 |   65 |      0 |
\n|          TABLE ACCESS CLUSTER  |TS$                  |     1 |   17 |      1 |
\n|           INDEX UNIQUE SCAN    |I_TS#                |     1 |      |      0 |
\n|         TABLE ACCESS BY INDEX R|FILE$                |     1 |    7 |      1 |
\n|          INDEX UNIQUE SCAN     |I_FILE1              |     1 |      |      0 |
\n|        FIXED TABLE FIXED INDEX |X$KCCFE (ind:1)      |     1 |   13 |      0 |
\n--------------------------------------------------------------------------------
\n
\nEnd of Report
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Remover a instala\u00e7\u00e3o do STATSPACK<\/span><\/h3>\n

Para finalizar, se por alguma raz\u00e3o voc\u00ea n\u00e3o for mais utilizar o STATSPACK, poder\u00e1\u00a0<\/span>remove-lo utilizando o script “spdrop.sql” que pode ser encontrado no diret\u00f3rio “$ORACLE_HOME\/rdbms\/admin”,\u00a0n\u00e3o se esque\u00e7a que voc\u00ea precisa de um usu\u00e1rio SYSDBA para executar esse script e ao final da execu\u00e7\u00e3o\u00a0desse script, remova os JOBS criados para automatizar os processos e elimine a tablespace do reposit\u00f3rio do\u00a0STATSPACK se ela era de uso exclusivo deste.<\/span><\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

O STATSPACK pode suprir a maioria das informa\u00e7\u00f5es fornecidas no relat\u00f3rio AWR, mas se al\u00e9m do STATSPACK\u00a0voc\u00ea decidir adquirir algum software de Performance e Tuning, observe se ele n\u00e3o acessa alguma vis\u00e3o\u00a0que faz parte da Option “Diagnostic Pack License” pois a utiliza\u00e7\u00e3o dessas vis\u00f5es tamb\u00e9m requerem a\u00a0contrata\u00e7\u00e3o da licen\u00e7a.<\/span><\/p>\n

O software DBTimeWizard<\/a> oferece fun\u00e7\u00f5es de Diagn\u00f3stico para Tuning de Banco de dados e aplica\u00e7\u00f5es sem consultar as vis\u00f5es que fazem parte da Option “Diagnostic Pack License”.<\/span><\/p>\n

Neste artigo procuramos abordar as principais fun\u00e7\u00f5es do STATSPACK, mas se voc\u00ea tiver a necessidade de\u00a0realizar algo que n\u00e3o foi coberto pelo artigo, consulte a documenta\u00e7\u00e3o do STATSPACK<\/a>.<\/span><\/p>\n

Refer\u00eancias<\/span><\/h2>\n

http:\/\/docs.oracle.com\/cd\/B10500_01\/server.920\/a96533\/statspac.htm<\/a><\/p>\n

http:\/\/www.oracledistilled.com\/oracle-database\/performance\/installing-and-configuring-statspack\/<\/a><\/p>\n

https:\/\/docs.oracle.com\/cd\/E13160_01\/wli\/docs10gr3\/dbtuning\/statsApdx.html<\/a><\/p>\n

http:\/\/docs.oracle.com\/cd\/B28359_01\/license.111\/b28287\/options.htm#CIHIHDDJ<\/a><\/p>\n

http:\/\/docs.oracle.com\/cd\/B19306_01\/license.102\/b40010\/database_management.htm<\/a><\/p>\n

http:\/\/www.akadia.com\/services\/ora_statspack_survival_guide.html<\/a><\/p>\n

AWR Reporting – Licensing Requirements Clarification (Doc ID 1490798.1)<\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Muitos profissionais que administram os bancos de dados Oracle vers\u00f5es 10g em diante utilizam o relat\u00f3rio AWR sem saber que a sua utiliza\u00e7\u00e3o requer \u00a0a aquisi\u00e7\u00e3o de uma licen\u00e7a adicional (Diagnostic Pack License), esse equ\u00edvoco\u00a0se deve um pouco ao fato desse recurso ser instalado normalmente numa instala\u00e7\u00e3o padr\u00e3o do banco, ou seja, se a empresa que voc\u00ea trabalha n\u00e3o possui<\/p>\n","protected":false},"author":2,"featured_media":93,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[33,32,26,30,31,28,29,34,27,23],"class_list":["post-92","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tuning-tools","tag-awr-report","tag-banco-de-dados-oracle","tag-diagnostic-pack-license","tag-instalacao","tag-instalation","tag-oracle-database-diagnostic-report","tag-performance-tuning","tag-relatorio-awr","tag-relatorio-diagnostico-desempenho","tag-statspack"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/92","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=92"}],"version-history":[{"count":29,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/92\/revisions"}],"predecessor-version":[{"id":2055,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/92\/revisions\/2055"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/93"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=92"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=92"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=92"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}