Materialized view usage
October 5th, 2005 by Peter Scott
For one of my DW customers we adopted a materialized view-based aggregation strategy. Only the base level summaries are exposed to the query tool and the Oracle database uses query rewrite to select the ‘best fit’ aggregation. This was the customers first use of query rewrite, their previous DW had all of the summary tables hand-mapped into the query tool. As often the case with something new there was a degree of uncertainty from the customer as to whether the technology did what it was supposed to do. And from my point of view, I had feeling that the customer had insisted on too many MV aggregates to be built.
To analyze what was going on we had to come up with a way of measuring which MVs were being used during the working day. Capturing the users queries is probably not the best approach. The SQL you see is against the base table, to get the MV accessed you need to look at the explain plan. It did not look a viable approach to capture hundreds of queries per day and then explain them to identify the MVs hit by the query.
However MVs physically exist as tables in the database, it would be perfectly feasible to determine physical and logical IO (from V_$SEGSTAT) against each table at the beginning and end of each day and look for changes. So we implemented a procedure to capture the IO stats for each summary MV at the end of the daily batch (to avoid any IO caused by rebuilding or collection of statistics on the MVs) and again capturing the IO stats at the end of the on-line day. From there we can build a daily history table based on tables that have shown an increase in IO over the day. For good measure we did the same for bitmap index usage.
Net result – - evidence that query re-write works and at least some of aggregates are used!
October 5th, 2005 at 5:25 pm
If you are using 10g, why not use the automatic workload repository together with the SQL access advisor. Set AWR to collect queries that are being issued over a given time frame and then run the SQL access advisor in comprehensive mode (this includes drop recommendations). Obviously, you need to be very careful with drop recommendation. The AWR may not catch all queries (like queries that are only run quarterly). It will also recommend MV and index creation, give size estimates and cost estimates.
October 5th, 2005 at 7:31 pm
This customer is on 9.2 – their query tool vendor does not certify the tool against 10g :(
But a good point, thank you
October 9th, 2005 at 10:06 pm
If the shared pool is large enough to keep the plans in the library cache for some time, maybe you could dump the plans from v$sql_plan using this tool:
http://spazioinwind.libero.it/albertodellera/scripts_etcetera/sga_xplan/index.html
It dumps also the statistics from v$sql_plan_statistics as a bonus.
The “Real Rows(real-estd)” is experimental, the rest has been working fine for me for more then one year now (and I and my developers have found it really convenient).
If you want to take a look, I’d suggest to take the first tour using the persistent=n install option.
Please let me know if you find it useful, have some doubt, or ideas – I *love* building dev tools, it’s a real hobby of mine. My email is at the bottom of the page…