Re: Dynamic Query
From: joel garry <joel-garry_at_home.com>
Date: Tue, 13 Oct 2009 11:44:04 -0700 (PDT)
Message-ID: <af307aa6-a425-40d4-961f-471b04de3f2c_at_z4g2000prh.googlegroups.com>
On Oct 13, 10:32 am, The Magnet <a..._at_unsu.com> wrote:
> 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...
Date: Tue, 13 Oct 2009 11:44:04 -0700 (PDT)
Message-ID: <af307aa6-a425-40d4-961f-471b04de3f2c_at_z4g2000prh.googlegroups.com>
On Oct 13, 10:32 am, The Magnet <a..._at_unsu.com> wrote:
> 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...
Could be, though it is possible that your program is written correctly. In essence, if the PHP is generating absolutely identical SQL, you may not be SOL. Kind of a low chance, but the only way to know for sure is to trace the sessions and watch what the bind variables are doing, look at child cursor statistics and statspack, and you can kinda get an idea with EM by looking at the cursors opened.
See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:580642700346593146 and http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2320123769177 and http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/ andthe various tracing tutorials floating about like http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php .
jg
-- _at_home.com is bogus. Oh man, dumbass MS http://www3.signonsandiego.com/stories/2009/oct/13/sidekick-users-distraught-personal-data-vanishes/?uniontribReceived on Tue Oct 13 2009 - 13:44:04 CDT