In most cases, SQLMON has little impact on system performance. However, be careful when you use the SampleIO subsystem, because it can generate enough overhead to impact the performance of other DBEnvironment sessions.
Be careful also when you use the Static subsystem. The Static subsystem differs from the other SQLMON subsystems, because it establishes a connection with the DBEnvironment. When you leave the Static subsystem for another subsystem, the connection closes.
SQLMON obtains information on the primary screen in the subsystem, the Static screen, when you issue a SET DBENVIRONMENT command. System performance should not be affected when you access the Static screen.
However, when you access other screens in the Static subsystem, SQLMON issues commands that are similar to the UPDATE STATISTICS statement. These commands perform serial scans on each DBEFileSet, which can be very time consuming (however, the commands do not update statistics in the system catalog or invalidate stored sections). SQLMON does not acquire locks on user tables during this processing.
To improve performance, you can issue the command
SET DBEFILESET DBEFileSetName
|
which improves performance, because
SQLMON obtains information only about the objects in
DBEFileSetName, thereby reducing the number of serial
scans that must be performed
Serial scans are performed only once on DBEFileSetName. Serial scans occur the first time you access a screen other than the primary screen in the subsystem, not when you issue the SET DBEFILESET command. When you access another screen in the subsystem, you view information obtained during the original scans.
If the DBEFILESET variable is OFF, SQLMON performs scans on each DBEFileSet each time that you access a screen in the Static subsystem.
