|
Materialized views were introduced in Oracle8i
and enable major performance improvement for many queries.
However, the challenge for the DBA is creating a set of materialized
views which can be used to service the current workload.
The Summary Advisor helps solve this problem
by recommending a set of materialized views to create, drop
or retain. It makes these decisions based upon a workload
which is loaded via one of three procedures in the dbms_olap
package. The workload information can be obtained from:
- current contents of the SQL cache
- user-defined workload
- a workload collected by Oracle Trace
- a hypothetical workload generated by the
Summary Advisor when no real workload is available
The entire workload does not have to be used.
It can be filtered either at loading time or when the recommendation
process is run. There are eight filters, which allow the workload
to be filtered by application name, cardinality of the base
table, date a query was last used, the number of times a query
is executed, the database users executing the queries, the
priority of a query, the base tables referenced in a query
and the query response time.
The materialized views recommendations are
generated using the procedure dbms_olap.recommend_mview_strategy.
They are stored in the advisor repository tables and implemented
by calling the procedure dbms_olap.generate_mview_script.
There is no limit to the number of recommendations that may
be retained, therefore it is possible to perform ‘what-if’
modelling on different workload scenarios.
An alternative to using the dbms_olap.recommend_mview_strategy
procedure is to call the Summary Advisor wizard in Oracle
Enterprise Manager. The wizard will not only show the recommendations
from the dbms_olap.recommend_mview_strategy procedure,
but also implement them. A major benefit of using the wizard
is that it displays a graph, which is shown below, which enables
the DBA to assess the benefits and storage costs for the materialized
views being recommended. By moving the slider on the graph,
different sets of recommendations will be seen according to
the position selected.
The Summary Advisor also has the ability to
estimate the size of a materialized view given a SQL statement,
using the procedure dbms_olap.estimate_summary_size,
and to assess whether a materialized view is likely to be
used in a workload via the procedure dbms_olap.evaluate_mview_strategy.
In Oracle9i,
significant improvements have been made to the Summary Advisor
which include:
- workload from SQL Cache or user-defined
- filtering workload
- single API procedure to recommend materialized
views
- SQL script to implement the recommendations
- report of Summary Advisor recommendations
- graph of recommendations performance benefits
in Summary Advisor wizard in Oracle Enterprise Manager
- support for non-star schemas
- shares its knowledge base with query rewrite
to improve recommendation accuracy
The enhancements to the Summary Advisor in
Oracle9i
make it easier to generate a good set of recommendations for
the current workload, which will then be used by query rewrite
and will result in improved query response times.
More
Info
Oracle9i
Daily Features
|
Bookmark
