|
Employ Materialized Views
Once there was a call from a point-of-sale data warehouse in
Germany. The IT manager spoke very little English and most of the
conversation was done using
Babelfish. The system was largely read-only with a short batch
window for nightly updates. Once I connected, I immediately
noticed that virtually every query in the system was performing a
sum() or avg() function against several key tables.
The v$sql_plan view (via plan9i.sql) showed loads of
very-large-table, full-table scans, and the system was crippled
with “db file scattered read” waits.
I
was easily able to help by creating three materialized views and
employing query rewrite to reduce physical disk I/O by over 2,000
percent; this improved performance by more than 30x — a real
silver bullet!
If you like Oracle tuning, you might enjoy my latest book
“Oracle Tuning: The Definitive Reference” by Rampant TechPress.
It’s only $41.95(I don’t think it is right to charge a fortune
for books!) and you can buy it right now at this link:
http://www.rampant-books.com/book_ 2005_1_awr_proactive_tuning.htm
|
