The AWR tables contains a wealth of important performance data which can be very useful in performance tuning trend analysis and also when comparing performance between two seperate periods of time.
AWR data is stored in the WRH$ and DBA_HIST tables in the SYSAUX tablespace. There could be performance implications if these tables were to grow too large in size or if the retention was increased beyond the default of 7 days.
A good solution is to have a central repository and move statistical AWR data periodically to this central repository database using the Oracle supplied awrextr.sql and awrload.sql scripts which can be found in the $ORACLE_HOME/rdbms/admin directory.
The AWR History is by default maintained for 7 days and the data is gathered in the AWR repository tables every hour by default.
The current snapshot retention settings and data gathering frequency can be determined by the query shown below. Note in this case the default settings of 7 days and 1 hour is displayed.
The AWR default settings can be modified using the DBMS_WORKLOAD_REPOSITORY package as shown below. In this case the retention is being increased to 30 days and the interval to every 30 minutes.
retention => 43200,
interval => 30);
Extracting AWR data
Create a directory
SQL> CREATE DIRECTORY AWR_DATA AS
The script will list the information we need to provide to it
After entering the range of snapshot ids, we will need to provide the directory location where the data pump export file will be located. We need to also enter the
dumpfile name as well.
Note: the script will autiomatically append a ‘.dmp’ to the data punp export file name. So we need to just enter the dumpfile name without any extension.
After the export is complete, we will need to ftp the data pump dump file awrexp.dmp to the target server where our central repository database is located.
Loading AWR Data
On the repository database, we will create a directiory AWR_DATA as well and the ensure that the DIRECTORY_PATH corresponds to the directory where the awrexport.dmp file is located.
Also, a temporary staging schema AWR_STAGE is created. Objects are first imported into this staging schema and then inserted into the WR$ and DBA_HIST AWR historical tables.
We will need to provide information about the directory location, dump file and the staging schema name
While specifying the dumpfile name we need to only provide the file name without the .dmp extension
We need to specify the default tablespace and temporary tablespace for this staging user. Note – this user will be dropped once the load is completed.
if we look at the import log file, we will see that data is first imported into the AWR_STAGE schema and then from here it is inserted into the WRH$ and other DBA_HIST tables.
If we now query the DBA_HIST_SNAPSHOT table, we see that it contains the data for two DBID’s – this shows that AWR history data is available in the repository database for two databases.
We can query the repository AWR tables based on DBID values to obtain data for a specific database.
1 comment to Exporting and Importing AWR snapshot data