Troubleshooting sidecar causing memory unloading
I occasionally am getting complaints of a report becoming very slow. The report uses MSEG which is queried frequently every single day and so I would not expect this to be automatically unloaded. If it were unloaded being that it's so popular I would assume we have a serious capacity issue. Anyhow when this happens often I don't hear about it until AFTER the unload happens, I go back into Performance/Load KPI's and I can see massive unloading at a certain time of day.
I'm trying to find out what sidecar query that is running that triggers the need for massive unloading. I ask SAP Netweaver team to give me a list of queries running during this time but of course it's a huge list. I look at SQL plan cache in HANA for what's executed that day and filter out anything using MSEG and I'm still left with a needle in a haystack. To make matters worse the Netweaver team would like an application user which doesn't seem to show in the plan cache. The only way I can capture the application user is if I catch it running LIVE at the time of the unloading event.
Does anybody have any tips on how to pinpoint root of a memory hog report like this? All our sidecar reports are tested before going to production but I think what could be happening is users are running for much larger selections than originally tested or something like this and I'm trying to figure out which report it could be.
Fernando Da Ros replied
The list of queries executed will not explain which one required memory from system to unload others, also I don't expect one with MSEG table.
With the time which it should happen. I'd turn the memory tracking and expensive statements to catch where is the one to focus on.
alter system alter configuration ('global.ini','SYSTEM') set ('resource_tracking','enable_tracking') ='on' with reconfigure;
alter system alter configuration ('global.ini','SYSTEM') set ('resource_tracking','memory_tracking') ='on' with reconfigure;
... and turn expensive statements on... and active monitor... Things will slow down but you have a better chance to catch the hungry statement.
Regards, Fernando Da Rós