Question Re: HANA Optimizer function in Technology Q&A

HANA Optimizer function

Former Member — Thu, 17 May 2018 04:49:20 GMT

HI,

I'm using SAP HANA DB.

There are some problems with user's query. Sometimes it makes OOM on HANA DB.

So, i'm thinking of a function that blocks user's query(which may cause OOM) before executing query on HANADB .

If SAP HANA optimizer can estimate cost of query user asked, i think that it may possible to forecast that the query may cause OOM or Not.

so if SAP HANA optimizer have that kind of function, please tell me how to use it.

(If not, also please tell me that it's impossible)

thank you for your help

regards, Tae

Re: HANA Optimizer function

dvankempen — Fri, 18 May 2018 08:58:39 GMT

Hi Tae,

Have you considered Admission Control and/or Workload Management)?

Regards,

Denys / SAP HANA Academy

Re: HANA Optimizer function

lbreddemann — Sun, 20 May 2018 23:49:16 GMT

The HANA query optimizer does estimate the memory usage to expect (in terms of records to be expected by the different processing steps) - however using this information alone is insufficient to avoid resource overallocation, which can lead to out of memory errors.

No part of a database can predict what the resource allocation will be in the future - that is when your query is being processed - but only how much resources your query might use. HANA can dynamically make memory available, when it needs it and when no other current session requires it. These requirements usually are triggers by components outside of the HANA server, most notably by the database clients.

In consequence, this means, it is not generally possible to allow running arbitrary queries against a database and guarantee that there will always be sufficient resources to process them.

Having said that, typically it is possible to analyse failing statements and learn the cause(s) for the OOM and to find solutions for them. Very often (in my experience) one can change the query or data model to enable a more memory efficient processing.