Re: Dynamic Query
From: The Magnet <art_at_unsu.com>
Date: Wed, 14 Oct 2009 06:26:55 -0700 (PDT)
Message-ID: <8d87139c-a5d9-4756-abba-48103208afad_at_j19g2000yqk.googlegroups.com>
On Oct 13, 3:26 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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/
Date: Wed, 14 Oct 2009 06:26:55 -0700 (PDT)
Message-ID: <8d87139c-a5d9-4756-abba-48103208afad_at_j19g2000yqk.googlegroups.com>
On Oct 13, 3:26 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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/
Well, since in this application there are few SQL statements that are actually the same, hard parsing may be the only option.
Although, would the first statement execute better than the second?
l_string = "SELECT x FROM table_name WHERE y = " || :l_input; open p_cursor for l_string using l_input1;
l_string = "SELECT x FROM table_name WHERE y = " || v_input; open p_cursor for l_string;
Or whatever the exact syntax is. Is there a difference? Would the first maybe be a soft parse? I think the problem is that most statements are unique. Received on Wed Oct 14 2009 - 08:26:55 CDT