Re: Oracle Object Types and Java (migrating from mod_plsql)

Thomas Kyte wrote:
> some of the things you mention above are huge scalability inhibitors
-- the
> execute immediate for example -- that is downright "wrong" to have
dynamic sql
> "the norm". dynamic sql in plsql should be the exception. You lose
the parse
> once, execute many (you might pick SOME of it back up in 10g, execute
immediate
> has an optimization or two to try and cache sql). You lose the
dependency
> chain, the compile time checks -- you lose tons.
>

On the parse once, execute many point:

As we are runing mod_plsql, we lose most (all) of the session information after each page is served.

Hence our execute immediate sql will always have to parsed for each seperate page. Whilst there maybe 10 different queries to generate a page, all the queires are totally different.

So (in reference to parse once/execute many) it doesn't matter if it is dynamic or native sql - 97% of queries are already soft parsed.

A performance immprovement can probably be obtained using session_cached_cursors but this is not due to dynamic vs native sql but rather due to the stateless opperation of mod_plsql.

I hope I understand this correctly as I have spent a few days looking at your asktom website.

Your other points regarding native vs dymaic sql are well made and I will commence migrating to native sql where I can. Received on Mon Feb 21 2005 - 16:20:57 CST