BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

11g Before and After Enabling Query Rewrite on the Cube

Oracle 11g New Features Tips by Burleson Consulting
July 12, 2008

Oracle 11g SQL New Features Tips

Before enabling Query Rewrite on the Cube

--Run query against the base tables (with Query Rewrite disabled on the cube)

ALTER MATERIALIZED VIEW GLOBAL.CB$SALES_CUBE3 DISABLE QUERY REWRITE;

set autotrace traceonly explain
select
ch.channel_id channel,
sum(CAST(SALES AS NUMBER))  SALES,
sum(CAST(UNITS AS NUMBER))  UNITS
from
GLOBAL.channel_dim ch,
GLOBAL.units_history_fact f
where ch.channel_id = f.channel_id
group by
ch.channel_id; 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=925 Card=3 Bytes=96)
 
   1    0   SORT (GROUP BY NOSORT) (Cost=925 Card=3 Bytes=96)
   2    1     MERGE JOIN (Cost=925 Card=3 Bytes=96)
   3    2       SORT (JOIN) (Cost=921 Card=3 Bytes=87)
   4    3         VIEW OF 'VW_GBC_5' (VIEW) (Cost=921 Card=3 Bytes=87)
   5    4           HASH (GROUP BY) (Cost=921 Card=3 Bytes=45)
   6    5             TABLE ACCESS (FULL) OF 'UNITS_HISTORY_FACT' (TAB
          LE) (Cost=882 Card=885988 Bytes=13289820)
 
   7    2       SORT (JOIN) (Cost=4 Card=3 Bytes=9)
   8    7         TABLE ACCESS (FULL) OF 'CHANNEL_DIM' (TABLE) (Cost=3 Card=3 Bytes=9)

After enabling Query Rewrite on the Cube

Before the query against the base tables will rewrite, the Materialized Views will need to be refreshed by executing the dbms_mview.refresh procedure similar to the following:

exec dbms_mview.refresh ('CB$CUSTOMER_MARKET_SEGMENTS','CF');
exec dbms_mview.refresh ('CB$CHANNEL_PRIMARY','CF');
exec dbms_mview.refresh ('CB$CUSTOMER_SHIPMENTS','CF');
exec dbms_mview.refresh ('CB$PRODUCT_PRIMARY','CF');
exec dbms_mview.refresh ('CB$TIME_CALENDAR_YEAR','CF');
exec dbms_mview.refresh ('CB$SALES_CUBE3','CF');

To test if queries against the base tables are rewritten against the Cube Organized Materialized Views run an explain plan:

--Run query against the base tables (but with Query Rewrite enabled on the cube)

ALTER MATERIALIZED VIEW GLOBAL.CB$SALES_CUBE3 ENABLE QUERY REWRITE;

set autotrace traceonly explain
select
ch.channel_id channel,
sum(CAST(SALES AS NUMBER))  SALES,
sum(CAST(UNITS AS NUMBER))  UNITS
from
GLOBAL.channel_dim ch,
GLOBAL.units_history_fact f
where ch.channel_id = f.channel_id
group by
ch.channel_id; 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=34 Card=3 Bytes=309)
   1    0   HASH (GROUP BY) (Cost=34 Card=3 Bytes=309)
   2    1     HASH JOIN (Cost=33 Card=60 Bytes=6180)
   3    2       TABLE ACCESS (FULL) OF 'CHANNEL_DIM' (TABLE) (Cost=3 Card=3 Bytes=9) 
   4    2       CUBE SCAN (PARTIAL OUTER) OF 'CB$SALES_CUBE3'(Cost=29 Card=2000                 Bytes=200000)

The above SQL was written to query the UNITS_HISTORY_FACT table, but due to Cube Organized Materialized Views it was rewritten behind-the-scenes to query against the cube.  The execution plan above is similar to the following SQL where the Remote DBA wrote the SQL to specifically query against an OLAP cube.

set autotrace traceonly explain

SELECT *

FROM
TABLE(CUBE_TABLE(‘GLOBAL.SALES2’));

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=29 Card=2000 Bytes=200000)
   1    0   CUBE SCAN (PARTIAL OUTER) OF 'SALES2' (Cost=29 Card=2000 Bytes=200000)

The advantage to Cube Organized Materialized View is realized in the above examples where a query that normally would have queried against the relational table was seamlessly rewritten to access the OLAP cube without making any changes to the SQL.  Thus applications which are impossible or difficult to alter can take advantage of OLAP cubes without altering a single line of code.

To further expand the number of queries that can take advantage of materialized views Oracle can now rewrite queries on remote tables.

Conclusion

11g adds many new enhancements (482 to be precise), however to get the value from these improvements the Remote DBA must first learn the new features and then understand how to employ them to meet the needs of the business.  11g enables the Remote DBA to provide improved levels of service through the use of replication.  For example the standby database (with Real-Time query) can be up on the weekends, while the Remote DBA is patching or upgrading the primary database, thus allowing the business to run reports.  Replication such as Streams is up to two times faster with 11g, helping the Remote DBA to meet service level objectives.  Another way 11g helps meet service level objectives is through the use of Enhanced Fast-Start Failover.  The Remote DBA can failover for user configurable health conditions.  This chapter covered some of the ways 11g DataGuard, Streams and other replication options have been enhanced to provide better High Availability. 

 

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter