Oracle Database - SQL Access Advisor (Summary Advisor)

Card Puncher Data Processing

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.

Oracle Sql Advisor





Discover More
Oracle Database Em Dimension
OBIEE 11G - sametaexport utility (Metadata Exchange)

sametaexport is an utility to exchange metadata with DB2 or Oracle Database (Dimension with its Hierarchy) to pre-aggregate the relational data and improve query performance. For DB2, the utility: ...
Card Puncher Data Processing
Oracle Database - Materialized view (Snapshot)

in Oracle. Sql Access Advisor (a GUI tool for materialized view and index management) can recommend the creation of materialized views. The end user queries the tables and views in the database....
Tables Dimension Hierarchie
Oracle Database - Methodology for Designing and Building Materialized Views

There are two methods that can be used to define our materialized views: create them manually or generate them from advice given by the SQL Access Advisor. The starting point for creating...
Card Puncher Data Processing
Oracle Database - What are the benefits to define a dimension in Oracle ?

Dimensions do not have to be defined. However, if your application uses dimensional modeling, it is worth spending time creating them as it can yield significant benefits, because : they help query...
Data System Architecture
Relational Data Modeling - View selection problem (recommending the best aggregation tables) - Data Warehousing

The View Selection Problem (VSP) is an NP-Complete problem. Challenges: Design Which materializations to create? Populate Load them with data Maintain Incrementally populate when data changes...



Share this page:
Follow us:
Task Runner