About
In database releases prior to 10g, this feature is called the Oracle Database Summary Advisor.
On a large and complex database, manually determining the optimal set of materialized views and their indexes that support the users queries can be a time consuming task. The SQL Access Advisor (in Entreprise Manager), which is part of the Tuning Pack, and has been available since Oracle Database 10g, is available to make this task considerably easier and it is an invaluable tool for this purpose. The SQL Access Advisor can be found in Advisor Central in Oracle Enterprise Manager or can be invoked from the command line using SQL*Plus by calling one of the procedures in the DBMS_ADVISOR package. Using as input a workload of SQL statements, the advisor takes you step-by-step through the process to recommend the materialized views, their indexes and the materialized view logs and how to implement them. The results of this process are provided as a set of recommendations, which can be implemented either by the SQL Access Advisor or manually.
Figure below shows the Summary screen resulting from running the SQL Access Advisor with a user workload containing our eight scenario queries. We can see that the advisor has made some recommendations that it predicts can result in significant performance improvements. From here it is possible to navigate to other screens to examine and modify the generated scripts, for example to change the materialized view names and tablespaces, and then a task can be scheduled for their deployment in the warehouse database.
