Forum .LRN Q&A: Re: performance optimization for dotLRN portlets

Collapse
Posted by Dirk Gomez on
Caroline,

I don't like the fact that there are hard-coded values in every SQL query you posted.

If you don't use bind variables every single statement needs to be parsed - which may lead to a huge scalability issue. Parsing a query is quite costly in Oracle because a lot of things are being done. The Cost-Based Optimizer tries to figure the most efficient way of performing a query and traverses a tree of possible execution plans, needs to check permissioning etc. There's a cut-off somewhere, but it is entirely likely that a prominent %age of time of each above-mentioned query is taken for parsing alone.

(It also has an effect on concurrency, read this for some gory details:
http://asktom.oracle.com/pls/ask/f?p=4950:8:43470802654141231::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2444907911913,)

I suggest that you switch to using transaction-temporary tables or look at this http://asktom.oracle.com/pls/ask/f?p=4950:8:43470802654141231::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:210612357425,