Re: OAS performance questions

[Quoted] Joseph Babad <joseph_babad_at_my-deja.com> writes:
> Are you/anyone using OAS to invoke stored PL/SQL procedures in
> the database that generate HTML and run queries?
> We're looking at doing this with the PL/SQL Web toolkit.
> I'm worried about moving this into the DB - since this way the logic
> (to generate the HTML) is being run on the database engine
> rather than on the middle tier.
>
> Any thoughts/suggestions/experiences?

We've used the PL/SQL Web toolkit for a Web site (database publishing mainly). Speed is acceptable (main problem is tuning the queries, the PL/SQL is not a problem). Some things to watch out for:

• separate HTML (page layout) from PL/SQL (logic). We're using some conventions and perl scripts (that convert HTML files with some placeholders to PL/SQL procedures) to accomplish this.
• PL/SQL has no session context. This prevents smart application level caching. Oracle will do its best to cache data, but when you know your Web application you can often do much better. If you expect application-level caching can significantly improve your systems performance, don't choose PL/SQL.

  PS: I think I read WebDB 3 will support sessions.   Since WebDB uses PL/SQL below it, I wonder if this means   PL/SQL will support sessions.

• PL/SQL in the database server is efficient in bandwidth. That is, if your PL/SQL does a lot of database activity, and not much computing, and returns only a little bit of the processed information, PL/SQL is good for you.

  If you retrieve only a little bit of infomation from   the database, and do a lot of other processing, it may   be better to put it in a separate tier, and use another   programming language.

Kind regards,

--
-- Marco Nijdam,       marco_at_west.nl
-- West Consulting bv, Bagijnhof 80,  2611 AR  Delft, The Netherlands
--                     P.O. Box 3318, 2601 DH  Delft
-- Tel: +31 15 219 1600, Fax: +31 15 214 7889