Optimize the database footprint with AWR Warehouse

In general Oracle databases do play a role in wider critical production system used to provide vital services to the business. In cases where databases are deployed and they are being used to almost the maximum capabilities it is important to ensure you have a strategy for monitoring and tuning performance.

In those cases the majority of the companies will invest in implementing the correct monitoring and management solutions. For Oracle databases this is commonly, and for good reasons, Oracle Enterprise Manager. Oracle Enterprise Manager will provide a lot of options for monitoring performance out of the box and as part of the free (gratis) base installation.

When dealing with critical database systems, as described above, that are in need of more thorough performance monitoring and tuning companies will make use of AWR. A less known options is AWRW or in full Automatic Workload Repository Warehouse.

AWR Warehouse
The AWR Warehouse is a part of Oracle Enterprise Manager and provides a solution to one of the shortcomings of “standard” AWR and provides a lot more options to DBA’s and performance tuning specialist. With “standard” AWR you will be able to keep an 8 day set of data on your local database server. The advantage of AWR Warehouse is that all AWR data is collected and stored in one central warehouse as part of Oracle Enterprise Manager.

This provides a number of direct advantages as listed below;

• The ability to store a long(er) period of AWR data
• The ability to easily compare AWR data from different databases in one single location
• Use out of the box diagnostics features from OEM on the historical AWR snapshots

Query, analyze and compare

One of the things AWR Warehouse will be supporting you in is to make your performance tuning team more efficient. With AWR Warehouse you have to option to query the AWR snapshots directly, you can analyze the data and run this same query for another database instance or for all database instances on your engineered system or the entire IT footprint. 

As an example, if you find a sub-optimal implementation of SQL code in an isolated database you might be interested if this same implementation is used in other databases across your estate. By making use of AWR Warehouse you will have the ability to check with one single query on which systems this also might be an issue and where you might need to do code refactoring or performance tuning. 

The business benefit

The benefits to the business are obvious. By enabling performance tuning teams, development teams and DBA’s to analyze all databases at once using a centralized AWR Warehouse the time to find possible performance issues  is shortened. 

The ability to and the effectiveness of analyzing AWR reports and finding possible performance issues is drastically improved while the time needed for the analysis is shortened. 

AWR Warehouse will give the ability to move away from case-by-case tuning. It provides the ability to move to a more overall tuning strategy. In general tuning teams and DBA’s will work on a case by case basis where they take an isolated issue in a single database. Tracking down if the same type of performance issues is in another database somewhere in the vast IT footprint is often a tedious task which is not performed. AWR Warehouse provides the option to run the same diagnostics you run for a single isolated database on all databases in your IT footprint. This moves a company into a wider, a better, tuning strategy which directly benefits the business. 

By optimizing your database, by finding issues in your SQL code you will be able to make your database instances more effective. Ensuring you do remove bottleneck and remove sub optimal implementations that use far more resources than required. Essentially it will free compute resources by doing this which can be used for other purposes. It provides the ability to run more database instances on existing hardware or grow load on your systems without the need to purchase additional hardware. 

The business case 

The business case for purchasing the required licenses needed to use AWR Warehouse need to involve a couple of data points to make a fare business case to invest into this. 

• The number of critical databases in need of tuning
• The amount of FTE spending time on tuning
• The (potential) los in revenue due to slow performance
• The (potential) gain in freeing compute resources due to tuning
• The (potential) not needed investment in hardware expansion

Those pointers should be incorporated in a business case, next to the standard data points that you would include in a standard business case. Failing to include the above will result in a sub-optimal business case. 

General advice

In general the advice is to look into using AWR Warehouse when: 

• You do have an Oracle database footprint which is significant. Significant in this case is open for discussion, we use a 15 production database threshold.  
• You do have the need to tune your databases in an optimized manner without the need to have a significant number of people investing in optimizing. 
• You have a system sizing which is “tight” and you need to ensure your databases are optimized in the most optimal way
• You have a system sizing which is “generous” and you like to limit the number of resources per database to free resources for other use (other / more database instances on the same hardware)
• You foresee that the load on your systems will grow in the near future and you need to ensure you are prepared for this and database response times will stay acceptable by the database.