Hi All,
I was trying to benchmark a slow performing query and realized that
almost 83 seconds is being spent on prepare and only 0.001 sec is being
spent on execute. Dynamic package cache was flushed between each run
and I tried it for all optimization levels. for this i used a stmtheap
of 12288 x 4kb.
However I realised that as soon as I reduce the stmtheap to a lower
value say 4096, the prepare time goes down to almost 7 seconds, with no
change in the exec time.
Iam curious as to why this would happen? The query am running is
complex and I keep getting "SQL0437W Performance of this complex query
may be sub-optimal. Reason code:1"..
Iam hesitant to change this in production as it might impact other
queries which are performing well.
The environment is db2 v8 fp 10 suse linux es 9 with 8 procs.
Thanks for your inputs!
3  4830 
Purple-D wrote:
Hi All,
I was trying to benchmark a slow performing query and realized that
almost 83 seconds is being spent on prepare and only 0.001 sec is being
spent on execute. Dynamic package cache was flushed between each run
and I tried it for all optimization levels. for this i used a stmtheap
of 12288 x 4kb.
However I realised that as soon as I reduce the stmtheap to a lower
value say 4096, the prepare time goes down to almost 7 seconds, with no
change in the exec time.
Iam curious as to why this would happen? The query am running is
complex and I keep getting "SQL0437W Performance of this complex query
may be sub-optimal. Reason code:1"..
Iam hesitant to change this in production as it might impact other
queries which are performing well.
The environment is db2 v8 fp 10 suse linux es 9 with 8 procs.
Can you downgrade the optimization level for that query using the
special register?
Is the query complex? Does it use SQL Functions (many/nested)?
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference http://www.ibm.com/software/data/ond...ness/conf2006/
Yeah thats what I did,a blanket change on our prod and uat environment
to user opmization level 2, the query is complex in terms of number of
joins however does not use functions/procs. I would think that there
should be some form of heuristic which should kick in and let db2 get
on with executing the query (which takes 0.006 secs) rather than
spending 73 secs to prepare it!.
Oh well!
Serge Rielau wrote:
Purple-D wrote:
Hi All,
I was trying to benchmark a slow performing query and realized that
almost 83 seconds is being spent on prepare and only 0.001 sec is being
spent on execute. Dynamic package cache was flushed between each run
and I tried it for all optimization levels. for this i used a stmtheap
of 12288 x 4kb.
However I realised that as soon as I reduce the stmtheap to a lower
value say 4096, the prepare time goes down to almost 7 seconds, with no
change in the exec time.
Iam curious as to why this would happen? The query am running is
complex and I keep getting "SQL0437W Performance of this complex query
may be sub-optimal. Reason code:1"..
Iam hesitant to change this in production as it might impact other
queries which are performing well.
The environment is db2 v8 fp 10 suse linux es 9 with 8 procs.
Can you downgrade the optimization level for that query using the
special register?
Is the query complex? Does it use SQL Functions (many/nested)?
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Purple-D wrote:
Yeah thats what I did,a blanket change on our prod and uat environment
to user opmization level 2, the query is complex in terms of number of
joins however does not use functions/procs. I would think that there
should be some form of heuristic which should kick in and let db2 get
on with executing the query (which takes 0.006 secs) rather than
spending 73 secs to prepare it!.
Point taken. What did you have before? The recommended optimization
level for OLTP is 3. 5 is a good starting point for BI.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference http://www.ibm.com/software/data/ond...ness/conf2006/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Paul Mateer |
last post by:
Hi,
I have been running some queries against a table in a my database and
have noted an odd (at least it seems odd to me) performance issue.
The table has approximately 5 million rows and includes the following
columns:
DocID (INTEGER, PRIMARY KEY, CLUSTERED)
IsRecord (INTEGER, NONCLUSTERED)
|
by: George Hester |
last post by:
Please take a look at this google artcle:
http://groups.google.com/groups?hl=en&lr=&frame=right&th=55d6f4b50f5f9382&seekm=411f370d%241%40olaf.komtel.net#link9
The op was having trouble with access denied using resizeTo. I am having the same issue
but the explanations in this article don't seem to apply here. I am not trying to
resize a window with content from a different server. This issue lies here.
What I do is make a popup...
|
by: Anthony Cuttitta Jr. |
last post by:
We have an application that outputs several different graphs from data
downloaded from our AS400. The application has worked without (this)
issue for several months now, but just recently, the workstation it is
normally run from has been upgraded to WinXP from WinNT 5.0.
The issue is that the graphs will either display the data within the
recordsource (a query), OR it will display the "temp" data in the
chart's datasheet (from initial...
|
by: Kevin Spencer |
last post by:
Hi all,
I am working on a service that uploads METAR weather information to the
National Weather Service FTP site. The service I'm authoring is hosted on a
Windows 200 server, and the NWS FTP host is on a Unix server. I'm using the
FtpWebRequest and FtpWebResponse classes to do the file uploading. I have
tested it without any errors at all uploading to an IIS FTP server hosted on
our network Most of the time it uploads without error to...
|
by: Ben Rush |
last post by:
Hello World,
Okay, I have spent the day browsing the newsgroups and reading up on
article after article concerning ViewState corruption and so forth, and I
have a couple questions. We intermittantly get the following exception -
EXCEPTION MESSAGE:
The viewstate is invalid for this page and might be corrupted.
STACK TRACE:
| | |
by: Robert |
last post by:
I have a series of web applications (configured as separate applications) on
a server.
There is a main application at the root and then several virtual directories
that are independant applications.
I am testing an upgrade of all of the sites and have converted the main root
site...although not necessarily fixed any issues.
I move on instead and converted one of the virtual roots that is a seperate
|
by: Charles Leonard |
last post by:
I am having yet another issue with Windows Server 2003. This time, the web
service (a file import web service) appears to run except for one odd
message: "ActiveX component can't create object". There are no other
statements to indicate what object cannot be created. Otherwise, everything
on the test Windows Server 2003 works fine—all import data updates correctly.
Unfortunately, my normal development environment is not Windows...
|
by: Paul |
last post by:
Hi,
I've been struggling with this today, I'm developing a DotNet2.0 website in C# that needs to call a long running data query. Obviously this is a good candidate for an Asynchronous call, so the page can carry on rendering whilst the call is taking place and also returning the ASP.NET worker thread to service another page request (for scalability). I have developed a very basic asynchronous object (for testing, that reproduces the same...
|
by: SAL |
last post by:
Hello,
I'm trying to include a popup in the ItemTemplate of a gridview row. The
ItemTemplate for the field contains a textbox and when the user clicks in
the textbox I want a popup panel to show allowing the user to make a
selection from the popup window. I have enabled AJAX extensions and have a
working sample outside of a gridview. However, when I click in the textbox
of a gridview row, all I see is a really small square instead of the...
|
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...
|
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,...
| | |
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...
|
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
