source: http://www.dba-oracle.com/t_tuning_cognos.htm
Just because SQL is generated dynamically, you can still tune it with Materialized Views (using query re-write), Oracle stored outlines (optimizer plan stability) and the Oracle 10g SQL profiles.
Cognos is similar to other OLAP tools (Business Objects, Hyperion) in that it generated ad-hoc SQL and then builds in-RAM "cubes" of the result data.
When building cubes, Cognos will often request aggregated data, and the resulting SQL may have repeating full-table scans.
One of the most effective ways to tune Cognos is to examine the execution plans for the top SQL using a STATSPACK or AWR report. Once the commonalities of the Cognos queries are identified, a few well-placed Materialized views can re-write the Cognos SQL to access the pre-summarized data.
