Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Plan table differences - trying to improveperformance.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 41
Location: Amsterdam

PostPosted: Fri Dec 02, 2011 7:12 pm    Post subject: Plan table differences - trying to improveperformance.
Reply with quote

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:
Code:
SELECT COUNT (*) INTO :H
FROM "T02" AA02,  "T04" AA04, "TAC"
ACB,   "T12" AA12
WHERE                         
AA02."FAA041VN"    = AA04."VOLGORDER" AND     
AA04."FAA081TY"    = :H               AND                                 
AA02."DATUM"      >= :H               AND                         
AA02."DATUM"      <= :H               AND                         
AA02."OWNER"       =
ACB."NUMMER"     AND                               
ACB."BN"           = :H               AND
AA02."FAA121KD"    = AA12."KODE"      AND
AA12."VB           = :H                 


Which creates the following entries in the Plan Table during the compile:
Code:
QUERYNO   METHOD   TNAME   TABNO   ACCESSTYPE   MATCHCOLS   ACCESSNAME
2644   0   T12   4   R      0   
2644   1   T02   1   M      0   
2644   1   T02   1   MI      0   
2644   1   T02   1   MX      1      XAA02AH1
2644   1   T02   1   MX      1      XAA02AH5
2644   1   TAC   3   I      2      XACBEHA2
2644   1   T04   2   I      1      XAA04BS1


If I personally run an Explain via Spufi I get different results.
Code:
QUERYNO   METHOD   TNAME   TABNO   ACCESSTYPE   MATCHCOLS   ACCESSNAME
2644   0   T04   2   I      1      XAA04BSZ
2644   1   T02   1   I      1      XAA02AH4
2644   1   TAC   3   I      2      XACBEHA2
2644   4   T12   4   I      1      XAA12MY1


If I look at the acces to T04 for example, the Explain version uses XAA04BSZ:
Code:
  CREATE INDEX XAA04BSZ
    ON T04
           (FAA081TY              ASC,
             LASTUPDATED_TIMEST    ASC) 


The PlanTable version uses:
Code:
  CREATE UNIQUE INDEX XAA04BS1     
    ON T04
     (VOLGORDER             DESC)   


I can of course post the DDL but I thought this was already long enough!

Daniel. icon_rolleyes.gif
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Dec 02, 2011 7:30 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 41
Location: Amsterdam

PostPosted: Fri Dec 02, 2011 7:42 pm    Post subject:
Reply with quote

icon_eek.gif 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 icon_biggrin.gif

Dan.
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Fri Dec 02, 2011 7:46 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 41
Location: Amsterdam

PostPosted: Fri Dec 02, 2011 7:49 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Dec 06, 2011 2:43 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Bind plan as a member to another bind... rexx77 DB2 0 Thu Feb 16, 2017 2:02 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us