473,842 Members | 1,601 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

dramatical performance degradation on dynamic sql over night

Hello,

we are running DB2 UDB EEE Version 7.2 Fixpack 12 on a two machine
Windows 2000 Advanced Server Cluster in a dss environment.

Some dynamic sql statements for etl processes and even some for online
user queries switched overnight from some minutes runtime to a few
hours or "never come back" statements.

We didn't change a single database or instance parameter. Even the
statistics are still the same for the involved queries. Unfortunately
we cannot compare the before and after access plans because we only
have the actual ones. Query rewriting and index optimization helped us
to tune the bad performing dynamic sqls but we still see bad
performing statements which used to run well.

Most (not all) of the statements are using inner joins and left outer
joins. Some of the tables have more than 100 million rows. However the
statements used to perform well some days ago without changing
parameters. Even the reorgchk output seems to be ok.

My questions:
Has anybody seen a similar behaviour in his/her environment?
What else could affect the access plan for dynamic sql statements?
Are there any issues besides the access plans which may be caused the
performance degradation?

thank you
Florian
Nov 12 '05 #1
6 3538
"florian" <au*****@web.de > wrote in message
news:35******** *************** ***@posting.goo gle.com...
Hello,

we are running DB2 UDB EEE Version 7.2 Fixpack 12 on a two machine
Windows 2000 Advanced Server Cluster in a dss environment.

Some dynamic sql statements for etl processes and even some for online
user queries switched overnight from some minutes runtime to a few
hours or "never come back" statements.

We didn't change a single database or instance parameter. Even the
statistics are still the same for the involved queries. Unfortunately
we cannot compare the before and after access plans because we only
have the actual ones. Query rewriting and index optimization helped us
to tune the bad performing dynamic sqls but we still see bad
performing statements which used to run well.

Most (not all) of the statements are using inner joins and left outer
joins. Some of the tables have more than 100 million rows. However the
statements used to perform well some days ago without changing
parameters. Even the reorgchk output seems to be ok.

My questions:
Has anybody seen a similar behaviour in his/her environment?
What else could affect the access plan for dynamic sql statements?
Are there any issues besides the access plans which may be caused the
performance degradation?

thank you
Florian


Make sure you have sufficient sizes for your system temporary tablespaces.
Sometimes when they are not large enough, the queries can take a lot longer.
Nov 12 '05 #2
Has anything changed at the system level? If you haven't changed any
db2 parameter settings, and it has the old stats then plans can not
change. I'd suggest looking at your system first and try to understand,
with the various perf monitors that Windows provides, if the system is
performing badly. May be a disk has gone bad on a raid array? may be
you are using a lot more paging now, etc.

au*****@web.de (florian) writes:
Hello,

we are running DB2 UDB EEE Version 7.2 Fixpack 12 on a two machine
Windows 2000 Advanced Server Cluster in a dss environment.

Some dynamic sql statements for etl processes and even some for online
user queries switched overnight from some minutes runtime to a few
hours or "never come back" statements.

We didn't change a single database or instance parameter. Even the
statistics are still the same for the involved queries. Unfortunately
we cannot compare the before and after access plans because we only
have the actual ones. Query rewriting and index optimization helped us
to tune the bad performing dynamic sqls but we still see bad
performing statements which used to run well.

Most (not all) of the statements are using inner joins and left outer
joins. Some of the tables have more than 100 million rows. However the
statements used to perform well some days ago without changing
parameters. Even the reorgchk output seems to be ok.

My questions:
Has anybody seen a similar behaviour in his/her environment?
What else could affect the access plan for dynamic sql statements?
Are there any issues besides the access plans which may be caused the
performance degradation?

thank you
Florian


--
Regards,
--
Haider
Nov 12 '05 #3
"florian" <au*****@web.de > wrote in message
news:35******** *************** ***@posting.goo gle.com...
Hello,

we are running DB2 UDB EEE Version 7.2 Fixpack 12 on a two machine
Windows 2000 Advanced Server Cluster in a dss environment.

Some dynamic sql statements for etl processes and even some for online
user queries switched overnight from some minutes runtime to a few
hours or "never come back" statements.


I've occasionally seen such behaviour. Adding RUNSTATs jobs into key bits of
the ETL fixed the problem, however I've never been sure about the root
cause.

The immediate cause is differing access plans, but whether this is because
the optimiser has poor statistics and is on a knife edge between two
separate plans - one a good one in practice and another a very bad one. In
this case, a few more rows in one table might be enough to push the
optimiser to choose the good plan to the bad one.

The solution here is to improve the stats that the optimiser is working
with. For example look at some of the estimated filter factors and
cardinalities in your access plans. If these are way out to what you know
the real filter factors and cardinalities to be, look to RUNSTATs with more
freqvalues and quantiles, or in extreme cases, manually alter the stats so
that the calculated filter factors, cardinalities etc look better.

The other possible root cause is a suspicion that the optimiser might look
at SYSCAT.TABLES.S TATS_TIME and then if the timestamp is 'too old' it decide
to fabricate statistics instead. I've never actually found out if the
optimiser does indeed do something like that, because as I said, I tend to
include RUNSTATs in my ETL (when it is not too costly to do such). If
STATS_TIME is a factor, then update some stats column to a new value to get
the STATS_TIME to be updated.

Regards
Paul Vernon
Business Intelligence, IBM Global Services
Nov 12 '05 #4
"Haider Rizvi" <ha****@nouce.c a.ibm.com> wrote in message
news:7z******** ****@thinkhr.to rolab.ibm.com.. .
Has anything changed at the system level? If you haven't changed any
db2 parameter settings, and it has the old stats then plans can not
change.


Is that 100% true?

Looking in the manuals for a whiff of my suspicion about STATS_DATE, all I
could find is this statement.

"If RUNSTATS is not executed or the optimizer suspects that RUNSTATS was
executed on empty or nearly empty tables, it may either use defaults or
attempt to derive certain statistics based on the number of file pages used
to store the table on disk (FPAGES)."

However, that does imply that access plans can change solely due to I/U/D
activity. I.e. if you runstat a 'nearly empty table' the you will get one
set of access plans. If you then insert lots of rows into said table, DB2
may decide to derive certain statistics instead for subsequent queries, so
by producing different access plans without any change in RUNSTATs
statistics.

Regards
Paul Vernon
Business Intelligence, IBM Global Services
Nov 12 '05 #5
"Paul Vernon" <pa*********@uk k.ibmm.comm> writes:
"Haider Rizvi" <ha****@nouce.c a.ibm.com> wrote in message
news:7z******** ****@thinkhr.to rolab.ibm.com.. .
Has anything changed at the system level? If you haven't changed any
db2 parameter settings, and it has the old stats then plans can not
change.
Is that 100% true?


You are right about this possibility. I was keying off of Florian's
statement that stats had not changed, also implicitly assuming that the
runstats was done on a reasonably loaded table.

Looking in the manuals for a whiff of my suspicion about STATS_DATE, all I
could find is this statement.

"If RUNSTATS is not executed or the optimizer suspects that RUNSTATS was
executed on empty or nearly empty tables, it may either use defaults or
attempt to derive certain statistics based on the number of file pages used
to store the table on disk (FPAGES)."

However, that does imply that access plans can change solely due to I/U/D
activity. I.e. if you runstat a 'nearly empty table' the you will get one
set of access plans. If you then insert lots of rows into said table, DB2
may decide to derive certain statistics instead for subsequent queries, so
by producing different access plans without any change in RUNSTATs
statistics.

Regards
Paul Vernon
Business Intelligence, IBM Global Services


--
Regards,
--
Haider
Nov 12 '05 #6
Mark, Haider, Paul

thank you very much for your fast reply.
Make sure you have sufficient sizes for your system temporary tablespaces.Sometimes when they are not large enough, the queries can take a lot longer.

this could be an issue, sometimes we see a completely filled tempspace
with 20 GB

I'd suggest looking at your system first and try to understand,
with the various perf monitors that Windows provides, if the system isperforming badly. May be a disk has gone bad on a raid array? may be
you are using a lot more paging now, etc.
sometimes i wish we had a unix system which makes it easier to get os
performance information.
It is always hard to find performance changes if you don't have a
history of "normal" operation performance values. But i agree that it
is time to look at system level now, too.

The immediate cause is differing access plans....


Our Runstats are collected every 2 weeks. Most of the tables have
millions of rows and change within 2 weeks a few ten to hundred
thousand rows so that the statistics are close to the real world. But
i think your suggestion is a very good one and the access path could
be the key here to solve our problems.

On another machine some month ago we had the same behaviour of a
dynamic sql statement where runtime decreased from 7 minutes to 3
hours. I tuned it by rewriting (left outer join to fullselect was
possible) and got it back to 4 minutes. After one week the new
statement needed again 2-3 hours and we tried the old one which
finished again in 8 minutes. But in that case the statistics changed
slighly overtime and i was able to find the two points of time where
another access plan was chosen, so we think we understand what
happened.

I am going to create some test tables and use the problem queries on
them. I am interested in seeing access plan changes after updating the
statistics manually.

I will keep you updated and appreciate your help

Thanks again
Florian
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1961
by: Przemyslaw Mazur | last post by:
Hello. I use postgress to manage a small amount of data (ca. 25MB), but often changed. This is a mirror of several tables taken form four different databases. All data are updated once per hour (or sometimes on demand) Update of every table is closes within a transaction (constant data availability is most important):
4
1943
by: Jason Heyes | last post by:
What can I do to circumvent the performance degradation associated with dynamic allocation and small objects? Thanks.
0
1203
by: Andrew Mayo | last post by:
This problem was discovered with MSDE2000 SP2 and under WinXP SP2. We are unsure whether it is more widespread as it has only been seen on one machine to date. The problem is related to name resolution. If you attempt to connect to a local database with a connect string using server=. rather than
6
2327
by: teedilo | last post by:
We have an application with a SQL Server 2000 back end that is fairly database intensive -- lots of fairly frequent queries, inserts, updates -- the gamut. The application does not make use of performance hogs like cursors, but I know there are lots of ways the application could be made more efficient database-wise. The server code is running VB6 of all things, using COM+ database interfaces. There are some clustered and non-clustered...
3
1712
by: adsheehan | last post by:
Hi all, Wondering if a GIL lock/unlock causes a re-schedule/contect swap when embedding Python in a multi-threaded C/C++ app on Unix ? If so, do I have any control or influence on this re-scheduling ? The app suffers from serious performance degradation (compared to pure c/C++) and high context switches that I suspect the GIL unlocking may be aggravating ?
7
3245
by: Kevin Wan | last post by:
Hello, Would anyone explain why there is a consistent large performance degradation with the dumb copy? Thanks in advance! array_copy_dumb.c: /* array_copy_dumb.c */
22
3366
by: Kevin Murphy | last post by:
I'm using PG 7.4.3 on Mac OS X. I am disappointed with the performance of queries like 'select foo from bar where baz in (subquery)', or updates like 'update bar set foo = 2 where baz in (subquery)'. PG always seems to want to do a sequential scan of the bar table. I wish there were a way of telling PG, "use the index on baz in your plan, because I know that the subquery will return very few results". Where it really matters, I have...
6
1480
by: Joachim Worringen | last post by:
I need to process large lists (in my real application, this is to parse the content of a file). I noticed that the performance to access the individual list elements degrades over runtime. This can be reproduced easily using this code: import time N=100000 p=10000
0
1888
by: datapro01 | last post by:
Running DB2 8.2.6 on AIX 5.3 I recently took on an assignment supporting a DB2 database which supports a Siebel application. Siebel version is 7.5.3 I have a reorg script that carefully follows Siebel guidance on reorgs and have had great success with it at two other locations. Quick background; entire database had NEVER been reorged in 6 years
0
9872
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9715
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10945
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10612
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10672
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9453
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
4499
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4089
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3145
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.