3

We are trying to find out why a (pretty complicated) statement has an extremely high variance in runtime. Sometimes it just takes "minutes" (around 40-50) and sometimes it does not finish even after 12 hours.

My usual approach to problems like that is to check if the statement is monitored and if it is, get the "real-time" execution of it using dbms_sqltune.report_sql_monitor() so I can pin-point the bottleneck in the execution.

However that specific statement is never monitored by Oracle. According to the Oracle manual a statement is monitored, if:

By default, SQL monitoring is automatically started when a SQL statement either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution

As this statement runs for substantially longer then 5 seconds and uses parallel execution it should be monitored.

We even added the hints gather_plan_statistics and monitor to the SQL to force monitoring of the statement, but still it's not monitored.

According to "My Oracle Support" (DocID: 1613163.1) monitoring is limited to statements with a plan with less then 300 lines. When I generate the plan, it has 282 lines (=highest line ID in the plan), so that should not prevent monitoring.

There is a hidden parameter ("_sqlmon_max_planlines") that controls this, but this is a hosted database server and changing something like that is quite complicated.

So my question is:

Are there any other (maybe undocumented) restrictions on why Oracle would not monitor a statement?


This is an Oracle 11.2.0.3 Enterprise Edition (upgrade to 11.2.0.4 is scheduled, but that might take some time).

3
  • "Running" for > 5 seconds is not the same as consuming more than 5 seconds of CPU time. Check for blocking locks?
    – Philᵀᴹ
    Commented Nov 29, 2017 at 10:16
  • @Philᵀᴹ: good point, but no lock waits are visible for that statement in v$active_session_history (usually only db file sequential read waits - which is to be expected)
    – user1822
    Commented Nov 29, 2017 at 10:21
  • @Philᵀᴹ: plus: parallel queries should always be monitored according to the manual regardless of the CPU they use.
    – user1822
    Commented Nov 29, 2017 at 11:55

2 Answers 2

1

The statement in question had multiple plans (13 to be precise) which for some reason were not returned by dbms_xplan.display_cursor(). The plan returned by that function was one with less then 300 lines, but other plans had more then 300 lines.

After the hosting partner increased the "_sqlmon_max_planlines" parameter to 500, the statement is now monitored.

1

If you know the SQL_ID, you can also issue:

ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737] force=true';

to ensure monitoring takes place (for example, even for short running SQL statements)

4
  • and how do you then turn it off?
    – q3kep
    Commented Jan 20, 2022 at 12:37
  • ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737] off' or just 'ALTER SYSTEM SET EVENTS 'sql_monitor off' Commented Jan 21, 2022 at 13:16
  • SQL doesn't apper in Oracle monitor using this. What's wrong? What Oracle version does support this?
    – Okloks
    Commented May 19, 2023 at 7:35
  • see here for potential causes tanelpoder.com/2013/10/07/… Commented May 22, 2023 at 3:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.