discoverer 10 server side tuning

as per Note: 466697.1 ....its an memory error....& need to increase MaxVirtualDiskMem and MaxVirtualHeapMem

But we already have slowly increased MaxVirtualDiskMem and MaxVirtualHeapMem to below very high values ...but the issue remains same.......

as per note we are getting Logkeys: exceptions discoiv.servlet_exceptions error but
after that we are not getting below error ............
Unexpected error in state machine: java.lang.OutOfMemoryError

Hence I presume that its different issue rather than memmory....

Below are pref.txt values..........


--------------------------------------------------------------------------------

CacheFlushPercentage = 25 # Percent of cache flushed if the cache is full. valid values 0 - 100%.
MaxVirtualDiskMem = 9294967296 # Maximum amount of disk memory allowed for the data cache. Should be greater than or equal to MaxVirtualHeapMem
MaxVirtualHeapMem = 4294967296 # Maximum amount of heap memory allowed for the data cache.
QueryBehavior = 0 # Action to take after opening a workbook (0 = Run Query Automatically, 1 = Don't Run Query, 2 = Ask for Confirmation)

Also we have raised an SR & as per SR .............all below settings are tried as per SR except for applying a recent patch....

====================================================================
Discoverer performance is largely determined by how well the database
has been designed and tuned for queries.
A well-designed database will perform significantly better than a poorly
designed database.
Workbook design can also affect query performance.

1. Apply latest Discoverer patch as documented in <>
'ALERT: Required and Recommended Patch Levels For All Discoverer Version'.

2. Increase the maximum JVM heap memory:

In general, the default values for the minimum heap (-Xms) memory and
maximum heap (-Xmx) memory are sufficient.
However, if your organization consistently runs large Discoverer queries
then you may benefit from increasing the maximum heap memory from the
default values.

This is recomended if your users are typically running large queries via
Discoverer Viewer.


Increasing the JVM memory can help to avoid "java.lang.OutOfMemoryError"
in Discoverer Viewer:
Please see <>, Best Practice: Configuring The
OC4J_BI_Forms JVM For
Discoverer Viewer/Portlet 10g Performance And Stability for specific
details.

3. Disable Query Prediction:

Query Prediction provides an estimate of the time required to retrieve
the information in a query.
The Query Prediction appears before the query and consumes time.


Edit the /discoverer/util/pref.txt on the middle-tier
server and set:

QPPEnable=0

Also set:


QPPObtainCostMethod = 0

4. Uncheck the 'Enable fantrap detection' checkbox.

When the box is checked, every query generated by Discoverer is
interrogated. Discoverer will detect a fan trap and rewrite the query to
ensure that the aggregation is done at the correct level.

Please refer to <>, Oracle BI Discoverer: Fan Trap
Resolution - Correct Results
Everytime for more information on fantraps.

To disable, in Plus go to Tools -> Options -> Advanced -> Fan Trap
settings.
In Viewer go to Preferences and uncheck the box.

5. Disable Materialized Views/Summaries
In pref.txt add parameter:

MaterializedViewRedirectionBehavior = 0

Value equal to 0 ensures that Materialized View (MV) Redirection is
always performed when MVs are available.

6. Improve query performance by optimizing the SQL.
In pref.txt modify/add following parameters:


SQLFlatten = 0
SQLItemTrim = 0
SQLJoinTrim = 0
UseOptimizerHints = 0

If value of SQLFlatten is 1 then Discoverer will merge inline views in
the query SQL where ever possible.
In case of SQLItemTrim, Discoverer will remove unused folder items from
the query SQL where possible and for SQLJoinTrim Discoverer will remove
unnecessary joins from the query where possible.

UseOptimizerHints will add Optimizer hints to SQL if set >
0.Unnecessarily making Discoverer perform these checks consumes
resources and rather than increasing the performance may reduce the same. So unless you feel these checks have to be performed depending on
requirements, assign zero to these parameters.

7. When Discoverer builds a query, Discoverer makes a database security
check to confirm that the user has access to the tables referenced in
the folders. Avoiding this check can save time.

So in pref.txt underDatabase section add:

ObjectsAlwaysAccessible = 1
8. Whenever you are creating conditions, ensure that you match the Case.
This in turn can reduce the time Discoverer spends on changing the Case
and matching.

For example:

option Upper(Department) in (Upper('VIDEO SALES')

9. Ensure that summaries are refreshed periodically in Discoverer
Administrator.

10. Increase the amount of memory available for the Discoverer data cache. Please refer to <>, Explaining Oracle BI Discoverer
Session Memory Management
And Server Cache Settings.

11. Performance may be enhanced by enabling OracleAS Web Cache.