Skip to Main Content
  • Questions
  • Real Time SQL monitoring doesn't show my query

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Krystian.

Asked: December 19, 2018 - 4:12 pm UTC

Answered by: Connor McDonald - Last updated: October 15, 2020 - 4:33 am UTC

Category: Database Administration - Version: 12.1.0.2

Viewed 1000+ times

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 we said...

The most likely cause here is the size of the SQL, more accurately, the size of the plan.

Because we need to track the concurrent activity in the all of the plan lines, we won't monitor a statement that has a massive plan (by default).

Check this blog post for how to modify it

https://blogs.oracle.com/upgrade/sql-monitoring-limitation-at-300-lines-per-statement


and you rated our response

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

_sqlmon_max_planlines

January 11, 2019 - 9:20 am UTC

Reviewer: Rajeshwaran, Jeyabal

increasing this "_sqlmon_max_planlines" parameter could lead to increase in the size of shared pool.

Maria discuss them in detail @ https://sqlmaria.com/2017/08/01/getting-the-most-out-of-oracle-sql-monitor/

sqlmonitor showing logical IO's at line level.

October 13, 2020 - 1:56 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Team:

one question about sql monitor compared to execution plan (produced by gather_plan_statistics hint in place) - wont it be good, if sql monitor reports the logical io's (buffer gets) for each step in plan like how "gather_plan_statistics" hint reports rather than as "IO statistics" at the top right corner? that way we could easily focus on where we spend most of IO's per execution?
Connor McDonald

Followup  

October 14, 2020 - 4:03 am UTC

Sounds like a good enhancement request

https://community.oracle.com/tech/apps-infra/categories/database-ideas-ideas

sqlmonitor showing logical IO's at line level.

October 14, 2020 - 9:59 am UTC

Reviewer: Rajeshwaran, Jeyabal from India

Thanks - added to the databsae ideas, when you find some time, please add your vote to that.

https://community.oracle.com/tech/apps-infra/discussion/4476337/sqlmonitor-showing-logical-ios-at-line-level
Connor McDonald

Followup  

October 15, 2020 - 4:33 am UTC

Done

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.