How to Check Which Statements Return Big Results

[detlev_beutner]

Hi there,

We have a production problem at the moment as follows: A WAS Java is running on MaxDB (7.5.00.32; I know, it is old), had no issues for years.

Now, since 12 days, the system halts every morning, due to the DB not answering any more. On the DB side, normally (after a WAS / DB restart), the DATA area is filled up to 79%. We observed now that during the issue, this goes up to > 95%. So I expect this coming from results, as this space gets free after a restart immediately.

We also had a bad design for the DATA area (one DATA file with 15GB). We will change this within the next hours to 5 * 5GB DATA files, removing the 15GB DATA file afterwards. That should (a) offer more parallelism (we see many IOWaits and "IOWait(R)(041): b13get_node: await read              :-(" during the critical situation), and (b) offer enough space.

If this succeeds (any additional thoughts welcome), I would like to analyse which statements lead to big results, as probably this is what has changed 12 days ago. Question: How to check this?

Thanks in advance

Detlev

[Former Member]

You can use Database Analyzer. SAP note 819324 can be helpful too.

[detlev_beutner]

Hi Samuli,

Thanks for the hint, but hm, that is a quite general answer. I don't see my requirement reflected in the documentation. Just (processing-intensive in the meaning of) "long-running" statements I can analyse with an SQL trace (also done already). Anyhow, I need a specific approach to analyse which statements lead to big temp data and/or big results.

If this can be done specifically, could you point out how?!

Thanks in advance

Detlev

[Former Member]

Agreed, the instructions are generic. I can't provide specific instructions off the top of my head since I don't have access to MaxDB databases anymore (I used to run MySQL and later MaxDB on all of my SAP databases a few years back). You could look at CPU and memory usage of individual MaxDB processes and figure out what queries cause the big result set.

[detlev_beutner]

You could look at CPU and memory usage of individual MaxDB processes and figure out what queries cause the big result set.

That's not really helping, as we need to check this over more than an hour, there are (hundreds of) thousands of statements handled in this time, and CPU/memUsage do not really correspond to the temp result immediately.

No, I really would need some kind of report which can be switched on and then evaluates the statements - like an SQL trace, but with the size of temp data result (or at least with the size of the ResultSet) saved for each query so that this can be evaluated afterwards.

Anyone else?

[Former Member]

Standard MaxDB performance analysis functionality is included in the CCMS (DB50) of AS ABAP. Since you are using AS JAVA you are stuck with the generic MaxDB tools. Unless Database Analyzer, which is meant for long term analysis, provides the information you are after I don't believe it is available. You could write a custom script that takes snapshots of the system (processes, active statements, space usage, etc) at regular intervals and then another script to summarize the snapshots. Should be easy enough for example with perl. For short term analysis, monitors are being used and they have an impact on the performance of the system. I assume your analysis is correct that the growth happens in the temporary area since it is released if the database is restarted. You could make sure by looking at the system table DATASTATISTICS and seeing whether it is indeed the temporary data area that grows. You should be able to identify the growing table(s) (even system tables) by looking at the system table TABLESTATISTICS. Another idea. Could it be that one of the monitors was switched on 12 days ago, causing the problem? With MaxDB 7.5 you shouldn't switch on any of the monitors if having patch level lower than 35, see SAP note 1098561 for details.

[detlev_beutner]

Hi Samuli,

You could write a custom script that takes snapshots of the system (processes, active statements, space usage, etc) at regular intervals and then another script to summarize the snapshots. Should be easy enough for example with perl.

Hm, that probably would be a bit much of an effort (as the main issue has gone after extending / reorganizing the data volumes). We still have some hickups in one application, but that seems to be a logical issue within the application (which we are not maintaining, we just run the AS java). So I am willing to analyse something with easy-to-be-used tools (for example, I still check some long running statements and check the existence of meaningful indexes), but to develop something just to show the original developers where they are going a bad way... no... 😉

I assume your analysis is correct that the growth happens in the temporary area since it is released if the database is restarted.

Yeah, that's no question in the end; you can monitor the temp area immediately with the old and new tools.

Could it be that one of the monitors was switched on 12 days ago, causing the problem? With MaxDB 7.5 you shouldn't switch on any of the monitors if having patch level lower than 35, see SAP note 1098561 for details.

No, first the problem came up (for all app's and in the end for the complete server), then we started the analysis, not vice versa 🙂 Anyhow, this is a valid point, as we are really running patch level 32 (I know, I know, not my decision...).

Thanks for the hints anyway. I really like MaxDB and all support tools SAP offers around, but this concrete thing could have been handled better... Maybe in the future... 🙂

Thanks & best regards

Detlev

[thorsten_zielke]

Hi,

first, let me say that I cannot offer a solution for your specific question of what has caused the sporadic temp space increase.

But some general thoughts from my side:

1. 'Database Studio' displays the 'command monitor' and 'resource monitor' data (but these can cause some performance drop in 7.5). But this is not an option here since MaxDB 7.5 is not supported by Database Studio.

2. If you have a R/3 system available, I would suggest to integrate that JAVA system into the CCMS monitoring (see SAP FAQ note 990602, question 8 & 9). This way you could work with the resource and command monitor plus you would get the Database Analyzer data displayed much easier to read and you could even aggregate that data to show you daily or monthly aggregates...

Database Analyzer also shows the temp space usage in one of the 'Expert View' files. The Analyzer per default collects data every 15 minutes, but you might want to shorten the interval to 60 seconds for more accurate data. Still, in MaxDB 7.9 the Database analyzer is more sophisticated and can collect more statistics, because the Database monitoring in MaxDB has been improved...

3. If this is a SAP system, you could also open a SAP ticket for assistance.

4. It might be worth a try to let 'x_cons' run while the database is starting and you suspect the temp space increasing. You can configure x_cons to run as 'x_cons <dbname> show tasks 60 60 > out.txt' and it would collect the task states of all tasks at an interval of 60 seconds with a total of 60 calls (which results in one hour runtime...). This would not show you the cause of increasing temp space, but give you a better overview of what happens at task level at that time.

Thorsten