Method R Discussion GroupMethod R helps developers, DBAs, and decision-makers optimize Oracle-based software in every phase of the software life cycle. |
||
Discuss Method R products, techniques, and events. Ask new questions or post your own responses. |
Our data warehouse uses BusinessObjects as the frond end. All the queries issued by BO are dynamic, which means that I can not hardcode hints. Can I tune this kind of SQL statements?
BO reports usually require advanced knowledge of output column datasets for layout purposes, e.g. so "dynamic" does not necessarily mean generating runtime-random column results. Same advanced knowledge required if user chooses drill-down (slice/dice) via cached datasets. Advanced knowledge provides many tuning options.
First, BO Designer could be used to control joins, similar to hints. New views of data with hints can be included in the universe given known access patterns. Access paths can be engineered into BO usage, using CBO statistics, views (all types), and hints. This is the place to implement most SQL tuning steps. Second, tracing BO sessions (thick client or webi) can provide detailed response time focus on specific queries, or types of queries. E.g. Cost may be high because too many rows are returned to client for drill-down (slice/dice)? Whatever is causing response time issues can be identified. Then, you can decide if hints might help you, or improving CBO statistics is needed, or if you can use views (all types) instead. I found that truly generating "dynamic" BO queries, making tuning difficult, is a very rare BO event. E.g. BO did not allow me to use pl/sql refcursor returns with different column sets on my reports without extensive customization. Hope I understood the question, and this helps. :-) |
Powered by FogBugz