STMTHEAP Issue

[Purple-D]

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!

[Serge Rielau]

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]

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/

[Serge Rielau]

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/