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).
Best Answer
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.