![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Sun, 07 Apr 2013 @ 20:25:19 GMT
Subj: | | EXPLAIN running for 23 seconds |
|
From: | | Bakthavachalam, Roopalini |
Hi Forum,
One of the users on the box is trying to optimize his query and when he runs a explain on the query, it takes 23 seconds to just run the
explain. I checked the DBQLOGTBL and found that the parseCPUTime is 23 seconds. I am unable to understand why running an EXPLAIN should take 23
seconds to give the output? Since we have TASM enabled, I was thinking if the workload priority is causing it, however I ran the query with system
ID and it still took the same 23 seconds and so I ruled out TASM.
Could any of you help me understand , the reason for this?
EXPLAIN SELECT A11.R_T_ ID,
A13.R_C_G_ID,
A11.S_M_ID,
A12.M_S_CD,
A15.DAY_OF_WEEK_NR ,
A11.C_ID,
SUM((CASE WHEN (A11.SC_ID >= 20121205) AND (A11.SC_ID <= 20130404)
THEN A11.R_O_NR ELSE NULL END)) WJXBFS1,
SUM((CASE WHEN (A11.SC_ID >= 20121205) AND (A11.SC_ID <= 20130404)
THEN A11.R_R_AMT ELSE NULL END)) WJXBFS2,
SUM((CASE WHEN (A11.SC_ID >= 20111205) AND (A11.SC_ID <= 20120404)
THEN A11.R_O_NR ELSE NULL END)) WJXBFS3,
SUM((CASE WHEN (A11.SC_ID >= 20111205) AND (A11.SC_ID <= 20120404)
THEN A11.R_R_AMT ELSE NULL END)) WJXBFS4,
SUM((CASE WHEN (A14.M_M_CD NOT IN ('OTZ') ) AND ( A11.SC_ID >= 20111205 AND A11.SC_ID <= 20120404)
THEN A11.R_O_NR ELSE NULL END)) WJXBFS5,
SUM((CASE WHEN (A11.SC_ID >= 20121205) AND (A11.SC_ID <= 20130404) AND (A14.M_M_CD NOT IN ('OTZ'))
THEN A11.R_O_NR ELSE NULL END)) WJXBFS6
FROM STAY A11
JOIN CHANNEL A12
ON (A11.C_ID = A12.C_ID)
JOIN R_CODE A13
ON (A11.R_C_ID = A13.R_C_ID)
JOIN MARKET A14
ON (A11.S_M_ID = A14.M_ID)
JOIN CALENDAR A15
ON (A11.SC_ID = A15._ID)
WHERE (A11.H_ID IN (13)
AND A11.C_TYPE_CD IN ('RP')
AND A14.B_UNIT_NM IN ('H')
AND (A11.SC_ID >= 20121205
AND A11.SC_ID <= 20130404)
OR (A11.SC_ID >= 20111205 AND SC_ID <= 20120404))
GROUP BY A11.ROOM_TYPE_ID,
A13.RATE_CODE_GROUP_ID,
A11.STAY_MARKET_ID,
A12.MAJOR_SOURCE_CD,
A15.DAY_OF_WEEK_NR,
A11.COUNTRY_ID
Thanks
Roopalini
| |