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

STMTHEAP Issue

P: n/a
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!

Oct 3 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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/
Oct 3 '06 #2

P: n/a
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/
Oct 4 '06 #3

P: n/a
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/
Oct 4 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.