The Enterprise Manager AWR Warehouse is designed to hold performance data from multiple databases for long-term analysis. It promoses that it will save storage and improve performance on your production systems. In that it is indeed correct. However the warehouse itself does not seem to be performant when taking in multiple sources and retaining them long-term – 400 days in our case. Why 400 days is an obvious question that might be asked. Primarily because we are a Retail organisation and Easter is variable each year.
- A dmp file is extracted on the source database and transferred across to the AWR server
- The dmp file is then imported into a temporary schema called AWR$XXXXXX (this loads quickly)
- This data is then inserted into the main AWR tables inside the SYS schema. Is is this stage that is slow.
In order to completely isolate the issue, we altered a parameter, so only one AWR file gets loaded at once, cutting any contention / locking issues out of the equation: