Re: Dynamic Query
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 13 Oct 2009 20:18:09 +0200
Message-ID: <4ad4c460$0$83249$e4fe514c_at_news.xs4all.nl>
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=oracle+high+latch+free+wait+event&source=bl&ots=KN438VyP3l&sig=8Qt5KLYDu8Gy6Hg7eorSBFWWv_8&hl=en&ei=JhN7SuyeEYykMMGnvP8C&sa=X&oi=book_result&ct=result&resnum=1#v=onepage&q=oracle%20high%20latch%20free%20wait%20event&f=false
Date: Tue, 13 Oct 2009 20:18:09 +0200
Message-ID: <4ad4c460$0$83249$e4fe514c_at_news.xs4all.nl>
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=oracle+high+latch+free+wait+event&source=bl&ots=KN438VyP3l&sig=8Qt5KLYDu8Gy6Hg7eorSBFWWv_8&hl=en&ei=JhN7SuyeEYykMMGnvP8C&sa=X&oi=book_result&ct=result&resnum=1#v=onepage&q=oracle%20high%20latch%20free%20wait%20event&f=false
They suck indeed!
But:
Cursor_sharing = force= a bad idea. It MAY improve performance, but query results are unpredictable and instable.
Shakespeare Received on Tue Oct 13 2009 - 13:18:09 CDT