*AWR - Automatic Workload Repository armazena as informações do banco das visões WRH$ e DBA_HIST na tablespace SYSAUX. Dependendo do modo que esteja configurada a coleta de dados do AWR pode haver implicações de desempenho, pois estas tabelas irão crescer muito.
Uma solução adequada é ter um repositório central e mover periodicamente os dados estatísticos do AWR para este banco de dados usando o repositório central. Para executar essa atividade a Oracle fornece os scripts “awrextr.sql” e o “awrload.sql” que são encontrados em $ORACLE_HOME/rdbms/admin.
Quando o banco de dados é criado, as informações das estatísticas são coletadas, armazenadas e mantidas por 7 (sete) dias. Os dados são gravados nas tabelas do repositório AWR a cada hora.
As configurações da retenção e da frequência de coleta dos dados podem ser verificadas utilizando a consulta abaixo.
SQL> SELECT TO_CHAR(SNAP_INTERVAL,'DD') INTERVALO,
TO_CHAR(RETENTION,'DD') RETENCAO
FROM DBA_HIST_WR_CONTROL;
INTERVALO RETENCAO
------------------ ------------------
+00000 00:10:00.0 +00021 00:00:00.0
Nota: O resultado
da consulta apresenta uma configuração do AWR com retenção de
21 (vinte um) dias e uma coleta de dados a cada 10 (dez)
minutos.
As configurações do AWR podem ser alteradas utilizando a “package” “dbms_workload_repository” como mostrado abaixo.
SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION => 36000,INTERVAL => 30);
Neste caso, a retenção será ajustada de 21 (vinte e um) para
25 (vinte e cinco) dias e o intervalo a cada 30
(trinta) minutos.
Importante: Para se calcular o valor para o parâmetro
RETENTION, deve-se usar a fórmula abaixo: Exemplo para 25 dias.
Fórmula
para cálculo do RETENTION (numero de dias * 24 * 60)
Substituindo
temos: (25 * 24 * 60) que será igual a 36000
Verificando os parâmetros alterados:
SQL> SELECT TO_CHAR(SNAP_INTERVAL,'DD') INTERVALO,
TO_CHAR(RETENTION,'DD') RETENCAO
FROM DBA_HIST_WR_CONTROL; INTERVALO
RETENCAO
------------------ ------------------
+00000 00:30:00.0 +00025 00:00:00.0
COMO EXTRAIR OS DADOS DO AWR (EXPORT)
Para a extração das informações do repositório do
AWR, deve-se inicialmente criar um diretório onde os dados serão exportados:
SQL>
CREATE DIRECTORY AWR_DATA AS '/home/oracle/export';
Directory created.
Depois para executar a exportação deve-se usar o
script “awrextr.sql”:
SQL> @?/rdbms/admin/awrextr.sql
O script irá solicitar
as informações para a geração do export do AWR, detalhes seguem abaixo:
AWR
EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will extract the AWR data for a
range of snapshots ~
~ into a dump file. The script will prompt users for the ~
~ following information: ~
~ (1) database id ~
~ (2) snapshot range to extract ~
~ (3) name of directory object ~
~ (4) name
of dump file ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Databases
in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Deve-se
informar o “ID” do banco de dados, a faixa de “snapshots
ids” a ser exportada, o nome do diretório
onde o arquivo de exportação será
criado. Também deve-se informar o
nome do arquivo.
DB Id
DB Name Host
------------
------------ ------------
* 1238712821 DB_TEST oralab
The
default database id is the local one: '1238712821'. To use this
database
id, press to continue, otherwise enter an alternative.
Enter
value for dbid: 1238712821
Using
1238712821 for Database ID
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
without
specifying
a number lists all completed snapshots.
Deve-se informar o número de dias que se deseja exportar do
repositório.
Enter
value for num_days: 1
Listing
the last day's Completed Snapshots
DB
Name Snap Id Snap Started
------------
--------- ------------------
DB_TEST
137501 13 May 2013
00:00
137502 13 May 2013 00:30
137503 13 May 2013 01:00
137504 13 May 2013 01:30
137505 13 May 2013 02:00
137506 13 May 2013 02:30
137507 13 May 2013 03:00
137508 13 May 2013 03:30
137509 13 May 2013 04:00
137510 13 May 2013 04:30
137511 13 May 2013 05:00
137512 13 May 2013 05:30
137513 13 May 2013 06:00
137514 13 May 2013 06:30
137515 13 May 2013 07:00
137516 13 May 2013 07:30
137517 13 May 2013 08:00
137518 13 May 2013 08:30
137519 13 May 2013 09:00
137520 13 May 2013 09:30
137521 13 May 2013 10:00
137522 13 May 2013 10:30
137523 13 May 2013 11:00
137524
13 May 2013 11:30
Specify
the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter
value for begin_snap: 137501
Begin
Snapshot Id specified: 137501
Enter
value for end_snap: 137524
End Snapshot Id specified: 137524
Specify
the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory
Name Directory Path
--------------------- ------------------------------------------------
AWR_DATA /home/oracle/export
DATA_PUMP_DIR /home/oracle/export
OPS_LOG /opt/oracle/OPS_LOG
ORACLE_OCM_CONFIG_DIR /opt/oracle/product/11.2.0/db_1/ccr/state
XMLDIR
/opt/oracle/product/11.2.0/db_1/rdbms/xml
Choose
a Directory Name from the above list (case-sensitive).
Enter
value for directory_name: AWR_DATA
Using
the dump directory: AWR_DATA
Specify
the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The
prefix for the default dump file name is awrdat_137501_137524.
To
use this name, press to continue, otherwise enter
an
alternative.
Enter
value for file_name: awr_export_15052013
Nota: O script automaticamente acrescenta a extensão (*.dmp)
ao nome do arquivo.
Using
the dump file prefix: awr_export_15052013
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /home/oracle/export
| awr_export_15052013.dmp
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Extract Started ...
|
| This operation will take a few moments. The
| progress of the AWR extract operation can be
| monitored in the following directory/file:
| /home/oracle/export
| awr_export_15052013.log
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Extract Started ...
|
| This operation will take a few moments. The
| progress of the AWR extract operation can be
| monitored in the following directory/file:
| /home/oracle/export
| awr_export_15052013.log
|
End
of AWR Extract
Após concluída a
exportação pode-se fazer o “scp” do arquivo para o servidor desejado.
COMO CARREGAR OS
DADOS DO AWR (IMPORT)
Para
“importar” os dados do repositório AWR no banco de dados destino,
deve-se criar um diretório AWR_DATA e colocar o arquivo
de “dump” neste diretório.
SQL> CREATE DIRECTORY AWR_DATA AS
'/home/oracle/destino/export';
Directory created.
Além disso, durante
o processo de importação será criado um “schema” chamado AWR_STAGE. Os objetos serão
importados neste esquema e em seguida, serão inseridos nas
tabelas históricas WR$ e DBA_HIST.
Durante a
execução do script “awrload.sql” serão solicitadas informações sobre o diretório,
o nome do arquivo (*.dmp) e o nome do “schema” AWR_STAGE.
SQL>
@?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR
LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will load the AWR data from a
dump file. The ~
~ script will prompt users for the following
information: ~
~ (1) name of directory object ~
~ (2) name of dump file ~
~ (3) staging schema name to load AWR data
into ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify
the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory
Name Directory Path
----------------------
------------------------------------------------
AWR_DATA
/home/oracle/export
DATA_PUMP_DIR /home/oracle/export
OPS_LOG /opt/oracle/OPS_LOG
ORACLE_OCM_CONFIG_DIR
/opt/oracle/product/11.2.0/db_1/ccr/state
XMLDIR /opt/oracle/product/11.2.0/db_1/rdbms/xml
Choose
a Directory Name from the list above (case-sensitive).
Enter
value for directory_name: AWR_DATA
Using
the dump directory: AWR_DATA
Specify
the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please
specify the prefix of the dump file (.dmp) to load:
Enter
value for file_name: awr_export_15052013
Loading
from the file name: awr_export_15052013
Staging
Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The
next step is to create the staging schema
where
the AWR snapshot data will be loaded.
After
loading the data into the staging schema,
the
data will be transferred into the AWR tables
in
the SYS schema.
The
default staging schema name is AWR_STAGE.
To
use this name, press to continue, otherwise enter
an
alternative.
Enter
value for schema_name:
Specify
the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please
specify the prefix of the dump file (.dmp) to load:
Enter
value for file_name: awr_export_15052013.dmp
Loading
from the file name: awr_export_15052013.dmp
Será necessário especificar a
tablespace onde os dados do AWR serão importados e também a tablespace
temporária.
Nota: Após a carga dos dados o usuário AWR_STAGE será
descartado quando a carga estiver completa.
Choose the AWR_STAGE users's default
tablespace. This is the
tablespace in which the AWR data will be staged.
TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
------------------------------ ---------
------------------
SYSAUX PERMANENT *
USERS PERMANENT
Pressing will
result in the recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace:
Using tablespace SYSAUX as the default tablespace
for the AWR_STAGE
Choose the Temporary tablespace for the AWR_STAGE
user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.
TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
------------------------------ ---------
-----------------------
TEMP TEMPORARY *
Pressing will
result in the database's default temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace:
Using tablespace TEMP as the temporary tablespace
for AWR_STAGE
... Creating AWR_STAGE user
No arquivo de log da importação é possível ver que os dados são
importados primeiro no esquema AWR_STAGE e depois os dados são carregados nas
tabelas WRH $ e outras tabelas DBA_HIST.
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported
"AWR_STAGE"."WRH$_SQL_PLAN" 3.845 MB 11494 rows
. . imported "AWR_STAGE"."WRH$_SQLTEXT" 1.012 MB 569 rows
. . imported
"AWR_STAGE"."WRH$_SYSMETRIC_SUMMARY" 156.8 KB 2025 rows
.......
........
Append Data for
"AWR_STAGE".WRH$_SGASTAT.
INSERT /*+ APPEND */ INTO SYS.WRH$_SGASTAT
(SNAP_ID, DBID, INSTANCE_NUMBER, NAME, POOL, BYTES) SELECT SNAP_ID,
3228342000, INSTANCE_NUMBER, NAME, POOL,
BYTES FROM "AWR_STAGE".WRH$_SGASTAT WHERE DBID = :c_dbid
...
appended 388 rows
Ao final da
importação dos dados pode-se consultar
a tabela DBA_HIST_SNAPSHOT e verificar dois DBIDs.
SQL> select distinct dbid from dba_hist_snapshot;
DBID
----------
1238712821 -> Informações
importadas do banco de dados DB_TEST
3892233981
É possível consultar as
informações de um banco de dados específico, informando o valor do parâmetro
DBID, no repositório AWR.
Referências: Oracle®
Database Concepts 11g Release 2
(11.2)
Oracle® Database Performance
Tuning Guide 11g Release 2
(11.2)
MSc.
Rubens Oliveira
DBA Oracle Consultor
