| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
05-01-09, 13:10
|
|
Registered User
|
|
Join Date: May 2008
Posts: 15
|
|
Query Performance Issue
|
|
Our one online transaction has started taking so much time for last couple of days. What is interesting is this started tanking on 4/29, but nothing was changed since 4/17. As there is just 1 SQL statement in the package, the only other thing I can think of is the :HV being passed in may cause a long scan.
Something has gone awfully wrong in the past 2 days. The CPU and Elapsed time have drastically increased. DBA attempted a rebind to go to a new access path, but it didn’t change the outcome. This one query took close to 90 minutes of cpu time.
Could anyone please suggesst any changes in query to make it efficient?
Appreciate your help.
|
|
05-01-09, 13:14
|
|
Registered User
|
|
Join Date: May 2008
Posts: 15
|
|
query..
Code:
SELECT TABA.SRVDLS_IDN
,TABA.FLAG_CODE
,INDTYP.IND_TYPE_DESC
FROM (SELECT SRVDLS.IDN SRVDLS_IDN
, 'SLR' FLAG_CODE
FROM SERV_DLVRY_SITE SRVDLS
WHERE SRVDLS.IDN = :KEYSET-SRVDLS-IDN
AND SRVDLS.LOC_EXPLAN_TEXT IS NOT NULL
UNION
SELECT SRVDLS2.IDN SRVDLS_IDN
, 'LSP' FLAG_CODE
FROM SERV_DLVRY_SITE SRVDLS1
,SERV_DLVRY_SITE SRVDLS2
WHERE SRVDLS2.IDN = :KEYSET-SRVDLS-IDN
AND SRVDLS2.SRVDLS_IDN = SRVDLS1.IDN
AND SRVDLS1.CNCR_CUSTMR_IDN IS NOT NULL
UNION
SELECT SERV.SRVDLS_IDN
, 'BRS' FLAG_CODE
FROM SERVICE SERV
,READING_COND RDGCND
,INSTALLED_REGISTER INSREG
,METER_INSTALLATION MTRINL
WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND SERV.IDN = RDGCND.SERV_IDN
AND SERV.IDN = INSREG.SERV_IDN
AND INSREG.MTRINL_IDN = MTRINL.IDN
AND INSREG.LAST_EFF_DATE IS NULL
AND MTRINL.LAST_EFF_DATE IS NULL
AND RDGCND.READING_COND_DATE >=
CURRENT DATE - 45 DAYS
AND RDGCND.READING_COND_CODE
IN ('004', '005', '010')
UNION
SELECT FWREQ.SRVDLS_IDN
, 'PWR' FLAG_CODE
FROM FIELD_WORK_REQUEST FWREQ
WHERE FWREQ.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND REQ_STATUS_CODE IN ('OPN', 'REQ')
UNION
SELECT FWREQ.SRVDLS_IDN
,'REC' AS FLAG_CODE
FROM FIELD_WORK_REQUEST FWREQ
WHERE FWREQ.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND FWREQ.COMPL_DATE IS NULL
AND REQ_STATUS_CODE = 'OPN'
AND WORK_REQ_TYPE_CODE = '094'
UNION
SELECT FWREQ.SRVDLS_IDN
,'RCO' AS FLAG_CODE
FROM FIELD_WORK_REQUEST FWREQ
WHERE FWREQ.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND FWREQ.COMPL_DATE >=
CURRENT DATE - 1 DAY
AND REQ_STATUS_CODE = 'CLO'
AND WORK_REQ_TYPE_CODE = '094'
AND WORK_STATUS_CODE = 'CMPL'
UNION
SELECT FWREQ.SRVDLS_IDN
,'SDI' AS FLAG_CODE
FROM FIELD_WORK_REQUEST FWREQ
WHERE FWREQ.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND FWREQ.COMPL_DATE >=
CURRENT DATE - 1 DAY
AND REQ_STATUS_CODE = 'CLO'
AND WORK_REQ_TYPE_CODE = '094'
AND (WORK_STATUS_CODE IS NULL
OR WORK_STATUS_CODE = ' ')
UNION
SELECT FWREQ.SRVDLS_IDN
,'RER' AS FLAG_CODE
FROM FIELD_WORK_REQUEST FWREQ
WHERE FWREQ.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND REQ_STATUS_CODE NOT IN ('CAN', 'CLO')
AND WORK_REQ_TYPE_CODE IN
('096', '101', '102')
UNION
SELECT SERV.SRVDLS_IDN
, 'UIM' FLAG_CODE
FROM SERVICE SERV
WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND SERV.INACT_MTR_USG_IND = 'Y'
AND SERV.STAT_CODE = 'INA'
UNION
SELECT SITIND.SRVDLS_IDN
, 'ENP' FLAG_CODE
FROM SITE_INDICATOR SITIND
WHERE SITIND.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND SITIND.IND_TYPE_CODE = 'EP'
UNION
...continue
|
|
05-01-09, 13:15
|
|
Registered User
|
|
Join Date: May 2008
Posts: 15
|
|
|
query continue...
|
|
Code:
query continue...
SELECT SERV.SRVDLS_IDN
,CASE WHEN MTRINL.EMR_RDG_MTHD_CODE = 'A'
THEN 'AMR'
WHEN MTRINL.EMR_RDG_MTHD_CODE = 'S'
THEN 'AMS'
WHEN MTRINL.EMR_RDG_MTHD_CODE = 'E'
THEN 'ADP'
WHEN MTRINL.EMR_RDG_MTHD_CODE = 'M'
THEN 'MAN'
WHEN MTRINL.EMR_RDG_MTHD_CODE = 'R'
THEN 'AMX'
END AS FLAG_CODE
FROM SERVICE SERV
,INSTALLED_REGISTER INSREG
,METER_INSTALLATION MTRINL
WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND INSREG.MTRINL_IDN = MTRINL.IDN
AND SERV.IDN = INSREG.SERV_IDN
AND
VALUE(MTRINL.LAST_EFF_DATE,DATE('9999-12-31'))
>= CURRENT DATE
AND
VALUE(INSREG.LAST_EFF_DATE,DATE('9999-12-31'))
>= CURRENT DATE
AND MTRINL.EMR_RDG_MTHD_CODE IN
('A', 'S', 'E', 'M','R')
UNION
SELECT SERV.SRVDLS_IDN
,'ITR' FLAG_CODE
FROM SERVICE SERV
,INSTALLED_REGISTER INSREG
,METER_INSTALLATION MTRINL
WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND INSREG.MTRINL_IDN = MTRINL.IDN
AND SERV.IDN = INSREG.SERV_IDN
AND
VALUE(MTRINL.LAST_EFF_DATE,DATE('9999-12-31'))
>= CURRENT DATE
AND
VALUE(INSREG.LAST_EFF_DATE,DATE('9999-12-31'))
>= CURRENT DATE
AND MTRINL.EMR_RDG_MTHD_CODE = 'I'
AND MTRINL.COMMUN_TYPE_CODE = ' '
UNION
SELECT SERV.SRVDLS_IDN
, 'TOU' FLAG_CODE
FROM SERVICE SERV
,INSTALLED_REGISTER INSREG
,METER_INSTALLATION MTRINL
WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND INSREG.MTRINL_IDN = MTRINL.IDN
AND SERV.IDN = INSREG.SERV_IDN
AND
VALUE(MTRINL.LAST_EFF_DATE,DATE('9999-12-31'))
>= CURRENT DATE
AND
VALUE(INSREG.LAST_EFF_DATE,DATE('9999-12-31'))
>= CURRENT DATE
AND MTRINL.METER_CONFIG_CODE IN ('TOU', 'DTU')
UNION
SELECT SERV.SRVDLS_IDN
, 'OMR' FLAG_CODE
FROM SERVICE SERV
,INSTALLED_REGISTER INSREG
,METER_INSTALLATION MTRINL
,METER_ROUTE MTRRTE
WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND INSREG.MTRINL_IDN = MTRINL.IDN
AND SERV.IDN = INSREG.SERV_IDN
AND
VALUE(MTRINL.LAST_EFF_DATE,DATE('9999-12-31'))
>= CURRENT DATE
AND
VALUE(INSREG.LAST_EFF_DATE,DATE('9999-12-31'))
>= CURRENT DATE
AND MTRINL.EMR_RDG_MTHD_CODE = 'I'
AND MTRINL.COMMUN_TYPE_CODE = 'O'
AND SERV.MTRRTE_IDN = MTRRTE.IDN
AND MTRRTE.MOBILE_READ_IND ^= 'Y'
UNION
SELECT SERV.SRVDLS_IDN
, 'MMR' FLAG_CODE
FROM SERVICE SERV
,INSTALLED_REGISTER INSREG
,METER_INSTALLATION MTRIN
,METER_ROUTE MTRRTE
WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND INSREG.MTRINL_IDN = MTRINL.IDN
AND SERV.IDN = INSREG.SERV_IDN
AND
VALUE(MTRINL.LAST_EFF_DATE,DATE('9999-12-31'))
>= CURRENT DATE
AND
VALUE(INSREG.LAST_EFF_DATE,DATE('9999-12-31'))
>= CURRENT DATE
AND SERV.MTRRTE_IDN = MTRRTE.IDN
AND MTRRTE.MOBILE_READ_IND = 'Y'
UNION
SELECT DISTINCT INSREG.SRVDLS_IDN
, CASE
WHEN MTRINL.RMT_CAPABLE_NM = 'CONNECT'
THEN 'RCC'
WHEN MTRINL.RMT_CAPABLE_NM = 'READ'
THEN 'RRC'
END AS FLAG_CODE
FROM INSTALLED_REGISTER INSREG
,METER_INSTALLATION MTRINL
WHERE INSREG.SRVDLS_IDN =
:KEYSET-SRVDLS-IDN
AND INSREG.MTRINL_IDN =
MTRINL.IDN
AND VALUE(MTRINL.LAST_EFF_DATE,
DATE('9999-12-31')) >= CURRENT DATE
AND VALUE(INSREG.LAST_EFF_DATE,
DATE('9999-12-31')) >= CURRENT DATE
AND MTRINL.RMT_CAPABLE_NM IN
('READ','CONNECT')
UNION
SELECT SITIND.SRVDLS_IDN
, SITIND.IND_TYPE_CODE AS FLAG_CODE
FROM SITE_INDICATOR SITIND
WHERE SITIND.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND SITIND.IND_TYPE_CODE NOT IN ('EP', 'HPP')
UNION
SELECT Q1.SRVDLS_IDN, Q1.FLAG_CODE FROM (
SELECT VALUE(Q2.SRVDLS_IDN, Q3.SRVDLS_IDN)
AS SRVDLS_IDN, VALUE(Q2.FLAG, Q3.FLAG)
AS FLAG_CODE FROM (
(SELECT 'AMI' AS FLAG, SRVDLS.IDN AS SRVDLS_IDN
FROM SERV_DLVRY_SITE SRVDLS
WHERE SRVDLS.IDN = :KEYSET-SRVDLS-IDN
AND EXISTS (
SELECT 1 FROM TCBIS_TRF_XREF T
, SERVICE S, METER M
WHERE T.SRVDLS_IDN = S.SRVDLS_IDN
AND S.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND S.STAT_CODE NOT IN
('CAN','REQ','RMV','RMW')
AND T.SERV_IDN = S.IDN
AND T.CIRCUIT_NUM IN
(SELECT CIRCUIT_NUM FROM
UOF_CIRCUIT_PILOT)
AND S.METER_IDN = M.IDN
AND M.FEATURE_CODE IN
('L','X','Y') ) ) AS Q2
FULL OUTER JOIN
(SELECT 'AMA' AS FLAG, SRVDLS.IDN AS SRVDLS_IDN
FROM SERV_DLVRY_SITE SRVDLS
WHERE SRVDLS.IDN = :KEYSET-SRVDLS-IDN
AND EXISTS (
SELECT 1 FROM TCBIS_TRF_XREF T
, SERVICE S, METER M
WHERE T.SRVDLS_IDN = S.SRVDLS_IDN
AND S.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
AND S.STAT_CODE NOT IN
('CAN','REQ','RMV','RMW')
AND T.SERV_IDN = S.IDN
AND T.CIRCUIT_NUM IN
(SELECT CIRCUIT_NUM FROM
UOF_CIRCUIT_PILOT)
AND S.METER_IDN = M.IDN
AND M.FEATURE_CODE NOT IN
('L','X','Y') ) ) AS Q3
ON Q2.SRVDLS_IDN = Q3.SRVDLS_IDN ) ) AS Q1
) AS TABA
LEFT OUTER JOIN INDICATOR_TYPE INDTYP
ON TABA.FLAG_CODE = INDTYP.IND_TYPE_CODE
QUERYNO 1
END-EXEC.
|
|
05-01-09, 14:02
|
|
Registered User
|
|
Join Date: Dec 2008
Posts: 76
|
|
You give no version or platform, but even if we had these we could not evaluate such a long complex statement properly. It will be dependant on the structure of the tables, the data, and a dozen other factors.
Get an explain of the statement to pinpoint problem areas. Perhaps it needs indexes, stats, a reorg, etc.. Maybe some parts can be rewritten more efficiently.
|
|
05-01-09, 16:07
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
Just two suggestions, from a quick first investigation of the query:
- try to replace all "UNION"s by "UNION ALL"s
- the FULL OUTER JOIN: could it be reformulated into an INNER JOIN in this case?
If you post your PLAN_TABLE after running Explain, maybe some more useful suggestions could be given.
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Query Performance Issue
