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

On Jun 17, 2:16 am, trbosjek <ivan.petrov..._at_t-mobile.hr> wrote:
> Thank you Joel Garry.
>
> You have been most helpful and explained to me that I should read more
> about read consistency, and that it applies to myself too (not myself,
> but the database I'm working on, right?). I hope then I'll understand
> how read consistency becomes messed up by what I was trying to do.
>
> "I suspect you are running into bugs or misuse of bind variables"- I
> don't quite understand what you mean. Could you please post again and
> explain which bugs and which bind variables?
>
> Thanks again.

Well, no. You are welcome to explore new ways of doing things, but you must remember you then risk the consequences. We could speculate all day about bugs. Read Tom Kyte's books to see how you should be doing things. See http://www.google.com/search?hl=en&q=bind+variables+ora-1555+site%3Aasktom.oracle.com for a few examples.

My own experience (which is reaffirmed daily with "database agnostic" code) is not to try to do things dynamically unless you have a really good reason and a really well defined problem space. This goes double for features introduced beyond Oracle 7. And quadruple for spiffy tricks that are dependent on init parameters. And infinity for spiffy tricks that are dependent on undocumented parameters.

But that's just me, dealing with enterprise applications in production. Your requirements may differ. But you should worry when you see things like "(the static query uses just 5% of the latches required by the DLA)," as you may find it is even worse than Sybrand said. It's really bad to discover such things in production.

jg

--
@home.com is bogus.
They will survive.  http://www.signonsandiego.com/uniontrib/20080617/news_1b17milberg.html