Dynamic Query

#1

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=14O...0event&f=false

#2

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=14O...0event&f=false

They suck indeed!

But:

Cursor_sharing = force= a bad idea. It MAY improve performance, but
query results are unpredictable and instable.

Shakespeare

#3

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=14O...0event&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.

#4

On Oct 13, 10:32*am, The Magnet <a... (AT) unsu (DOT) 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=14O...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/...42700346593146
and http://asktom.oracle.com/pls/asktom/...:2320123769177
and http://jonathanlewis.wordpress.com/2...ind-variables/ and
the various tracing tutorials floating about like
http://www.oracle-base.com/articles/...dTkprof10g.php
..

jg
--
@home.com is bogus.
Oh man, dumbass MS
http://www3.signonsandiego.com/stori...hes/?uniontrib

#5

On Oct 13, 11:34*am, "Gerard H. Pille" <g... (AT) skynet (DOT) 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=14O...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/thre...71400&tstart=0

jg
--
@home.com is bogus.
http://fuzzydata.wordpress.com/about/

#6

On Oct 13, 3:26*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

Quote:

On Oct 13, 11:34*am, "Gerard H. Pille" <g... (AT) skynet (DOT) 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=14O...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/thre...71400&tstart=0

jg
--
@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.

#7

On Oct 14, 6:26*am, The Magnet <a... (AT) unsu (DOT) com> wrote:

Quote:

On Oct 13, 3:26*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

On Oct 13, 11:34*am, "Gerard H. Pille" <g... (AT) skynet (DOT) 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 createsa
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=14O...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 sharingforce is the best he can do.

Also see this: *http://forums.oracle.com/forums/thre...71400&tstart=0

jg
--
@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
--
@home.com is bogus.
"Jonestown lite" http://www3.signonsandiego.com/stori...&zIndex=182375

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Dynamic Query

comp.databases.oracle.misc comp.databases.oracle.misc

Dynamic Query - 10-13-2009 , 12:32 PM

Re: Dynamic Query - 10-13-2009 , 01:18 PM

Re: Dynamic Query - 10-13-2009 , 01:34 PM

Re: Dynamic Query - 10-13-2009 , 01:44 PM

Re: Dynamic Query - 10-13-2009 , 03:26 PM

Re: Dynamic Query - 10-14-2009 , 08:26 AM

Re: Dynamic Query - 10-14-2009 , 12:17 PM