This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,964 Users
  • 2,269,775 Discussions


Performance Issues and PCOLL_CONTROL...Help!


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 and max.rows.per.node but still no improvement on performance. We have also tried setting the 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


  • 14736
    14736 Member Posts: 85
    The bottom of this article explains how to control/turn off the spill-over feature.

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

  • Steve Muench-Oracle
    Steve Muench-Oracle Member Posts: 5,468 Employee
    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.
This discussion has been closed.