|
View previous topic :: View next topic
|
| Author |
Message |
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
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 |
|
 |
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1050
Location: Richmond, Virginia
|
|
|
|
| Well, have you experimented at all? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| Or may be, send a request to your DBA to run an EXPLAIN on the query, to get you started. |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| I am not really sure wher it is consuming such long time.. |
|
| Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005
Posts: 306
|
|
|
|
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 |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| 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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| ok thanks all for your inputs..I have passed this info to the DBA and wating for his inputs. |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| 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...  |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
| Back to top |
|
|
|
|
