Re: How to prevent using shared pool with dynamic sql pivoting?

On Jun 16, 11:55 am, trbosjek <ivan.petrov..._at_t-mobile.hr> wrote:
> While not being polite at all, your answer is the only one so far.
> This is a Data Warehouse environment. Not some OLTP. Even if there
> would be a 5 minute parsing of a statement(which I assume is
> impossible), it would be acceptable when the whole select takes half
> an hour to finish. It's been over a decade since RDBMSs have ceased to
> be in favor of only some ATM machines and other stuff like this. And,
> yes before posting this question I have spent ten days trying to find
> a way NOT TO FLUSH THE WHOLE SHARED POOL, but only the part involved
> in this. No luck, but even flushing the whole shared pool would not do
> any harm in this environment(it’s a staging area for ETL). Why on
> earth is it that people always assume that there is some “Forms
> Application” that hundreds and thousands of people use on their client
> side and issue the same or almost the same statement over an over
> again? Please do not respond any more unless you have some
> constructive advice to share.

Both of Sybrand's responses are correct, if a bit harsh. What you seem to be missing is in the first link you posted - it doesn't work reliably and even the people who posted it don't understand why. Oracle goes to great lengths to optimize cursor usage in a manner that allows read consistency, and even allows people to play with cursor_sharing to kind-of fix really bad code. I suspect you are running into bugs or misuse of bind variables, and suggest you start mining asktom.oracle.com for nuggets of information about how to use bind variables, or just wait for 11g where all the problems become more clear.

Read consistency can apply to yourself, too, it doesn't take thousands of clients. Find Tom's discussion about using too many commits to shoot yourself in the foot with ora-15xx.

jg

--
@home.com is bogus.
http://lauren.vortex.com/archive/000389.html