472,354 Members | 1,451 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,354 software developers and data experts.

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 3413
"florian" <au*****@web.de> wrote in message
news:35**************************@posting.google.c om...
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.google.c om...
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.STATS_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.ca.ibm.com> wrote in message
news:7z************@thinkhr.torolab.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*********@ukk.ibmm.comm> writes:
"Haider Rizvi" <ha****@nouce.ca.ibm.com> wrote in message
news:7z************@thinkhr.torolab.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
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...
4
by: Jason Heyes | last post by:
What can I do to circumvent the performance degradation associated with dynamic allocation and small objects? Thanks.
0
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...
6
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...
3
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...
7
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
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...
6
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...
0
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...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made but the http to https rule only works for...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. header("Location:".$urlback); Is this the right layout the...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...

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.