How to use HDBAdmin to analyze performance traces in SAP HANA

[Former Member]

Most of the time, the Plan Visualizer is sufficiently powerful to understand what is going on inside of SAP HANA when you run a query. However, sometimes you need to get to a lower level of detail to understand exactly what is going on in the calculation engine.

It is then possible to use HANA Studio to record performance traces, and analyze them with HDBAdmin. This is a fairly advanced topic, so beware!

First, let's pick a query which runs slowly. This query takes 12 seconds, which is longer than I'd like. Admittedly, it's a tough query, grouping 1.4bn transactions and counting over 2m distinct customers.

We can double click on our HANA system in the HANA systems view, which will bring up the HANA Overview. Select Trace Configuration.

Now select the little edit button, next to the Performance Trace. Give your trace a name - I called it slowquery.tpt, and optionally select your username as a restriction, if you're sharing the system. You don't want to be doing anything else with this user whilst you run this query. You need to select a duration - 60 seconds is enough for me.

Select Finish, and immediately go and rerun your query. You can go and disable the performance trace in the same place, or you can just wait for the time to expire if you're not doing anything else.

Now, we need to go and get some software. You need two things - an X-Windows Client and a SSH client. If you are using MS Windows then I highly recommend Xming and PuTTY - other software usually causes problems. Go ahead and download and install them. When you run Xming, you will see an X appear in your task bar. Hover over it - and you should see that it shows Xming Server:0.0. If it doesn't show 0.0, take a note of what it says.

Now, you need to fire up PuTTY. PuTTY doesn't come with an installer - it's just a single executable, and it will bring up a configuration window like this:

Put your HANA hostname or IP in "hostname". Then select Connection -> SSH -> X11. Tick Enable X11 forwarding and type localhost:0.0 (or whatever number Xming displayed) in the X display location. This enables putty to know that we have an X server running on our local machine, and tell HANA about this.

Go back to the Session tab on the left, type a name for your session (I called it HANA) and select Save. Now select Open.

PuTTY should connect to your HANA system. You MUST now login as the correct HANA admin user - hdbadm in my case. This is critical.

Now we can do two things just to check things are good.

1) echo $DISPLAY - this shows us that we have the display correctly set. Note that it shows localhost:10.0 - that's just PuTTY taking care of X11 for us. Good.

2) xclock - this is the tried and tested way to check that X11 is functioning. You may see an Xming at the bottom of your screen and have to select that before you see the clock. Close the clock once it comes up - we are now good.

You can go right ahead and type ./HDBAdmin.sh, and HDBAdmin should now open.

Unfortunately HDBAdmin doesn't work out the box (not sure why) and you have to install the Emergency Support Package, the first time you run it. We do this with 4 little commands:

1) cd exe

2) sudo tar zxvf /usr/sap/HDB/SYS/global/hdb/emergency/emergencySupport.tgz .

3) sudo chown hdbadm.sapsys AttributeEnginePy.so _fuzzyPy.so executorPy.so

4) cd ..

This moves us into the executables folder, extracts the emergency support library as a superuser (you will need the root password) and then changes the permissions so the HANA user is the owner. Note that I used SID HDB, which is the name for my system. Yours will be different.

Now you can go ahead and rerun ./HDBAdmin, and it will run without errors.

Select More, which will prompt you to move to Advanced Mode, and then Perf. Trace. We are now in the right mode to open our query. Finally!

Select the Load button, and then double click on your machine name (saphana1), and then double click on the trace folder. You should now see your performance trace appear. Select your trace and select Open.

Your performance trace is now opened. Select Call plans, and you should see your slow-running query. Double click on it.

Interestingly, the trace shows in this case that the cost is in merging dicts and aggregating in parallel. This system has a large number of partitions on the table (60) and there is a cost in doing a count distinct in each partition, and then remerging them. Perhaps a simpler partition strategy or one with the CUSTOMER_ID field as a hash, would reduce the cost of this query. I'll give that a go.

Final Words

The HDBAdmin tool is a legacy tool which is incredibly powerful, but not that easy to use. The PlanViz tool inside HANA Studio is much easier to use and easier to understand.

But, if you are stuck and you need to get into the depths of query execution in HANA, HDBAdmin is a very powerful tool. Happy hunting!

[former_member182046]

Awesome, John, thanks a huge lot for this post.

Best,

Thorsten

[Former Member]

No problems. Thanks for getting me to pull my finger out and write it. Did it fix your problem?

[Former Member]

John

If you ignore the ESP step and continue to Load Trace, this still seems to allow you to upload a tpt file. Do you see any issue in not installing the ESP and continuing?

Steve

[Former Member]

I'm not sure. Maybe lars.breddemann could comment on that. I just don't like error messages :smile:

[former_member184871]

Thanks applebyjfor putting it up :smile: will give a try.

Regards

Kumar

[rama_shankar3]

Great Info and Steps - Thanks John! :smile:

[former_member182302]

Thank you John for the detailed explanation and this saved me at my work place today ... got to see this exactly when i needed this :smile:

Regards,

Krishna Tangudu

[former_member182302]

Sharing this in case if anybody is trying to do the same as explained in detailed by John above using AWS acccess from hanacloud server,

You will not be able to SSH using hdbadm user and you will have to use "Root" user and if you try to SU to hdbadm after logging with "Root" user then the tunnel breaks and $DISPLAY value will become "Empty" and you may not be able to open the .HDBAdmin tool .

Even if you try to set $DISPLAY value to the previous value before doing -SU it may fail due to "Authentication error".

Hence you need to add the xauth along with setting the $DISPLAY value as shown below:

Regards,

Krishna Tangudu

[Former Member]

Why can't you login as hdbadm - just change the password and you'll be good.

[former_member182302]

Hi John,

Not sure, I tried it but am unsuccessful in logging with hdbadm.

Also they kept this "(Note: ssh connection is possible only with root user)" in the Note pad they kept in AWS which holds all the credentials related information.

Regards,

Krishna Tangudu

[lbreddemann]

Sure :smile:

Ok, the ESP provides online access to the database in a highly privileged mode.

It's not required to display performance trace files, but it would be required to trigger performance traces out of HDBAdmin.

Anyhow, the HDBAdmin tool is not at all supported, not documented and provides no additional features that are not available via HANA studio but required to run SAP HANA.

In short: there's no need to use the tool (as John already pointed out, PlanViz is way easier to work with).

cheers,

Lars

[former_member205400]

So John, very good article but not really applicable for the ones that REALLY need this information. Only our Basis guys are going to get to use this if they have to log in using hdbadmin and they NEVER have time for the peeps doing the modeling and such.

I'm an admin, but not Basis ... I have the responsibility to make sure the stuff we roll out really performs. We have had cases where peeps put like 15 AT views connected in a unx and trying to run it brought the system down. That costs alot of down time for the rest of the users.

What should we be using to do perf monitoring on qry's and proc's?

Mike

[Former Member]

Well in this world of DevOps, quite a lot of modelers like me have access to this. Certainly if you are a performance guy then your team should give you access to HDBAdmin.

In the real world I rarely use HDBAdmin - only for certain end-end scenarios - probably once every 6 months.

I often use the PlanViz tool which will work even on complex views. Also, there is the Expensive SQL Statements analysis, which you can use to find slow SQL. Then you can run it in PlanViz. Between these two tools I can troubleshoot 95% of performance problems.

[former_member193666]

Hi Krishna,

Thanks so much for the info and screen shot, I finally can use HDBAdm.sh ! Super helpful

[former_member535249]

Thanks John. Very useful

[former_member629592]

Very precious and informative . Thanks John

[former_member667207]

Hi John & Lars,

Is it still supported to use HDBAdmin.sh ?

2534881 - Issues while working with HDBAdmin tool  --> This note says that it is for internal use only.

2520774 - FAQ: SAP HANA Performance Trace  --> This note says, that HDBadmin tool can be used and has a pointer to this blog too.

We also have the SQL Analyzer Python tool in place.

Request you help to understand better on which tool to be used when please .

Thanks,

Phani

 

 

 

[selim_derouiche]

Hello,

planviz does not work if we do inserts or updates

SAP support confirms that this is a bug planned to SP 6 (next SP of SP5)

insert with select does not have same plan as select only, for example when using constants.

insert into T select a , 'X' from S will be a lot different from select a , 'X' from S, since we remark a big difference in memory consumption.