is "diagnose monitor" eating devspace ?!?

[Former Member]

Hello,

last week we had to activate the diagnose monitor to check for some long running statements. Activation looks like:

DIAGNOSE MONITOR OFF

//

DIAGNOSE MONITOR CLEAR

//

DIAGNOSE MONITOR ROWNO 3000

//

DIAGNOSE MONITOR TIME 20000

//

DIAGNOSE MONITOR DATA ON

We expected not too much data, therefore the diagnose monitor was active about 14hours.

The strange thing (which we cannot explain yet) is, that in this time we had an increasing data volume of more than 10GB, and this is much, much more as our "standard" working day.

We had ~35GB permitted data before and right before disabling the diagnose monitor we had ~45GB permitted data. At that point of time our data devspace was filled up to 90% and we decided to add more devspace.

After setting the database to db_offline and db_online afterwards we've been back to something like 36GB permitted data.....?!?!

The usage scenario of our application at this day was not abnormal high, which could cause data transfers in such dimensions, and the incremental backup of that day is only 3GB g'zipped.

Is it possible that the diagnose monitor is writing a lot of data in some temp tables, or anywhere else, which could lead to this enormous data-devspace consumption ?

We're running MaxDB 7.5.0.38 on openSuse 10.2

any help appreciated....GERD...

==========================

BTW: after 14h of diagnose monitor enabled we only had about 200 statements in the result set of:

SELECT

sysmonitor.parseid,

rows_read,

rows_qual,

round((100/rows_read)*rows_qual, 4) as selectivity_percent,

virtual_reads,

subrequests,

strategy,

runtime,

vwaits,

vsuspends,

physical_io,

rows_fetched,

fetch_calls,

result_copied,

datetime,

username,

SUBSTR (sql_statement, 1, 7000)

FROM

sysmonitor,

sysparseid

WHERE

sysmonitor.parseid = sysparseid.parseid

ORDER BY

runtime

DESC

[lbreddemann · Accepted solution]

> Is it possible that the diagnose monitor is writing a lot of data in some temp tables, or anywhere else, which could lead to this enormous data-devspace consumption ?

>

> We're running MaxDB 7.5.0.38 on openSuse 10.2

Yes, that's possible.

And it's even documented...

1098561 Problems with the command/resource monitor (details)

1081508 Performance problems after you activate the command monitor

1091989 Problems using the command/resource monitor

3.  MaxDB Version 7.5                                                                                
< 7.5.00 Build 35:The upgrade is urgently recommend, the monitors     
     may only be turned on in an extreme emergency when the SAP System is  
     stopped, not in a running SAP System (Notes 802806, 894898)                                                                                
'>= 7.5.00 Build 35: The fill level of the database must be monitored  
     to prevent a DB full situation due to the growing SYSPARSEID table;   
     proceed as per Note 994590.

All of those problems had been fixed a long time ago.

So by just using MaxDB 7.6.06 you would be out of trouble here...

[Former Member · Accepted solution]

thank you Lars, for your quick and helpful answer....