Performance Issues and PCOLL_CONTROL...Help!

89163

Hi,

We are experiencing very serious performance issues with some of our JSPs that are returning result sets greater than 1000 records (10 minutes or more). The queries are well-tuned, and it appears that the performance slows as the temporary tables are created for the application modules. We have tried adjusting the max.active.nodes and max.rows.per.node but still no improvement on performance. We have also tried setting the max.active.nodes to -1, but performance stayed the same and the temp tables were still created.

I believe we want to prevent the spillover. We do not ever expect to have more than 3000 rows returned at once, but we need to maintain optimal performance.

What does anyone recommend on either preventing the creation of the temp tables or optimizing the performance?

Thanks in advance!
Andy Hamilton
TUSC

14736

The bottom of this article explains how to control/turn off the spill-over feature.
http://otn.oracle.com/products/jdev/htdocs/bc4j/bc4j_temp_tables.html

-Mark

6416

One option would be to use forward-only views. This would circumvente Viewcaches to take up too much space, requiring spill over to db.

-J.

Steve Muench-Oracle

Nothing more to add here. Both of the above suggestions are what I would have said :-)

Except that by building a VO with no related entity objects (an Expert-Mode SQL-only VO) in combination with using ForwardOnly mode, you can achieve a "no caching at all" situation, neither at the EO cache level, nor the VO row level.

This would be ideal for data you only plan to render on the browser and do not need to update subsequently.