Plan table differences - trying to improveperformance.

Daniel Prosser · New User Joined: 05 Nov 2010 Posts: 57 Location: Amsterdam

Hi, another fine Friday to you all :-)

We have a Cobol program performing badly. I am trying to improve the DB access.

If I compare the DB access in the Plan Table created when the program is compiled to what I can get via an Explain (in Spufi) the differences are significant.

I'm using the same SQL (taken directly from the package) The only difference is that I have replaced the :Host Variables with actual values.

I'm running the Explain against the same DB.

Can the use of values instead of :H really be the reason for such a large difference?

And if so, is there no way I can encourage the compiler to behave differently?

Any ideas much appreciated.

Here are the details.

The stmt I am looking at is:

dbzTHEdinosauer · Posted: Fri Dec 02, 2011 7:30 pm

i learned this on a db2 forum / thx guys

explain of a bind with a literal,
allows all the attributes for a column to be considered.

explain of a bind with a place-holder ?,
as with a host-variable,
does not allow all attributes for a column to be considered.

without a value (LITERAL) in the sql for the bind of the explain to use,
i believe the plan will be the same.

Daniel Prosser · New User Joined: 05 Nov 2010 Posts: 57 Location: Amsterdam

Well you're absolutely right. Now my Explain is at least the same as the Plan Table.

Now I just got to try and improve the stmt.

Thanks a million

Dan.

Ronald Burr · Posted: Fri Dec 02, 2011 7:46 pm

The difference may have to do with whether RUNSTATS has been run since the original plan bind was done. When you bind a plan (static), the DB2 optimizer uses the runstats that were current AT THE TIME the plan was bound - whereas for SPUFI, QMF, etc., since the SQL is considered to be dynamic, the optimizer does a bind using runstats that are current at run-time.
If the runstats at run-time for SPUFI, etc. are significantly different than those that were in effect when the plan was bound, it is quite likely that different access paths will be chosen by the optimizer.
The differences can be significant for tables with high-volitility and/or high-activity.

Daniel Prosser · New User Joined: 05 Nov 2010 Posts: 57 Location: Amsterdam

Thanks Ronald, but I made sure the runstats were up to date.

dbzTHEdinosauer suggested using place holders "?" instead of actual values in my Explain and that did the trick, now the PlanTable and the Explain are consistent. Unfortunately the access is not ideal :-)

Cheers.

GuyC · Posted: Tue Dec 06, 2011 2:43 pm

the reason for different acess paths : Skewed Data.
For some columns DB2 keeps the top most occurring values.
suppose in this case for column
AA04."FAA081TY" = :H
has 5 values :
60% are 'A'
30% are 'B'
5% are 'C'
2% are 'D'
1% is 'E'

Now an SQL containing FAA081TY = 'E'is totally different from one containing : FAA081TY = 'A'
And when DB2 knows that it will take the best Accesspath.
An SQL containing FAA081TY = ? will take an average of 20% and look for the best accesspath for that.

so far the explanation

Now there are techniques to get DB2 to use an index or a certain Table as first in a JOIN. But that would take a Tuning course .
adding OR 0=1 to some predicates is a possibility