SQL Monitoring – Limitation at 300 lines per statement

One of the best parts of my job at Oracle:
I still learn something new every day.

Yesterday I’ve learned from my colleague from Oracle Switzerland, Thomas Teske, that SQL Real Time Monitoring has an embedded default limitation on the number of lines in the statement. If the limit (default: 300 lines) is exceeded the statement won’t be monitored. We both work with a leading Swiss company and we wanted to monitor a complex plan.

Now you may think: Who the heck has statements longer than 300 lines?
Well … sometimes that is beyond your influence as in this particular case this is of course done by the application.

Solution:

SQL> alter system set “_sqlmon_max_planlines”=800 scope=both;

or set in your spfile:

_sqlmon_max_planlines=800

This limitation is described in:

MOS Note:1613163.1
How to Monitor SQL Statements with Large Plans Using Real-Time SQL Monitoring?

If you’d like to read a bit more about SQL Real Time Monitoring please follow one of these links – and be aware that it’s part of the Tuning Pack license and VERY helpful in many everyday situations. You’ll have to have STATISTICS_LEVEL either TYPICAL (the default) or ALL and CONTROL_MANAGEMENT_PACK_ACCESS=’DIAGNOSTIC+TUNING’ (the default as well).

–Mike

One thought on “SQL Monitoring – Limitation at 300 lines per statement

Leave a Reply

Your email address will not be published. Required fields are marked *