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 7 6269
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
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?
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
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.
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?
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Dumitru Sipos |
last post by:
Hello everybody!
is there possible to have a function that is both static and virtual?
Dumi.
|
by: Krivenok Dmitry |
last post by:
Hello all!
Perhaps the most important feature of dynamic polymorphism is
ability to handle heterogeneous collections of objects.
("C++ Templates: The Complete Guide" by David Vandevoorde
and...
|
by: pittendrigh |
last post by:
There must be millions of dynamically generated
html pages out there now, built by on-the-fly php code
(and jsp, perl cgi, asp, etc).
Programatic page generation is transparently useful.
But...
|
by: Ken |
last post by:
In C programming, I want to know in what situations we should use
static memory allocation instead of dynamic memory allocation. My
understanding is that static memory allocation like using array...
|
by: Jo |
last post by:
Hi,
How can i differentiate between static and dynamic allocated objects?
For example:
void SomeFunction1() {
CObject *objectp = new CObject;
CObject object;
|
by: Philipp |
last post by:
Hello
I don't exactly understand why there are no static virtual functions. I
would have liked something like this:
class Base{
static virtual std::string getName(){
return "Base";
}
}
|
by: Tarscher |
last post by:
hi all,
I have this seemingly simple problem. I have lost a lot of time on it
though.
When a user selects a value from a dropdownlist (static control) a
dynamic control is generated. I have...
|
by: JohnQ |
last post by:
The way I understand the startup of a C++ program is:
A.) The stuff that happens before the entry point.
B.) The stuff that happens between the entry point and the calling of
main().
C.)...
|
by: gs |
last post by:
Hi,
I want to know that when memory get allocated to static data member of
a class.
class A
{
static int i;
}
|
by: =?ISO-8859-1?Q?Tim_B=FCthe?= |
last post by:
Hi,
we are building a Java webapplication using JSF, running on websphere,
querying a DB2 9 on Suse Enterprise 10. The app uses JDBC and
PreparedStatements only (aka dynamic SQL). Every night,...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |