IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Tuning SQL query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Tue Aug 09, 2011 10:24 pm
Reply with quote

Hi,

I want to tune teh below SQL query so that it takes less time and CPU usage.
This query has a very high IO Wait time as I observed.


Code:
SELECT COUNT(*),                                       
           MAX(F.TOT_SAL_QTY)                               
    INTO :WS-RL12-TRN-COUNT,                               
         :WS-RL12-TOT-SAL-QTY:WS-RL12-SAL-QTY-NULL         
    FROM LOWES.E264G_WK_LCT_ITM A,                         
         LOWES.E264G_WK_LCT_ITM B,                         
         LOWES.T063A_LCT C,                                 
         LOWES.E131E_DAY_LCT_ITM F                         
    WHERE A.T063_LCT_NBR = C.DC_LCT_NBR                     
      AND B.T024_ITM_NBR = :E264G-REC.T024-ITM-NBR         
      AND A.T024_ITM_NBR = B.T024_ITM_NBR                   
      AND B.T063_LCT_NBR = C.T063_LCT_NBR                   
      AND C.DC_LCT_NBR = :E264G-REC.T063-LCT-NBR           
      AND F.T024_ITM_NBR = B.T024_ITM_NBR                   
      AND F.T063_LCT_NBR = B.T063_LCT_NBR                   
      AND F.E131D_WK_END_DT = B.E264A_WK_END_DT             
      AND B.E264A_WK_END_DT BETWEEN :WS-FIS-WK-END-DT AND   
          :T625-REC.FSC-WK-END-DT                           
      AND A.E264A_WK_END_DT = B.E264A_WK_END_DT   
      AND B.ITM_LCT_STK_IDC = 'N'                 
      AND A.ITM_LCT_STK_IDC = 'Y'         


Any ideas on this?
Thanks
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1050
Location: Richmond, Virginia

PostPosted: Wed Aug 10, 2011 6:46 am
Reply with quote

Well, have you experimented at all?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Aug 10, 2011 6:57 am
Reply with quote

Hello,

Quote:
Any ideas on this?
Yes - you may be on your own until you post somethng someone can use to help you.

How many rows are in each table? What are the indes definitions for each table? What does an EXPLAIN show? How many of your similar processes work acceptable? What does your database support have to say? Is there some business reason this all needs to be one SELECT (programmer preference is not a business requirement)?
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India

PostPosted: Wed Aug 10, 2011 6:09 pm
Reply with quote

Or may be, send a request to your DBA to run an EXPLAIN on the query, to get you started.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Thu Aug 11, 2011 12:37 am
Reply with quote

Hello,

Sorry for the delayed response due to the offshore time difference. Few of the points I would like to share is below:

1. This is a self join query which is used in a production program. I believe the condition which creates the challenge is
A.T063_LCT_NBR = C.DC_LCT_NBR
and B.T063_LCT_NBR = C.T063_LCT_NBR as for each selection for the first condition it will have to read the whole table C to find a match for the second condition

2. Index for table C is only 1 clolumn C.T063_LCT_NBR and for Table A,B they have 4 columns defined for index out of which we have the 2 columns A.T024_ITM_NBR and A.T063_LCT_NBRwhich are used in this query.


3. The jobstep with this program runs for 3-4 hrs and I checked in DB2- detector that the maximum time is consumed by this query. Further I checked that the IO Wait time is very high close to 2 hrs. I paste below the relvant information from the past production runs.

Code:

TIMEPCT CPUPCT  INDB2_TIME   INDB2_CPU   
------- ------- ------------ ------------
 15.06%  58.55% 10:34.737340 05:58.839748
 74.18%  21.67% 52:04.973786 02:12.812137----------> OUR Query


                                                                               
 SQL Statement Information                                                     
  SQL_CALL         -> SELECT              STMT#            -> 01217             
  SECT#            -> 00007               SQL              -> 921               
  TIMEPCT          -> 74.18%              CPUPCT           -> 21.67%           
  INDB2_TIME       -> 52:04.973786        INDB2_CPU        -> 02:12.812137     
                                                                               
 Buffer Manager Activity                                                       
  GETPAGE          -> 11776413            GETPFAIL         -> 0                 
  SYNCREAD         -> 584124              SPFETCH          -> 0                 
  LPFETCH          -> 0                   DYNPFETCH        -> 599368           
  PFPAGES          -> 272786              PAGEUPDT         -> 19139             
  IMWRITE          -> 0                   HPREAD           -> 0                 
 HPREADF          -> 0                   HPREADPGS        -> 0                 
 HPWRITE          -> 0                   HPWRITEF         -> 0                 
 EXPANS           -> 0                   REOPT            -> 0                 
                                                                               
Prepare Statement Cache Activity                                               
 PSC_MATCH        -> 0                   PSC_NMATCH       -> 0                 
 PSC_IPREP        -> 0                   PSC_KDPREP       -> 0                 
                                                                               
RID List Processing                                                           
 RID_USED         -> 0                   RID_FSTG         -> 0                 
 RID_FLIM         -> 0                                                         
                                                                               
.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .
 Locking (IRLM) Activity                                                       
  DRAINREQ         -> 0                   DRAINFAIL        -> 0                 
  CLAIMREQ         -> 312                 CLAIMFAIL        -> 0                 
  DEADLOCK         -> 0                   TIMEOUT          -> 0                 
  ESCALSHR         -> 0                   ESCALEXC         -> 0                 
  LOCKSUS          -> 0                   LATCHSUS         -> 1                 
  OTHERSUS         -> 0                   LOCKREQ          -> 1184             
  UNLKREQ          -> 269                 CHANGREQ         -> 0                 
  QUERYREQ         -> 0                   OTHERREQ         -> 0                 
                                                                               
 Data Sharing Locking Activity                                                 
  GLOCKREQ         -> 0                   GCHNGREQ         -> 0                 
 .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .
 Wait Times And Counts                                                         
  IO_WTIME         -> 48:01.574908        IOWCNT           -> 584124           
  LOCK_WTIME       -> 00:01.313267        LOCKWCNT         -> 9109             
  ORIO_WTIME       -> 00:52.232986        ORIOWCNT         -> 6247             
  OWIO_WTIME       -> 00:00.001697        OWIOWCNT         -> 1                 
  SERV_WTIME       -> 00:00.000000        SERVWCNT         -> 0                 
  ARCH_WTIME       -> 00:00.000000        ARCHWCNT         -> 0                 
  PLATCH_WTIME     -> 00:00.000000        PLATCHWCNT       -> 0                 
  DRAIN_WTIME      -> 00:00.000000        DRAINWCNT        -> 0                 
  CLAIM_WTIME      -> 00:00.000000        CLAIMWCNT        -> 0                 
  ARCRD_WTIME      -> 00:00.000000        ARCRDWCNT        -> 0                 
  SMSG_WTIME       -> 00:00.000000        SMSGWCNT         -> 0                 
 .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .
GLOCK_WTIME      -> 00:00.000000        GLOCKWCNT        -> 0                 
LOG_WTIME        -> 00:00.000000        LOGWCNT          -> 0                 
LOB_WTIME        -> 00:00.000000        LOBWCNT          -> 0                 
OCS_WTIME        -> 00:20.748950        OCSWCNT          -> 260               
SLS_WTIME        -> 00:00.000000        SLSWCNT          -> 0                 
DSS_WTIME        -> 00:00.000000        DSSWCNT          -> 0                 
OTS_WTIME        -> 00:00.000000        OTSWCNT          -> 0                 
PLOCK_WTIME      -> 00:00.000000        PLOCKWCNT        -> 0                 
LLOCK_WTIME      -> 00:00.000000        LLOCKWCNT        -> 0                 
                                                                             
dditional Time Values                                                         
ZIIP_CPU         -> 00:00.000000        ZONCP_CPU        -> 00:00.000000     
 PROC_CPU         -> 00:00.000000        UDF_CPU          -> 00:00.000000     
 UNACC_TIME       -> 00:36.289838                                             
******************************* BOTTOM OF DATA ********************************
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Thu Aug 11, 2011 12:43 am
Reply with quote

I am not really sure wher it is consuming such long time..
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Thu Aug 11, 2011 11:55 am
Reply with quote

Your get pages seems quite high , I really doubt if there is some tablespace scan happening in any of the tables inside the query. Is the stats uptodate for 3 tables refered in the query ?

in the wait times there seems some lock wait ,i/o Wait time...whats the level of locking,locksize?Why don't you have WITH UR specified in your query ?


Quote:

Index for table C is only 1 clolumn C.T063_LCT_NBR


Table C Index should be on DC_LCT_NBR ,T063_LCT_NBR this would give you much better access.

Quote:

Table A,B they have 4 columns defined for index out of which we have the 2 columns A.T024_ITM_NBR and A.T063_LCT_NBRwhich are used in this query


Make sure these columns are leading ones in the index defined for table A and B.Unless they are the leading ones optimizer won't select this index if you don't supply the leading columns .Is this the clustered index ?

For Table LOWES.E264G_WK_LCT_ITM an index on columns

T024_ITM_NBR
T063_LCT_NBR
ITM_LCT_STK_IDC
E264A_WK_END_DT

For table LOWES.E264G_WK_LCT_ITM an index on

T024_ITM_NBR
T063_LCT_NBR
ITM_LCT_STK_IDC
E264A_WK_END_DT

For table LOWES.T063A_LCT an index on

T063_LCT_NBR
DC_LCT_NBR

For table LOWES.E131E_DAY_LCT an index on

T024_ITM_NBR
T063_LCT_NBR
TOT_SAL_QTY
E131D_WK_END_DT

Would have better access paths. All depends on the number of rows,uniqueness,clustering etc..Try to produce a report of explain
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Aug 12, 2011 3:41 pm
Reply with quote

Code:
2. Index for table C is only 1 clolumn C.T063_LCT_NBR and for Table A,B they have 4 columns defined for index out of which we have the 2 columns A.T024_ITM_NBR and A.T063_LCT_NBRwhich are used in this query.


Are these the 1st two columns of the index?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Sat Aug 13, 2011 2:15 pm
Reply with quote

Rijit,

You need to talk with your DBA regarding IO_WTIME, that seems to be high
Code:
IO_WTIME         -> 48:01.574908
There seems to be a lots synchronous reads happening, so application activity will suspend until the physical I/O is complete.

Thanks,
Sushanth
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Fri Aug 19, 2011 12:04 pm
Reply with quote

ok thanks all for your inputs..I have passed this info to the DBA and wating for his inputs.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sun Aug 28, 2011 12:28 am
Reply with quote

Hello friends,

I did a mistake. I want to correct some information which I posted earlier in this thread. I checked E264G_WK_LCT_ITM is a view not a table which is being used in the query I posted in my first post.

For views we dont have index it? Is this the reason that the performance is getting degraded.

Thanks
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Sun Aug 28, 2011 9:29 am
Reply with quote

Hello,

If your dba has no suggestions so far, suggest you split up this query and run ithe "Pieces" in a coded program. Then, you should be able to identify which part(s) of the process is causing the excessive time.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Tue Aug 30, 2011 9:47 pm
Reply with quote

dick scherrer wrote:
Hello,

If your dba has no suggestions so far, suggest you split up this query and run ithe "Pieces" in a coded program. Then, you should be able to identify which part(s) of the process is causing the excessive time.


Yeah that looks like a good idea..I am trying on that but the logic i not simple to implement..Problem is the view E264G IS very huge the no of rows are in billions. Anyways will try to share whatever I do... icon_smile.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Aug 31, 2011 12:52 pm
Reply with quote

a view is nothing more than an implicit subselect.
Indexes don't exists on (db2-)views . but indexes should exists on the underlying tables.

dick scherrer wrote:
Hello,

If your dba has no suggestions so far, suggest you split up this query and run ithe "Pieces" in a coded program. Then, you should be able to identify which part(s) of the process is causing the excessive time.

There are several possible accesspaths for each SQL-statement. splitting one up and executing them seperately, is like choosing the accesspath yourself & executing it poorly:
-which table first ?
- looping to second or storing first in an array before scanning second table ?
- sorting RIDs ?
- sorting on columns of different tables?
- n calls to DB2

It might give you an idea of which table is large or which index is missing, but nothing that you can't know from explain and some catalog reports/queries.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Aug 31, 2011 7:49 pm
Reply with quote

Hello,

Quote:
splitting one up and executing them seperately, is like choosing the accesspath yourself & executing it poorly:
There is no reason for it to be poorly executed. . . At least not after the first few attempts.

It has been my experience that the db2 optimizer is rather week for this kind of compound query (though it may often be better than someone who has no understanding of how a query is processes).

Quote:
I am trying on that but the logic i not simple to implement..
It has also been my experience that when one does not understand the actual work going on within the query, code that looks logical will dim the lights in the data center. . .

Quote:
huge the no of rows are in billions.
How many of these rows must be looked at to determine if the row is to be used or bypassed? I know of no way for a query to actually use billions of rows quickly icon_smile.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Aug 31, 2011 8:30 pm
Reply with quote

Just going to give my 2c on it and then let it slide.

If the optimal accesspath is Nested loop join or merge scan join,
which are the simpliest accesspath to code yourself:

Even in this case coding it yourself involves more calls to DB2.
If the optimizers chooses the same path, DB2 will be faster.

If the optimizer chooses another sub-optimal accesspath, and you are not able to influence it (by e. changing the query) then you might consider of splitting it in your code.

If the optimal accesspath is something else or involves sorting columns from different tables, creating indexed in-lists,... There is no way you can come close to the performance DB2 gets.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Sep 16, 2011 8:21 pm
Reply with quote

someone smarter than me :
robertsdb2blog.blogspot.com/2011/06/application-programming-tip-let-db2-do.html
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Need help with ADABAS query (COBOL-AD... All Other Mainframe Topics 0
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top