Re: Dynamic Query

From: Gerard H. Pille <>
Date: Tue, 13 Oct 2009 20:34:16 +0200
Message-ID: <4ad4c825$0$2861$>

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:
> 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. Received on Tue Oct 13 2009 - 13:34:16 CDT

Original text of this message