By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,693 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

static and dynamic SQL

P: n/a
there is an application which issues a lot of simple dynamic queries
against an Oracle database. If CURSOR_SHARING is set to FORCE, Oracle
treats dynamic queries as static ones, for instance
SELECT * FROM SALES WHERE CUSTOMER_ID=12345
Oracle will replace 12345 with a parameter
SELECT * FROM SALES WHERE CUSTOMER_ID=:SYS_0
then it will provide 12345 (there is a very convenient setting
CURSOR_SHARING just for that). Next time a similar query is issued
SELECT * FROM SALES WHERE CUSTOMER_ID=23456,
it will be rewritten, and the execution plan from the first one will
be reused

Is there a similar setting in DB2? Otherwise lots of dynamic queries
will have to be replaced with static ones.

TIA
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Ford Desperado wrote:
there is an application which issues a lot of simple dynamic queries
against an Oracle database. If CURSOR_SHARING is set to FORCE, Oracle
treats dynamic queries as static ones, for instance
SELECT * FROM SALES WHERE CUSTOMER_ID=12345
Oracle will replace 12345 with a parameter
SELECT * FROM SALES WHERE CUSTOMER_ID=:SYS_0
then it will provide 12345 (there is a very convenient setting
CURSOR_SHARING just for that). Next time a similar query is issued
SELECT * FROM SALES WHERE CUSTOMER_ID=23456,
it will be rewritten, and the execution plan from the first one will
be reused

Is there a similar setting in DB2? Otherwise lots of dynamic queries
will have to be replaced with static ones.

TIA

To the best of my knowledge, the DB2 family does not have an internal
function to rewrite queries replacing data values with parameters.
Dynamic queries with parameters can be cached and reused to avoid the
costs of path selection and binding.

In the DB2 world, static SQL is code that is compiled and bound
separately from execution of the application. Both of your examples
would be considered dynamic.

Most business applications follow the 80/20 rule - in this case; 20% of
the queries will be 80% of the workload. That should help you determine
where to concentrate your initial efforts when making code changes.

Phil Sherman

Nov 12 '05 #2

P: n/a
AK
I'd like to add that in DB2 some more complex queries against large
tables should better be left dynamic, most notably range queries and
queries on columns with pronounced data skew.

To my best knowledge, Oracle's CURSOR_SHARING setting may also be
counter-productive, and for the same reason, correct?
Nov 12 '05 #3

P: n/a
There are changes coming in Stinger that might apply. Reop once and
reopt always. Phil


fo************@yahoo.com (Ford Desperado) wrote in message news:<e9**************************@posting.google. com>...
there is an application which issues a lot of simple dynamic queries
against an Oracle database. If CURSOR_SHARING is set to FORCE, Oracle
treats dynamic queries as static ones, for instance
SELECT * FROM SALES WHERE CUSTOMER_ID=12345
Oracle will replace 12345 with a parameter
SELECT * FROM SALES WHERE CUSTOMER_ID=:SYS_0
then it will provide 12345 (there is a very convenient setting
CURSOR_SHARING just for that). Next time a similar query is issued
SELECT * FROM SALES WHERE CUSTOMER_ID=23456,
it will be rewritten, and the execution plan from the first one will
be reused

Is there a similar setting in DB2? Otherwise lots of dynamic queries
will have to be replaced with static ones.

TIA

Nov 12 '05 #4

P: n/a
AK wrote:
I'd like to add that in DB2 some more complex queries against large
tables should better be left dynamic, most notably range queries and
queries on columns with pronounced data skew.

To my best knowledge, Oracle's CURSOR_SHARING setting may also be
counter-productive, and for the same reason, correct?


In FORCE mode, possibly (and probably) - but, there are other values for
CURSOR_SHARING, where, if the column being replaced has identifiable
skew (identified by stats etc), then the bind variable replacement
doesn't happen and the query stays with the literal. Similary in other
situations where the use of a bind variable could cause a 'worse' plan
than the use of a static literal (Actually that is the general rule,
skew would be just one example where this rule would be applied)

And then, there is also bind variable peeking as well, which can also
come into play in these types of situations, where the bind variable is
initially looked at as if it is a literal to determine selectivity etc.

And I never really grokked when and why it would happen, but I believe
that literal replacement can happen to only have the corresponding bind
variable peeked at to determine a plan based on the replaced literal
value. But that's a whole new spin and makes my head hurt.

So overall, it all depends.

Nov 12 '05 #5

P: n/a
AK
Mark,

while SELECT * FROM SALES WHERE SALE_DATE BETWEEN '1/1/2000' AND
'1/1/2004'
should be executed as a table scan (75% rows, low cluster factor),
a similar one SELECT * FROM SALES WHERE SALE_DATE BETWEEN '1/1/2000'
AND '2/1/2000' (0.5% rows, low cluster factor) sould utilize the index
on SALE_DATE. If you go for parameters, you'll get a one-size-fits-all
plan. Whichever plan the optimizer chooses, in some situations it will
be inefficient.
Scanning the table to retrieve 0.5% rows is very expensive.
Retrieveing 75% rows via the index is way more pricey than a
straightforward table scan. If SALES is big enough, it certainly pays
to compile the query every time to get the best execution plan.

What do you think?
Nov 12 '05 #6

P: n/a
AK wrote:
Mark,

while SELECT * FROM SALES WHERE SALE_DATE BETWEEN '1/1/2000' AND
'1/1/2004'
should be executed as a table scan (75% rows, low cluster factor),
a similar one SELECT * FROM SALES WHERE SALE_DATE BETWEEN '1/1/2000'
AND '2/1/2000' (0.5% rows, low cluster factor) sould utilize the index
on SALE_DATE. If you go for parameters, you'll get a one-size-fits-all
plan. Whichever plan the optimizer chooses, in some situations it will
be inefficient.
Scanning the table to retrieve 0.5% rows is very expensive.
Retrieveing 75% rows via the index is way more pricey than a
straightforward table scan. If SALES is big enough, it certainly pays
to compile the query every time to get the best execution plan.

What do you think?


I completely agree. In this situation, literal replacement is dangerous.
Hence the use of CURSOR_SHARING=FORCE, which would force literal
repalcement, would not be recommended. CURSOR_SHARING=SIMILAR may be
indicated, however, as the expectation would be that in this mode this
'unsafe' query would not have it's literal's replaced, while other
active 'safe' queries would.

Literal peeking also means that there is an opportunity for more than
one 'one size fits all' SQL plan. However, this is also not likely to be
useful in this particular example.

So once again, it all depends what is happening as to whether literal
replacement is a good idea or not. Sometimes it is, sometimes it isn't.
Hopefully the system will do a good job of working out when it should or
shouldn't apply this trick. Youse pays youse monies and youse takes
youse choice.

Nov 12 '05 #7

P: n/a
DB2 for zOS and DB2 Stinger for LUW support REOPT(ONCE) and REOPT(ALWAYS).
It does not strip literals (only the client does that in DB2 in select
cases), but it does peeking, as Mark T. calls it, either the first or
every time.
Other "argument" values fo rthsi mode are of course thinkable ...

Cheers
Serge
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.