You Asked
Hi TOM,
I've got a big query, which normally takes to complete around 90 seconds. I'd like to see the execution details using Real Tim SQL monitoring.
Unfortunately, it didn't appear there. Even when I specifically added /*+ MONITOR */ to the query, it didn't help either.
What's weird, the query consists of 4 other queries, and each of these sub-queries, when runs separately, can be monitored in the RT sql monitoring.
Any idea, what could be a cause, why the main query doesn't appear in the tool?
thanks,
Krystian
--
SELECT
(
SELECT
SUM(to_number(counter))
FROM
(
SELECT
COUNT(tableM.id) counter
FROM
tableM
INNER JOIN tableA wq ON wq.id = tableM.unique_id
LEFT OUTER JOIN tableB rt ON rt.id = tableM.tableB_id
LEFT OUTER JOIN tableC ow ON ow.id = tableM.owner_id
LEFT OUTER JOIN userA.viewD iuv ON iuv.email = ow.email
LEFT OUTER JOIN tableE dq ON dq.id = tableM.ref_id
INNER JOIN tableC eu ON eu.id = DECODE(tableM.tableB_id, 'xx', dq.tableC_id, tableM.tableMer_id
)
LEFT OUTER JOIN userA_rw.tableF wc ON wc.emailinternet = eu.email
LEFT OUTER JOIN userA_rw.tableG tr ON tr.customernumber = wc.targetnumbermagic
LEFT OUTER JOIN userA.tableH com ON com.magicnumber = tr.companymagicnumber
LEFT OUTER JOIN tableI sp ON sp.id = tableM.tableI_id
LEFT OUTER JOIN tableJ s ON s.id = sp.tableJ_id
LEFT OUTER JOIN userA.viewK av ON av.account_id = s.account_id
LEFT OUTER JOIN tableL sd ON ( ( sd.setting_group = 'yyyy'
AND ( sd.account_id = eu.account_id
OR ( sd.account_id IS NULL
AND eu.account_id IS NULL ) ) )
OR ( sd.setting_group = 'xycy'
AND sd.account_id IS NULL ) )
AND sd.setting_name = tableM.tableB_id
WHERE
tableM.tableB_id != 'yxxc'
AND ( tableM.unique_id = 'A'
OR tableM.last_unique_id = 'A' )
AND ( ( sd.setting_group = 'yyyy'
AND tableM.tableB_id NOT IN (
'bb',
'cc',
'yxxc'
) )
OR ( sd.setting_group = 'xycy'
AND tableM.tableB_id IN (
'bb',
'cc'
) ) )
AND ( ( tableM.tableB_id = 'cc'
AND s.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 45781
) )
OR ( tableM.tableB_id <> 'cc'
AND eu.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 7841
) ) )
AND tableM.status NOT IN (
'AAA',
'BBB',
'CCC',
'DDD'
)
AND ( ( ( ( ( trunc(SYSDATE - tableM.submitted_date) > sd.red_day )
OR ( ( trunc(SYSDATE - tableM.submitted_date) = sd.red_day )
AND ( trunc(mod((SYSDATE - tableM.submitted_date) * 24, 24), 1) >= sd.red_hours ) ) )
AND ( ( trunc(SYSDATE - tableM.submitted_date) > sd.amber_day )
OR ( ( trunc(SYSDATE - tableM.submitted_date) = sd.amber_day )
AND ( trunc(mod((SYSDATE - tableM.submitted_date) * 24, 24), 1) >= sd.amber_hours ) ) )
)
AND ( tableM.status != 'vvvvv'
OR tableM.status IS NULL ) )
OR ( tableM.last_rag = 'OOO'
AND tableM.status = 'vvvvv' ) )
UNION ALL
SELECT
COUNT(tableM.id) counter
FROM
tableM
INNER JOIN tableA wq ON wq.id = tableM.unique_id
LEFT OUTER JOIN tableB rt ON rt.id = tableM.tableB_id
LEFT OUTER JOIN tableC ow ON ow.id = tableM.owner_id
LEFT OUTER JOIN userA.viewD iuv ON iuv.email = ow.email
LEFT OUTER JOIN userA.tableH com ON com.magicnumber = tableM.number_no_decimal_1
LEFT OUTER JOIN tableI sp ON sp.id = tableM.tableI_id
LEFT OUTER JOIN tableJ s ON s.id = sp.tableJ_id
LEFT OUTER JOIN userA.viewK av ON av.account_id = s.account_id
LEFT OUTER JOIN tableL sd ON sd.setting_name = tableM.tableB_id
AND sd.setting_group = 'yyyy'
WHERE
tableM.tableB_id IN (
'yxxc'
)
AND ( tableM.unique_id = 'A'
OR tableM.last_unique_id = 'A' )
AND ( ( tableM.number_no_decimal_1 IS NULL
AND sd.account_id IS NULL )
OR ( tableM.number_no_decimal_1 IS NOT NULL
AND tableM.number_no_decimal_1 = sd.account_id ) )
AND ( ( tableM.tableB_id = 'cc'
AND s.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 34321
) )
OR ( tableM.tableB_id <> 'cc'
AND tableM.number_no_decimal_1 IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) ) )
)
) AS red,
(
SELECT
SUM(to_number(counter))
FROM
(
SELECT
COUNT(tableM.id) counter
FROM
tableM
INNER JOIN tableA wq ON wq.id = tableM.unique_id
LEFT OUTER JOIN tableB rt ON rt.id = tableM.tableB_id
LEFT OUTER JOIN tableC ow ON ow.id = tableM.owner_id
LEFT OUTER JOIN userA.viewD iuv ON iuv.email = ow.email
LEFT OUTER JOIN tableE dq ON dq.id = tableM.ref_id
INNER JOIN tableC eu ON eu.id = DECODE(tableM.tableB_id, 'xx', dq.tableC_id, tableM.tableMer_id
)
LEFT OUTER JOIN userA_rw.tableF wc ON wc.emailinternet = eu.email
LEFT OUTER JOIN userA_rw.tableG tr ON tr.customernumber = wc.targetnumbermagic
LEFT OUTER JOIN userA.tableH com ON com.magicnumber = tr.companymagicnumber
LEFT OUTER JOIN tableI sp ON sp.id = tableM.tableI_id
LEFT OUTER JOIN tableJ s ON s.id = sp.tableJ_id
LEFT OUTER JOIN userA.viewK av ON av.account_id = s.account_id
LEFT OUTER JOIN tableL sd ON ( ( sd.setting_group = 'yyyy'
AND ( sd.account_id = eu.account_id
OR ( sd.account_id IS NULL
AND eu.account_id IS NULL ) ) )
OR ( sd.setting_group = 'xycy'
AND sd.account_id IS NULL ) )
AND sd.setting_name = tableM.tableB_id
WHERE
tableM.tableB_id != 'yxxc'
AND ( tableM.unique_id = 'A'
OR tableM.last_unique_id = 'A' )
AND ( ( sd.setting_group = 'yyyy'
AND tableM.tableB_id NOT IN (
'bb',
'cc',
'yxxc'
) )
OR ( sd.setting_group = 'xycy'
AND tableM.tableB_id IN (
'bb',
'cc'
) ) )
AND ( ( tableM.tableB_id = 'cc'
AND s.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 23423
) )
OR ( tableM.tableB_id <> 'cc'
AND eu.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 23423
) ) )
AND tableM.status NOT IN (
'AAA',
'BBB',
'CCC',
'DDD'
)
AND ( ( ( ( ( trunc(SYSDATE - tableM.submitted_date) < sd.red_day )
OR ( ( trunc(SYSDATE - tableM.submitted_date) = sd.red_day )
AND ( trunc(mod((SYSDATE - tableM.submitted_date) * 24, 24), 1) < sd.red_hours ) ) )
AND ( ( trunc(SYSDATE - tableM.submitted_date) > sd.amber_day )
OR ( ( trunc(SYSDATE - tableM.submitted_date) = sd.amber_day )
AND ( trunc(mod((SYSDATE - tableM.submitted_date) * 24, 24), 1) >= sd.amber_hours ) ) )
)
AND ( tableM.status != 'vvvvv'
OR tableM.status IS NULL ) )
OR ( tableM.last_rag = 'AMBER'
AND tableM.status = 'vvvvv' ) )
UNION ALL
SELECT
COUNT(tableM.id) counter
FROM
tableM
INNER JOIN tableA wq ON wq.id = tableM.unique_id
LEFT OUTER JOIN tableB rt ON rt.id = tableM.tableB_id
LEFT OUTER JOIN tableC ow ON ow.id = tableM.owner_id
LEFT OUTER JOIN userA.viewD iuv ON iuv.email = ow.email
LEFT OUTER JOIN userA.tableH com ON com.magicnumber = tableM.number_no_decimal_1
LEFT OUTER JOIN tableI sp ON sp.id = tableM.tableI_id
LEFT OUTER JOIN tableJ s ON s.id = sp.tableJ_id
LEFT OUTER JOIN userA.viewK av ON av.account_id = s.account_id
LEFT OUTER JOIN tableL sd ON sd.setting_name = tableM.tableB_id
AND sd.setting_group = 'yyyy'
WHERE
tableM.tableB_id IN (
'yxxc'
)
AND ( tableM.unique_id = 'A'
OR tableM.last_unique_id = 'A' )
AND ( ( tableM.number_no_decimal_1 IS NULL
AND sd.account_id IS NULL )
OR ( tableM.number_no_decimal_1 IS NOT NULL
AND tableM.number_no_decimal_1 = sd.account_id ) )
AND ( ( tableM.tableB_id = 'cc'
AND s.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) )
OR ( tableM.tableB_id <> 'cc'
AND tableM.number_no_decimal_1 IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) ) )
)
) AS amber,
(
SELECT
SUM(to_number(counter))
FROM
(
SELECT
COUNT(tableM.id) counter
FROM
tableM
INNER JOIN tableA wq ON wq.id = tableM.unique_id
LEFT OUTER JOIN tableB rt ON rt.id = tableM.tableB_id
LEFT OUTER JOIN tableC ow ON ow.id = tableM.owner_id
LEFT OUTER JOIN userA.viewD iuv ON iuv.email = ow.email
LEFT OUTER JOIN tableE dq ON dq.id = tableM.ref_id
INNER JOIN tableC eu ON eu.id = DECODE(tableM.tableB_id, 'xx', dq.tableC_id, tableM.tableMer_id
)
LEFT OUTER JOIN userA_rw.tableF wc ON wc.emailinternet = eu.email
LEFT OUTER JOIN userA_rw.tableG tr ON tr.customernumber = wc.targetnumbermagic
LEFT OUTER JOIN userA.tableH com ON com.magicnumber = tr.companymagicnumber
LEFT OUTER JOIN tableI sp ON sp.id = tableM.tableI_id
LEFT OUTER JOIN tableJ s ON s.id = sp.tableJ_id
LEFT OUTER JOIN userA.viewK av ON av.account_id = s.account_id
LEFT OUTER JOIN tableL sd ON ( ( sd.setting_group = 'yyyy'
AND ( sd.account_id = eu.account_id
OR ( sd.account_id IS NULL
AND eu.account_id IS NULL ) ) )
OR ( sd.setting_group = 'xycy'
AND sd.account_id IS NULL ) )
AND sd.setting_name = tableM.tableB_id
WHERE
tableM.tableB_id != 'yxxc'
AND ( tableM.unique_id = 'A'
OR tableM.last_unique_id = 'A' )
AND ( ( sd.setting_group = 'yyyy'
AND tableM.tableB_id NOT IN (
'bb',
'cc',
'yxxc'
) )
OR ( sd.setting_group = 'xycy'
AND tableM.tableB_id IN (
'bb',
'cc'
) ) )
AND ( ( tableM.tableB_id = 'cc'
AND s.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) )
OR ( tableM.tableB_id <> 'cc'
AND eu.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) ) )
AND tableM.status NOT IN (
'AAA',
'BBB',
'CCC',
'DDD'
)
AND ( ( ( ( trunc(SYSDATE - tableM.submitted_date) < sd.amber_day )
OR ( ( trunc(SYSDATE - tableM.submitted_date) = sd.amber_day )
AND ( trunc(mod((SYSDATE - tableM.submitted_date) * 24, 24), 1) < sd.amber_hours ) ) )
AND ( tableM.status != 'vvvvv'
OR tableM.status IS NULL ) )
OR ( tableM.last_rag = 'GREEN'
AND tableM.status = 'vvvvv' ) )
UNION ALL
SELECT
COUNT(tableM.id) counter
FROM
tableM
INNER JOIN tableA wq ON wq.id = tableM.unique_id
LEFT OUTER JOIN tableB rt ON rt.id = tableM.tableB_id
LEFT OUTER JOIN tableC ow ON ow.id = tableM.owner_id
LEFT OUTER JOIN userA.viewD iuv ON iuv.email = ow.email
LEFT OUTER JOIN userA.tableH com ON com.magicnumber = tableM.number_no_decimal_1
LEFT OUTER JOIN tableI sp ON sp.id = tableM.tableI_id
LEFT OUTER JOIN tableJ s ON s.id = sp.tableJ_id
LEFT OUTER JOIN userA.viewK av ON av.account_id = s.account_id
LEFT OUTER JOIN tableL sd ON sd.setting_name = tableM.tableB_id
AND sd.setting_group = 'yyyy'
WHERE
tableM.tableB_id IN (
'yxxc'
)
AND ( tableM.unique_id = 'A'
OR tableM.last_unique_id = 'A' )
AND ( ( tableM.number_no_decimal_1 IS NULL
AND sd.account_id IS NULL )
OR ( tableM.number_no_decimal_1 IS NOT NULL
AND tableM.number_no_decimal_1 = sd.account_id ) )
AND ( ( tableM.tableB_id = 'cc'
AND s.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) )
OR ( tableM.tableB_id <> 'cc'
AND tableM.number_no_decimal_1 IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) ) )
)
) AS green,
(
SELECT
SUM(to_number(counter))
FROM
(
SELECT
COUNT(tableM.id) counter
FROM
tableM
INNER JOIN tableA wq ON wq.id = tableM.unique_id
LEFT OUTER JOIN tableB rt ON rt.id = tableM.tableB_id
LEFT OUTER JOIN tableC ow ON ow.id = tableM.owner_id
LEFT OUTER JOIN userA.viewD iuv ON iuv.email = ow.email
LEFT OUTER JOIN tableE dq ON dq.id = tableM.ref_id
INNER JOIN tableC eu ON eu.id = DECODE(tableM.tableB_id, 'xx', dq.tableC_id, tableM.tableMer_id
)
LEFT OUTER JOIN userA_rw.tableF wc ON wc.emailinternet = eu.email
LEFT OUTER JOIN userA_rw.tableG tr ON tr.customernumber = wc.targetnumbermagic
LEFT OUTER JOIN userA.tableH com ON com.magicnumber = tr.companymagicnumber
LEFT OUTER JOIN tableI sp ON sp.id = tableM.tableI_id
LEFT OUTER JOIN tableJ s ON s.id = sp.tableJ_id
LEFT OUTER JOIN userA.viewK av ON av.account_id = s.account_id
LEFT OUTER JOIN tableL sd ON ( ( sd.setting_group = 'yyyy'
AND ( sd.account_id = eu.account_id
OR ( sd.account_id IS NULL
AND eu.account_id IS NULL ) ) )
OR ( sd.setting_group = 'xycy'
AND sd.account_id IS NULL ) )
AND sd.setting_name = tableM.tableB_id
WHERE
tableM.tableB_id != 'yxxc'
AND ( tableM.unique_id = 'A'
OR tableM.last_unique_id = 'A' )
AND ( ( sd.setting_group = 'yyyy'
AND tableM.tableB_id NOT IN (
'bb',
'cc',
'yxxc'
) )
OR ( sd.setting_group = 'xycy'
AND tableM.tableB_id IN (
'bb',
'cc'
) ) )
AND ( ( tableM.tableB_id = 'cc'
AND s.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) )
OR ( tableM.tableB_id <> 'cc'
AND eu.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) ) )
AND ( tableM.status IN (
'AAA',
'BBB',
'CCC',
'DDD'
)
OR ( tableM.status = 'vvvvv'
AND tableM.last_rag = 'RED' ) )
UNION ALL
SELECT
COUNT(tableM.id) counter
FROM
tableM
INNER JOIN tableA wq ON wq.id = tableM.unique_id
LEFT OUTER JOIN tableB rt ON rt.id = tableM.tableB_id
LEFT OUTER JOIN tableC ow ON ow.id = tableM.owner_id
LEFT OUTER JOIN userA.viewD iuv ON iuv.email = ow.email
LEFT OUTER JOIN userA.tableH com ON com.magicnumber = tableM.number_no_decimal_1
LEFT OUTER JOIN tableI sp ON sp.id = tableM.tableI_id
LEFT OUTER JOIN tableJ s ON s.id = sp.tableJ_id
LEFT OUTER JOIN userA.viewK av ON av.account_id = s.account_id
LEFT OUTER JOIN tableL sd ON sd.setting_name = tableM.tableB_id
AND sd.setting_group = 'yyyy'
WHERE
tableM.tableB_id IN (
'yxxc'
)
AND ( tableM.unique_id = 'A'
OR tableM.last_unique_id = 'A' )
AND ( ( tableM.number_no_decimal_1 IS NULL
AND sd.account_id IS NULL )
OR ( tableM.number_no_decimal_1 IS NOT NULL
AND tableM.number_no_decimal_1 = sd.account_id ) )
AND ( ( tableM.tableB_id = 'cc'
AND s.account_id IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) )
OR ( tableM.tableB_id <> 'cc'
AND tableM.number_no_decimal_1 IN (
SELECT
account_id
FROM
tableN
WHERE
tableN.tableC_id = 57151
) ) )
)
) AS grey
FROM
dual;
and Connor said...
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment