Skip to content

Personal tools
You are here: Home » Datawarehouse » Data Warehouse Articles Archive » Improvements to Summary Management with Oracle 10g
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3548

Improvements to Summary Management with Oracle 10g

by Mark Rittman

With Oracle Database 10g, Oracle has introduced a number of improvements to summary management and query rewrite. This article details these improvements and explains how they can be used to improve performance for your data warehouse.


Oracle DBAs who manage databases used for decision support applications often create materialized views to improve the response time of user queries. Materialized views allow us to precalculate expensive joins and aggregation operations prior to users running queries, storing the results in a snapshot table in the database together with a mechanism to refresh the contents whenever the base-level data changes. Together with Query Rewrite, a technology that takes queries written against base tables and rewrites them to use materialized views, these two technologies allow the DBA to put together an effective aggregation strategy that provides a consistent response time whatever query a user puts to the data warehouse.

Materialized views and query rewrite were originally introduced with the Enterprise Edition of Oracle8i, and with each successive release of the database, improvements have been made to these two features to make them easier to create, maintain, and use. Oracle Database 10g includes a number of improvements to materialized views and query rewrite including the SQL Access Advisor, DBMS_ADVISOR.

The SQL Access Advisor

Oracle9i came with a feature known as the Summary Advisor that recommended a set of materialized views for a given query workload. Consisting of a number of procedures and functions within the DBMS_OLAP package, together with GUI functionality within Oracle Enterprise Manager, Summary Manager was a useful tool for recommending an aggregation strategy for your data warehouse.

Summaries are only part of the story, however, when looking to improve query response time within your Oracle data warehouse. The effective creation of indexes is just as important and with Oracle Database 10g, Oracle has acknowledged this by providing a new tool, the SQL Access Advisor, that now makes recommendations for both materialized views and indexes. Available through the DBMS_ADVISOR PL/SQL package, the SQL Access Advisor can be used to either tune existing materialized views and indexes, or to recommend them for new schemas that need tuning. An important point to note with the SQL Access Advisor is that whilst it is installed by default as part of the 10g database installation, it is actually part of the Enterprise Manager Tuning Pack, which needs to be licensed separately if using in a production environment.

A typical tuning exercise using the DBMS_ADVISOR package would be as follows:

1. Gather a number of SQL statements that will form the tuning workload.

2. Check that the user running DBMS_ADVISOR has the ADVISOR privilege, and has SELECT access to the tables and views referenced by the SQL statements.

3. Define a task using DBMS_ADVISOR.CREATE_TASK, to create a container for your tuning exercise:

:task_id, ‘my_first_task’);

Then define a workload object, into which we will later load individual SQL statements:

is my first workload');

Then, link your task and workload objects:


4. Statements can then be manually added into the workload object:

'my_first_workload', 'MONTHLY', 'ROLLUP', priority=>1,
executions=>20, -
username => 'DEMO',  sql_text => ‘SELECT SUM(sales) FROM

Alternatively, they can be loaded in from a table of SQL statements you create, an SQL Tuning Set, an SQL Cache workload, an Oracle9i Summary Advisor workload; otherwise, a hypothetical workload can be generated from a set of table and view definitions in a schema.

5. Generate recommendations for this task’s workload:


Each recommendation generated by the SQL Access Advisor can be viewed using catalog views such as USER_ADVISOR_RECOMMENDATIONS. In addition, the procedure GET_TASK_SCRIPT generates an executable SQL script that contains the CREATE, DROP, and ALTER statements to implement the advisor recommendations:

'ADVISOR_RESULTS', 'script.sql');

As an alternative to generating recommendations using the DBMS_ADVISOR package, the SQL Access Advisor functionality is also available as a wizard within the Enterprise Manager 10g Web site.

The SQL Access Advisor Wizard allows you to define tasks, gather workloads and implement recommendations using a graphical interface and is located in the Advisor Central page of the EM Web site.


As well as containing functionality that recommends suitable materialized views and indexes, DBMS_ADVISOR contains a procedure called TUNE_MVIEW that is used when working with the query rewrite mechanism.

Oracle9i came with two packages, DBMS_MVIEW.EXPLAIN_MVIEW and DBMS_MVIEW.EXPLAIN_REWRITE that could be used to diagnose why a materialized view wasn't being used for query rewrite. However, although these packages told you why rewrite hadn't happened, they left it down to you to work out how to alter your CREATE MATERIALIZED VIEW statement to ensure that rewrite happened correctly. Oracle 10g’s DBMS_ADVISOR.TUNE_MVIEW builds on this functionality and tells the DBA what changes to make to a materialized view to make it suitable for query rewrite, taking as its input a CREATE MATERIALIZED VIEW statement and outputting a corrected version that supports query rewrite and features such as fast refresh.

Tuning a materialized view follows a similar process to that used with the SQL Access Advisor.

1. First, create a task and supply a CREATE MATERIALIZED VIEW statement to DBMS_ADVISOR.TUNE_MVIEW

   select   category, country, sum(sales) -
   from     product p, geography g, sales s -
   where    s.product_id = p.product_id -
   and      s.geography_id = p.geography_id -
   group by p.category,

2. You can then directly query the resulting tuned CREATE MATERIALIZED VIEW statement from the USER_TUNE_MVIEW view:

WHERE TASK_NAME= ‘my_first_mv’ AND

3. Or you can output the tuned statement to the filesystem using the DBMS_ADVISOR.GET_TASK_SCRIPT procedure.

WHERE TASK_NAME= ‘my_first_mv’ AND

The DBMS_ADVISOR.TUNE_MVIEW procedure has the capability to add additional aggregate columns and materialized view logs to the view definition so that it becomes fast refreshable, and it can restate the view definition (sometimes breaking it into separate, simpler definitions) so that it is more likely to satisfy query rewrite restrictions.

Rewrite_or_Error Hint

With Oracle Database 10g, query rewrite is now possible when your SELECT statement contains analytic functions, full outer joins, and set operations such as UNION, MINUS and INTERSECT. In addition, you can now use a hint, /*+ REWRITE_OR_ERROR */, which will stop the execution of a SQL statement if query rewrite cannot occur.

  2      s.prod_id,
  3      sum(s.quantity_sold)
  4  FROM      sales s
  5  GROUP BY  s.prod_id;
FROM     sales s
ERROR at line 4:
ORA-30393: a query block in the statement did not rewrite

Query Equivalence

One of the options available for the Enterprise Edition of Oracle Database 10g is the OLAP Option, a multidimensional calculation engine that allows the DBA to perform OLAP analysis on multidimensional datatypes. By using the OLAP Option, DBAs working on data warehousing projects can choose to store their detail level data in normal Oracle relational tables, and then store aggregated data in OLAP Option “analytic workspaces” for further multidimensional analysis.

With the Oracle9i OLAP Option, you could provide access to these analytic workspaces using SQL statements, by using the new OLAP_TABLE function.

select product, city, sales,
  from table(OLAP_TABLE('my_first_aw DURATION session',
   'DIMENSION category from product
    DIMENSION country from geography
    MEASURE sales FROM sales

However, it was not possible to use these analytic workspaces as replacements for materialized views if you wanted to take advantage of query rewrite, as the query rewrite mechanism in Oracle9i would never recognise the OLAP_TABLE function as being one that could provide the aggregated answers to the users’ original query. Oracle Database 10g addresses this shortcoming by providing a new feature called “query equivalence.”

Query equivalence is declared using the DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE procedure, and uses the syntax:


Query equivalence allows the DBA to declare that two SQL statements are functionally equivalent, and that the target statement should be used in preference to the source statement. By using the query equivalence feature, a DBA can produce a custom SQL query, in this instance by using the OLAP_TABLE feature to retrieve summary data from an analytic workspace, and have the query used to satisfy a regular SQL statement that summarises via the usual sum() and group by clauses.

Taking the example previously cited, we might want our analytic workspace to be used to provide a specific summary for an SQL query. In this case, our SQL query might be:

select        category, country, sum(sales)
from          product p, geography g, sales s
where         s.product_id = p.product_id
and           s.geography_id = p.geography_id
group         by p.category,

To declare that our analytic workspace query is functionally equivalent to the previous query, issue the command

'select     category, country, sum(sales)
from     product p, geography g, sales s
where    s.product_id = p.product_id
and     s.geography_id = p.geography_id
group by p.category,',   
' select product, city, sales,      
from table(OLAP_TABLE('my_first_aw DURATION session',     
'DIMENSION category from product      
DIMENSION country from geography     
MEASURE sales FROM sales))');

Query equivalence can be used to substitute any SQL DML statement for another (including use of the new SQL Model clause), and is particularly useful when SQL is generated by an application and cannot be changed, but the DBA knows of a different way to phrase the query, perhaps using new data structures (such as an OLAP Option analytic workspace) that he has created.


Oracle 10g includes a number of enhancements to materialized views and the query rewrite mechanism. By using new features such as the SQL Access Advisor, DBMS_ADVISOR.TUNE_MVIEW and the new query equivalence mechanism, DBAs will find that summaries are easier to create, are more likely to qualify for query rewrite, and can now be delivered through alternative technologies such as the Oracle 10g OLAP Option.


Mark Rittman is a Certified Oracle Professional DBA and works as a Consulting Manager at SolStonePlus, specializing in developing BI and DW applications using the Oracle Database, Application Server, Discoverer, Warehouse Builder, and Oracle OLAP. Outside of Plus Consultancy, Mark chairs the UKOUG BI and Reporting Tools SIG, and runs a Weblog dedicated to Oracle BI and Data Warehousing technology. Mark recently was awarded an OTN Community Award for contributions to the Oracle developer community, and is a regular speaker at Oracle User events in both the UK and Europe.

Contributors : Mark Rittman
Last modified 2006-02-08 02:53 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone