Re: Dynamic Query

From: joel garry <joel-garry_at_home.com>
Date: Wed, 14 Oct 2009 10:17:16 -0700 (PDT)
Message-ID: <b724c0bd-1063-4deb-845b-5edfae682d78_at_y28g2000prd.googlegroups.com>



On Oct 14, 6:26 am, The Magnet <a..._at_unsu.com> wrote:
> 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.

This is where you come up with DDL and data for a complete test that people can help you with.

jg

--
_at_home.com is bogus.
"Jonestown lite" http://www3.signonsandiego.com/stories/2009/oct/14/us-sweat-lodge-deaths-101409/?northcounty&zIndex=182375
Received on Wed Oct 14 2009 - 12:17:16 CDT

Original text of this message