 |
|
| Computers Forum Index » Computer - Databases - Oracle (Misc) » Dynamic Query... |
|
Page 1 of 1 |
|
| Author |
Message |
| The Magnet... |
 Posted: Tue Oct 13, 2009 5:32 pm |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
| joel garry... |
| Posted: Tue Oct 13, 2009 6:44 pm |
|
|
|
Guest
|
On Oct 13, 10:32 am, The Magnet <a... at (no spam) unsu.com> wrote:
Quote: 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/ and
the various tracing tutorials floating about like
http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php
..
jg
--
at (no spam) home.com is bogus.
Oh man, dumbass MS
http://www3.signonsandiego.com/stories/2009/oct/13/sidekick-users-distraught-personal-data-vanishes/?uniontrib |
|
|
| Back to top |
|
|
|
| joel garry... |
| Posted: Tue Oct 13, 2009 8:26 pm |
|
|
|
Guest
|
On Oct 13, 11:34 am, "Gerard H. Pille" <g... at (no spam) skynet.be> wrote:
Quote: 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 (no spam) home.com is bogus.
http://fuzzydata.wordpress.com/about/ |
|
|
| Back to top |
|
|
|
| Shakespeare... |
| Posted: Tue Oct 13, 2009 10:18 pm |
|
|
|
Guest
|
The Magnet schreef:
Quote: 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 |
|
|
| Back to top |
|
|
|
| Gerard H. Pille... |
| Posted: Tue Oct 13, 2009 10:34 pm |
|
|
|
Guest
|
Shakespeare wrote:
Quote: 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
Don't hold back the better ideas, Zak! Until then, cursor sharing force is the best he can do. |
|
|
| Back to top |
|
|
|
| The Magnet... |
| Posted: Wed Oct 14, 2009 1:26 pm |
|
|
|
Guest
|
On Oct 13, 3:26 pm, joel garry <joel-ga... at (no spam) home.com> wrote:
Quote: On Oct 13, 11:34 am, "Gerard H. Pille" <g... at (no spam) 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 (no spam) 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. |
|
|
| Back to top |
|
|
|
| joel garry... |
| Posted: Wed Oct 14, 2009 5:17 pm |
|
|
|
Guest
|
On Oct 14, 6:26 am, The Magnet <a... at (no spam) unsu.com> wrote:
Quote: On Oct 13, 3:26 pm, joel garry <joel-ga... at (no spam) home.com> wrote:
On Oct 13, 11:34 am, "Gerard H. Pille" <g... at (no spam) 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 (no spam) 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 (no spam) home.com is bogus.
"Jonestown lite" http://www3.signonsandiego.com/stories/2009/oct/14/us-sweat-lodge-deaths-101409/?northcounty&zIndex=182375 |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Thu Nov 26, 2009 2:30 pm
|
|