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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Krystian.

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

Last updated: October 15, 2020 - 4:33 am UTC

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 Connor 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


Rating

  (4 ratings)

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

Comments

_sqlmon_max_planlines

Rajeshwaran, Jeyabal, January 11, 2019 - 9:20 am UTC

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.

Rajeshwaran, Jeyabal, October 13, 2020 - 1:56 pm UTC

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
October 14, 2020 - 4:03 am UTC

sqlmonitor showing logical IO's at line level.

Rajeshwaran, Jeyabal, October 14, 2020 - 9:59 am UTC

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
October 15, 2020 - 4:33 am UTC

Done

DOP got downgraded during execution - need inputs

Rajeshwaran, Jeyabal, November 23, 2020 - 4:57 am UTC

We got an application sql – that got downgraded degree of parallelism during the execution ( pretty big sql sitting inside a view, number of line in the plan was close to 500+), got the SQL Monitor report for the same.

Unlike mentioned in the link ( https://blogs.oracle.com/datawarehousing/finding-the-reason-for-dop-downgrades ) don’t see any reason code for downgrades

kindly let me know how can I share this sqlmonitor report ( in Active format ) to you - so that you can help us on this to identity the reason for downgraded degree of parallelism

or please let me know if any directions available to identity the reason for downgraded degree of parallelism- apart from this RT Sql monitoring

More to Explore

Performance

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