HANA Optimizer function

[lbreddemann]

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

[lbreddemann]

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.

[dvankempen]

Hi Tae,

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

• Managing Peak Load (Admission Control)
• Managing Workload with Workload Classes

Regards,

Denys / SAP HANA Academy