Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Real-Time SQL Monitoring and its limitation on more than 300 plan lines, and a fix …

Posted by FatDBA on December 16, 2021

Hi Guys,

Today while doing an analysis for a very complex & a big SQL query (402 lines), I tried to use the Real Time SQL Monitoring Report to get some quick stats about the execution, but it failed and it didn’t showed me any output.

Luckily I found a great article written by my friend Mohamed Houri and a metalink note for the problem (Doc ID 1613163.1), which says that there is a default limitation (300 lines) on the plan lines, and the threshold is in place to avoid spending too much time processing these large statements at the expense of other activities. So anything that has more than 300 plan lines won’t be monitored!

The fix to the problem is to set a hidden parameter which can be used to set the limit to a higher value, or you can set it on your SPFILE.

-- To change the default limit to any user defined value.
alter system set "_sqlmon_max_planlines"=450 scope=both;

-- or set it in your SPFILE
_sqlmon_max_planlines=450

Hope It Helped
Prashant Dixit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: