Re: Dynamic Query

From: joel garry <joel-garry_at_home.com>
Date: Tue, 13 Oct 2009 13:26:05 -0700 (PDT)
Message-ID: <fa3c98c8-cf11-443e-8bff-defcac18787e_at_p10g2000prm.googlegroups.com>

On Oct 13, 11:34 am, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> Shakespeare wrote:
> > The Magnet schreef:
> >> Dynamic queries suck! But, our setup is that we have a table with
> >> application stored queries. PHP calls a stored procedure, passing it
> >> parameters as well as the name of the query it needs. The stored
> >> procedure goes into a table, gets the SELECT statement and creates a
> >> dynamic query based on the parameters passed and then opens up a REF
> >> CURSOR for PHP to read. Here is an example:
>
> >> begin for_php.open_php(:data,'ba','SHAREPRICE',:col);end;
>
> >> So, the query is dynamic which means a hard parse, right? 90% of our
> >> application is written this way. It has really sent the latch waits
> >> through the roof, which probably causes performance issues.
>
> >> We've set CURSOR_SHARING = FORCE to help us, but we'd really like to
> >> bring the parse count down. Since these are all dynamic, and are not
> >> re-used often since the parameters, are we SOL?
>
> >> Been reading pages like this to look for answers:
>
> >>http://books.google.com/books?id=14OmJzfCfXMC&pg=PA148&lpg=PA148&dq=o...
>
> > They suck indeed!
>
> > But:
>
> > Cursor_sharing = force= a bad idea. It MAY improve performance, but
> > query results are unpredictable and instable.
>
> > Shakespeare
>
> Don't hold back the better ideas, Zak!   Until then, cursor sharing force is the best he can do.

Also see this: http://forums.oracle.com/forums/thread.jspa?threadID=971400&tstart=0

jg

--
_at_home.com is bogus.
http://fuzzydata.wordpress.com/about/

Received on Tue Oct 13 2009 - 15:26:05 CDT