We are using StreamServe ( http://www.opentext.com/what-we-do/products/customer-experience-management/customer-communications-management/opentext-streamserve ) to print documents from our database.

This application gives us a severe headache because it eats up SGA when there are lots of users (thousands). A bit of detective work has shown me that they use EXECUTE IMMEDIATE to select from metadata-tables containing approx 1000 lines of data. Not so much.

Now, my conclusion is that we have lots of parsing here on one query - SELECT archive_table FROM archive_field_mapping, metadataname WHERE archive_field_mapping.metadatanameid = metadataname.metadatanameid AND rownum < 2 AND archive_field_mapping.typeid = :1.

I do not have the source code of StreamServe but one might think that they are using bind variables so heavy parsing should not be the case? Is there a difference in using EXECUTE IMMEDIATE, would DBMS_SQL be a better choice here? I know that they are using EXECUTE IMMEDIATE, their support has told me so.


Morgan