I've queried v$sql for a particular poorly performing sql_id and I get the
first and last part but it is missing maybe 10-20 lines from just after the
first create table statement. Sql_text has just "CREATE TABLE CWC_148D1". DB
version is 11.2.0.4.
CREATE TABLE CWC_148D1 AS IS_MODIFIED1
FROM (SELECT LEVEL_ID1,LEVEL_ID2,LEVEL_ID3
,MAX(IS_BASE0) AS IS_BASE0
,MAX(IS_BASE1) AS IS_BASE1
,MIN(SEC_BASE0) AS SEC_BASE0
,MIN(SEC_BASE1) AS SEC_BASE1
,MAX(IS_MODIFIED0) AS IS_MODIFIED0
,MAX(IS_MODIFIED1) AS IS_MODIFIED1
FROM CWP_14865_1045_1421068032
GROUP BY LEVEL_ID1,LEVEL_ID2,LEVEL_ID3)ALL_COMBS
,(SELECT LEVEL_ID1,LEVEL_ID3
,(CASE WHEN ((MIN(NVL(IS_BASE1,-1))*MAX(NVL(IS_BASE1,-1))) = -1) THEN 1 END)
AS NEED_MERGE
FROM CWP_14865_1045_1421068032
GROUP BY LEVEL_ID1,LEVEL_ID3)BASE0_COMBS
,(SELECT LEVEL_ID1,LEVEL_ID3,COUNT(*) AS WEIGHT_BASE0
FROM (SELECT DISTINCT ITEM_ID,LOCATION_ID,LEVEL_ID1,LEVEL_ID3
FROM CWP_14865_1045_1421068032
WHERE IS_BASE0 = 1)WEIGHT_BASE0
GROUP BY LEVEL_ID1,LEVEL_ID3)WEIGHT_BASE0
,(SELECT LEVEL_ID1,LEVEL_ID3,LEVEL_ID2,COUNT(*) AS WEIGHT_BASE1
FROM (SELECT DISTINCT
ITEM_ID,LOCATION_ID,PROMOTION_ID,LEVEL_ID1,LEVEL_ID3,LEVEL_ID2
FROM CWP_14865_1045_1421068032
WHERE IS_BASE1 = 1)WEIGHT_BASE1
GROUP BY LEVEL_ID1,LEVEL_ID3,LEVEL_ID2)WEIGHT_BASE1
WHERE BASE0_COMBS.LEVEL_ID1 = ALL_COMBS.LEVEL_ID1
AND BASE0_COMBS.LEVEL_ID3 = ALL_COMBS.LEVEL_ID3
AND ALL_COMBS.LEVEL_ID1 = WEIGHT_BASE0.LEVEL_ID1(+)
AND ALL_COMBS.LEVEL_ID3 = WEIGHT_BASE0.LEVEL_ID3(+)
AND ALL_COMBS.LEVEL_ID1 = WEIGHT_BASE1.LEVEL_ID1(+)
AND ALL_COMBS.LEVEL_ID3 = WEIGHT_BASE1.LEVEL_ID3(+)
AND ALL_COMBS.LEVEL_ID2 = WEIGHT_BASE1.LEVEL_ID2(+)
AND (BASE0_COMBS.NEED_MERGE IS NULL
OR (BASE0_COMBS.NEED_MERGE = ALL_COMBS.IS_BASE1)))T_POPU_LIST
,T_EP_EBS_ACCOUNT
,PROMOTION
,T_EP_FISCAL_MONTH
WHERE T_POPU_LIST.LEVEL_ID1 = T_EP_EBS_ACCOUNT.T_EP_EBS_ACCOUNT_EP_ID
AND T_POPU_LIST.LEVEL_ID2 = PROMOTION.PROMOTION_ID
AND T_POPU_LIST.LEVEL_ID3 = T_EP_FISCAL_MONTH.T_EP_FISCAL_MONTH_ID
AND T_POPU_LIST.IS_BASE1 = 1
