Query slow performance sys_databases.m_backup_catalog

[Former Member]

We are in the process of deploying Hana and have been playing around with a couple of VM's just to get familiar with HANA before we get our real hardware in a few weeks. I realize that the VM does not meet specs etc but I am not sure this is related to that.

The VM I am working on is a multi-tenant install with 3 tenants. Version 1.00.110 on Suse Linux 3.0.101. The tenants are essentially empty other than the few tables we have created.

Noticed something today that I was not expecting when running a query against sys_databases.m_backup_catalog. The actual query I ran is more complex than the one below but the one below shows the issue. It is taking 7 minutes to query sys_databases.m_backup_catalog.

If I query the tenant's m_backup_catalog directly it runs in < 1 second.

Is that expected behavior? I understand it would be slower because of the combined nature of the sys_databases

view but 7 minutes seems excessive. I have not spent a lot of time looking at the explain plan or anything yet

was just curious if this was to be expected. Part of what lead to this was that using the backup and recovery console

from SYSTEMDB to look at one of the tenants exhibits the same behavior where it takes many minutes for the gui to refresh.

The commands below were run consecutively and used the time command just to show the elapsed time.

more ./count1.sql

  select count(*) from sys_databases.m_backup_catalog

  where database_name = 'SCOTT';

time hdbsql -d systemdb -u system -p XXXXXXXXXXXXXX -I ./count1.sql -o /tmp/count1.out

  real    7m48.532s

  user    0m0.000s

  sys     0m0.008s

more /tmp/count1.out

  COUNT(*)

  895

more ./count2.sql

  select count(*) from m_backup_catalog;

time hdbsql -d scott -u system -p XXXXXXXXXXXXX -I ./count2.sql -o /tmp/count2.out

  real    0m0.055s

  user    0m0.004s

  sys     0m0.008s

more /tmp/count2.out

COUNT(*)

895

Repeating the first query results in slightly better time of 5 minutes but still a lot slower than the one directly in the tenant.

[yakcinar]

Hello Scott,

7m is high value for HANA system.

I think your hw is certified hw by SAP. Check your hw from below list first.

Certified SAP HANA&amp;reg; Hardware Directory

Then you can run hwcct python script for HW check like below picture to see the result.

python HanaHwCheck.py

If these two are okey then I would check performance tab of the HANA Studio for the execution of this sql script. I could open SQL trace also.

Regards,

Yuksel AKCINAR