RDP302 - Understanding SAP HANA Database Performance

Lars Breddemann, SCE EMEA

Richard Bremer, SCE EMEA

Charu Agarwal, SCE APJ

TechEd 2013

RDP302

Understanding SAP HANA Database Performance

Disclaimer

This presentation outlines our general product direction and should not be relied on in making a

purchase decision. This presentation is not subject to your license agreement or any other agreement

with SAP. SAP has no obligation to pursue any course of business outlined in this presentation or to

develop or release any functionality mentioned in this presentation. This presentation and SAP's

strategy and possible future developments are subject to change and may be changed by SAP at any

time for any reason without notice. This document is provided without a warranty of any kind, either

express or implied, including but not limited to, the implied warranties of merchantability, fitness for a

particular purpose, or non-infringement. SAP assumes no responsibility for errors or omissions in this

document, except if such damages were caused by SAP intentionally or grossly negligent.

You and SAP HANA

• SAP HANA is, amongst other things, first and foremost a database management system.

• Or short: it’s the DB system for your application.

• As that it takes care about storing and retrieving your data safely and efficiently.

• That means:

You don’t have to care about that – SAP HANA offers this as a service to you, the application

developer.

• Focus on your application code and let SAP HANA figure out how to deal with your data.

Performance dilemma

But how to care about performance if the DB is handling all the data handling for me?

• Use funny SQL coding tricks? (WHERE 1=1 AND 1 IS NOT NULL…)

• Put optimizer hints into the SQL? ( /*+ INDEXACCESS … */)

• Set DB parameters? (force_fast_query_processing = true)

• This kills your productivity

• This makes your system hard to maintain and operate

• This prevents your SQL code to benefit from the

improvements in SAP HANA revisions

Performance dilemma – EXIT

(because system performance is still your responsibility)

• Don‘t treat SAP HANA as a black box!

• Understand how it works and use SAP HANA based on that!

• Because: “Garbage in, garbage out” is still true in a client/server situation.

• So, let’s see what SAP HANA does with your queries and how you can find out

about it…

The rise and fall of your SQL commands

or the lifetime of a session in SAP HANA

Application

HANA client software HANA client software

Network communication

Prepare query

Parse query or fetch

plan from cache

Shared SQL plan

cache

Handle on prepared query

Execute plan and

return result set(s)

result cache

Call query/open cursor

Providing parameter values

Fetch results

Close result set/close cursor

Close prep. statement handle

Close connection

Execution engines

OLAP

engine

Join

engine

Calc.

engine

SQL engine

Column store

Row store

Session context

SAP HANA DB Server DB Client

Authentication

(user name + password/token/certificate)

Lifetime of a SAP HANA session

Application

HANA client software HANA client software

Network communication

Prepare query

Parse query or fetch

plan from cache

Shared SQL plan

cache

Handle on prepared query

Execute plan and

return result set(s)

result cache

Call query/open cursor

Providing parameter values

Fetch results

Close result set/close cursor

Close prep. statement handle

Close connection

Execution engines

OLAP

engine

Join

engine

Calc..

engine

SQL engine

Column store

Row store

Session context

SAP HANA DB Server DB Client

• Applications create sessions in the SAP HANA server

• Sessions could be seen as a conversation between client and

server

• They provide context to the execution of SQL commands

• Sessions are facilitated over one or more physical connections

(network connections)

• SYSTEM variables are set at logon/automatically and cannot be

changed during the session e.g. session language is set via

LOCALE (ISO lang code)/LOCALE_SAP (SAP internal

language encoding)

• USER variables can be freely defined (up to 50 per session) and

accessed from outside the session (m_session_context view,

SAP HANA studio)

• APPLICATION* variables used by convention to allow easier

system monitoring

• APPLICATIONUSER should show the „real“ user (SAP

NetWeaver puts in ABAP user)

• APPLICATIONSOURCE shows source location of database call

(automatically in SAP NetWeaver)

• APPLICATIONVERSION should provide versioning information

of the DB client

Session context variables

Session context variables – used for monitoring

• Which user is running the

session?

• DB user ≠ OS user

• DB user ≠ SAP

NetWeaver user

• APPLICATIONUSER

provides this information

• APPLICATION*-variables provide additional meta data

for monitoring and answers questions like:

• Which ABAP module runs a statement?

• Which SAP HANA Studio versions are used?

• …

Lifetime of SAP HANA session

Application

HANA client software HANA client software

Network communication

Prepare query

Parse query or fetch

plan from cache

Shared SQL plan

cache

Handle on prepared query

Execute plan and

return result set(s)

result cache

Call query/open cursor

Providing parameter values

Fetch results

Close result set/close cursor

Close prep. statement handle

Close connection

Execution engines

OLAP

engine

Join

engine

Calc..

engine

SQL engine

Column store

Row store

Session context

SAP HANA DB Server DB Client

• Applications create sessions in the SAP HANA server

• Sessions could be seen as a conversation between client and

server

• They provide context, a semantic frame, to the execution of SQL

commands

• Sessions are facilitated over one or more connections

• Connections are physical network connections

• Can be cancelled/killed by administrator

• Enable multi-node access for a single DB session

• Can be automatically cancelled based on timeout mechanisms

indexserver.ini

[session]

idle_connection_timeout_application_statisticsserver

idle_connection_timeout_application_hdbstudio

idle_connection_timeout

connection_history_lifetime

Connection information – SQL

Connection information – DBACockpit

Connection information – SAP HANA Studio

Lifetime of a SAP HANA session

Application

HANA client software HANA client software

Network communication

Prepare query

Parse query or fetch

plan from cache

Shared SQL plan

cache

Handle on prepared query

Execute plan and

return result set(s)

result cache

Call query/open cursor

Providing parameter values

Fetch results

Close result set/close cursor

Close prep. statement handle

Close connection

Execution engines

OLAP

engine

Join

engine

Calc..

engine

SQL engine

Column store

Row store

Session context

SAP HANA DB Server DB Client

• With a session/connection ready, the application can send

command text to SAP HANA.

• SQL commands really are DB program source code that needs to

be parsed, validated, optimized and compiled before it can be

executed.

• Similar to activation of ABAP reports

• This can take a lot of time and resources and need to be

executed for every SQL command – so caching the compiled

program is key for performance!

• Think of parameterized statements!

• Shared SQL plan cache keeps

compiled and optimized

statements in memory and re-

uses these for exact matching

SQL commands

• Also provides execution statistics

for performance analysis

indexserver.ini

[sql]

plan_cache_size

The “magic” happens here…

• Query transformation

• Query optimization and

• plan generation (1. Part!)

Shared SQL plan cache – SQL monitoring

• Most flexible analysis approach

• System views M_SQL_PLAN_CACHE and

M_SQL_PLAN_CACHE_RESET provide lots of

runtime statistics on statement level

Shared SQL plan cache – DBACockpit monitoring

• Easy filtering and sorting of the data

• Straight forward workflow to find e.g. the long runner

SQL statements

• Build-in navigation to EXPLAIN PLAN, SQL Editor

and PlanViz-File creation

Shared SQL plan cache – SAP HANA Studio monitoring

• Easy filtering and sorting of the data

• Straight forward workflow to find e.g. the long runner

SQL statements

• Build-in navigation to Plan Visualization

Query plan information available after preparation

=

• Explain plan and it’s graphical representation represent the plan created and optimized by the SQL optimizer.

• It’s based on runtime information about the data (e.g. number of records, data distribution etc.) as well as table

statistics (if available).

• Upon actual execution, additional optimizations are applied by the execution engines (e.g. column pruning in the

calculation engine, join ordering in the join engine, removal of superfluous joins, etc.)

• That means, the explain plan can not tell us, where time is spent during execution!

Lifetime of a SAP HANA session

Application

HANA client software HANA client software

Network communication

Prepare query

Parse query or fetch

plan from cache

Shared SQL plan

cache

Handle on prepared query

Execute plan and

return result set(s)

result cache

Call query/open cursor

Providing parameter values

Fetch results

Close result set/close cursor

Close prep. statement handle

Close connection

Execution engines

OLAP

engine

Join

engine

Calc..

engine

SQL engine

Column store

Row store

Session context

SAP HANA DB Server DB Client

• As a result of the parsing process the application gets a handle for

the parsed statement

• With this handle the application can access the parsed statement

directly and thereby save time (no text parsing required anymore

to find the statement in the shared SQL plan cache)

• SAP NetWeavers’ database

interface (DBIF) keeps track of up

to 1000 handles per work process

• ABAP developers automatically

benefit from this feature

Lifetime of a SAP HANA session

Application

HANA client software HANA client software

Network communication

Prepare query

Parse query or fetch

plan from cache

Shared SQL plan

cache

Handle on prepared query

Execute plan and

return result set(s)

result cache

Call query/open cursor

Providing parameter values

Fetch results

Close result set/close cursor

Close prep. statement handle

Close connection

Execution engines

OLAP

engine

Join

engine

Calc..

engine

SQL engine

Column store

Row store

Session context

SAP HANA DB Server DB Client

• calling/opening/running a cursor  provide parameter values and

execute a prepared query

• SAP HANA uses multiple execution engines

• All of them run upon the basic data structures:

column store tables or row store tables

• Joins of column store tables  Join Engine (JE-

POPs)

• OLAP query support  OLAP Engine (OE, BW-

POPs)

• Complex calculations  Calculation engine

(CE-POPs)

• Rest is handled by SQL engine

• “Prepare once - execute often” only

possible with parameterized

statements (or by asking the same

question over and over again)!

• Massive time savings possible as

no parsing required for similar

queries with different filter condition

values.

Parameterized Statements…?!

select id, name from employees

where id = 1;

select id, name from employees

where id = 2;

• 2 similar, but different statements

• 2 x parsing, optimizing

• 2 x storing and keeping the execution

plan in memory

• Can be executed only for the hard

coded ID number.

select id, name from employees

where id = ?;

• Just 1 statement with a parameter

• 1 x parsing, optimizing

• 1 x storing and keeping the execution plan

in memory

• Can be re-used indefinitely

Execute [F8]

How to know what is really executed?

• The execution engines perform

additional optimizations when the

parameter values are known.

• This can change the actual execution

tremendously.

• Plan Visualization  Execute shows

actual operation order, intermediate

result set size, parallelism, biggest

runtime contributors, etc.

Execution Plan

Visualization

Execution Plan

timeline

Lifetime of SAP HANA session

Application

HANA client software HANA client software

Network communication

Prepare query

Parse query or fetch

plan from cache

Shared SQL plan

cache

Handle on prepared query

Execute plan and

return result set(s)

result cache

Call query/open cursor

Providing parameter values

Fetch results

Close result set/close cursor

Close prep. statement handle

Close connection

Execution engines

OLAP

engine

Join

engine

Calc..

engine

SQL engine

Column store

Row store

Session context

SAP HANA DB Server DB Client

• Once the result set is computed it’s kept at the SAP HANA server until the client closes the cursor.

 Memory allocation!

 Record versions need to be preserved if exclusive locks have been set.

• The client can decide not to fetch all records, e.g.. to display only the first 10 result rows.

 better use SELECT TOP 10.. /LIMIT 10 for this!

• Closing the cursor removes the result set from the server.

• Closing the statement handle only decreases the reference counter of the statement in the shared SQL cache (doesn’t free anything immediately,

but allows removal from cache when the statement is not referenced anymore by any session).

• Closing the connection ends the session and implicitly will release all locks again.

How does that look in the real world?

SAP NetWeaver SQL trace (ST05)

vanilla JAVA code

• Prepare

• Open

• Fetch

• Close

Performance Analysis Tools

What tools to you use when…

Trace options in SAP HANA Studio

Default settings

for traces

Actual system

wide trace

settings

Actual user

specific trace

settings

system wide or

user specific

SQL trace

SAP internal

performance

trace & profiler

SAP internal

sampling profiler

Expensive

statement trace

Setup for

NetWeaver E2E-

traces via SAP

PASSPORT

Trace options in SAP HANA Studio

• SQLTRACE (PYTHON TRACE)

• PERFORMANCE TRACE + FUNCTION PROFILER

• KERNEL PROFILER

• OPTIMIZER TRACES

• LOAD GRAPH

• STATISTICS SERVER

• EXPENSIVE STATEMENTS TRACE

• SQL PLAN CACHE

• EXPLAIN PLAN

• PLAN VISUALIZATION

SQLTRACE (PYTHON TRACE)

• Incomprehensible w/o SAP HANA development knowledge

• Is like “taking a sledgehammer to crack a nut"

• Doesn't provide what happens during the execution (rather input  output trace)

PERFORMANCE TRACE + FUNCTION PROFILER

• TREX heritage

• unsupported for non-SAP-Development usage

• requires HANA server access + X11-Window client

• requires special privileges

• doesn't cover row store activity

• tool is barely documented and complex

• function profiler is useless w/o source code

KERNEL PROFILER

• stack code sampling

• profiler tells you how often a specific part of the SAP HANA code was executed and how long that

took (approx.)

• again, useless w/o source code

OPTIMIZER TRACES

• sqlopttime (runtime information for the query optimization phase)

• sqloptStep (single query optimization/transformation steps)

• trex_qo (TREX query optimizer  column store plans)

Trace options in SAP HANA Studio

• SQLTRACE (PYTHON TRACE)

• PERFORMANCE TRACE + FUNCTION PROFILER

• KERNEL PROFILER

• OPTIMIZER TRACES

• LOAD GRAPH

• STATISTICS SERVER

• EXPENSIVE STATEMENTS TRACE

• SQL PLAN CACHE

• EXPLAIN PLAN

• PLAN VISUALIZATION

pretty useless for mere mortals like us…

LOAD GRAPH

• Nameserver data (nameserver_history.trc)

• TREX heritage

• Highly aggregated data - cannot (easily) be mapped to a specific user action

• Yet, give a "feeling" of how workload "looks like"

• Could be used to give a rough baseline

• No function to compare to time frames side-by-side or by overlay

• MIN/MAX/AVG numbers are highly volatile and fully depend on the chosen time frame

LOAD GRAPH – Screenshot

STATISTICS SERVER

• _SYS_STATISTICS schema

• Lots of data, gathered by automatic snapshots

• Are purged automatically

• Is used as the data source for solution manager (performance warehouse)

• Provide alerts for SAP HANA

STATISTICS SERVER

EXPENSIVE STATEMENTS TRACE

• Provides information about runtimes of single statement executions

• Captures the parameter values for parameterized statements

• Can be easily configured to focus on the real long running statements

• What long running statements are, clearly depends on your use case.

• Sometimes this means milliseconds, sometimes it's minutes

EXPENSIVE STATEMENTS TRACE – Setup Screen

EXPENSIVE STATEMENTS TRACE – Navigation

SQL PLAN CACHE

• Contains two kind of information:

1. parsed statements

2. call and runtime statistics for these statements

• Can answer questions like:

• "How much time was spent on actually executing the statement?"

• "Can my statement be reused or does it need to be optimized over and over again?"

• Depending on the use case, the default cache size can be much to small

• We're gaining experience with that right now

Trace options in SAP HANA Studio

• SQLTRACE (PYTHON TRACE)

• PERFORMANCE TRACE + FUNCTION PROFILER

• KERNEL PROFILER

• OPTIMIZER TRACES

• LOAD GRAPH

• STATISTICS SERVER

• EXPENSIVE STATEMENTS TRACE

• SQL PLAN CACHE

• EXPLAIN PLAN

• PLAN VISUALIZATION

System wide monitoring – the

BIG picture

EXPLAIN PLAN

• Can't look "into" modeled views!

• Raw formatting

• Focusses on "costs" and estimated cardinalities (no. of rows)

• First information on which engine is used

EXPLAIN PLAN – Demo 1

SELECT TOP 20

"ARTICLE_LABEL_LOCALE",

sum("MARGIN") AS "MARGIN",

sum("AMOUNT_SOLD") AS "AMOUNT_SOLD",

sum("QUANTITY_SOLD") AS "QUANTITY_SOLD"

FROM "_SYS_BIC"."i028297.demo/AN_SALES"

WHERE "FAMILY_NAME" IN ('City Trousers', 'City Skirts')

GROUP BY

"ARTICLE_LABEL_LOCALE"

ORDER BY "AMOUNT_SOLD" desc;

ARTICLE_LABEL_LOCALE MARGIN AMOUNT_SOLD QTY_SOLD

Side Slit Long Skirt 64.437,700000000004 130.569,09999999992 598

Lycra Trousers 45.848,600000000006 105.164,09999999992 600

Corduroy Shorts 26.852,000000000015 76.395,49999999994 429

…

EXPLAIN PLAN – Demo 2

EXPLAIN PLAN – Demo 3

Which Engines are used to process

the query?

EXPLAIN PLAN – Demo 4

OPERATOR_NAME OPERATOR_DETAILS

COLUMN SEARCH i028297.demo/AN_SALES.ARTICLE_LABEL_LOCALE,

SUM(i028297.demo/AN_SALES.MARGIN),

SUM(i028297.demo/AN_SALES.AMOUNT_SOLD),

SUM(i028297.demo/AN_SALES.QUANTITY_SOLD)

(LATE MATERIALIZATION)

LIMIT NUM RECORDS: 20

ORDER BY SUM(i028297.demo/AN_SALES.AMOUNT_SOLD) DESC

AGGREGATION GROUPING:

i028297.demo/AN_SALES.ARTICLE_LABEL_LOCALE,

AGGREGATION:

SUM(i028297.demo/AN_SALES.MARGIN),

SUM(i028297.demo/AN_SALES.AMOUNT_SOLD),

SUM(i028297.demo/AN_SALES.QUANTITY_SOLD)

COLUMN VIEW FILTER CONDITION:

i028297.demo/AN_SALES.FAMILY_NAME = 'City Trousers'

OR

i028297.demo/AN_SALES.FAMILY_NAME = 'City Skirts'

EXPLAIN PLAN – Demo 5

OPERATOR_NAME OPERATOR_DETAILS

COLUMN SEARCH i028297.demo/AN_SALES.ARTICLE_LABEL_LOCALE,

SUM(i028297.demo/AN_SALES.MARGIN),

SUM(i028297.demo/AN_SALES.AMOUNT_SOLD),

SUM(i028297.demo/AN_SALES.QUANTITY_SOLD)

(LATE MATERIALIZATION)

LIMIT NUM RECORDS: 20

ORDER BY SUM(i028297.demo/AN_SALES.AMOUNT_SOLD) DESC

AGGREGATION GROUPING:

i028297.demo/AN_SALES.ARTICLE_LABEL_LOCALE,

AGGREGATION:

SUM(i028297.demo/AN_SALES.MARGIN),

SUM(i028297.demo/AN_SALES.AMOUNT_SOLD),

SUM(i028297.demo/AN_SALES.QUANTITY_SOLD)

COLUMN VIEW FILTER CONDITION:

i028297.demo/AN_SALES.FAMILY_NAME = 'City Trousers'

OR

i028297.demo/AN_SALES.FAMILY_NAME = 'City Skirts'

EXEC_ENGINE SCHEMA_NAME TABLE_NAME TABLE_TYPE

COLUMN NULL NULL NULL

COLUMN _SYS_BIC i028297.demo/AN_SALES OLAP VIEW

PLAN VISUALIZATION

• Relatively easy to use

• Graphical tool, build into SAP HANA studio

• Provides estimation and actual runtime statistics for memory, CPU time, parallelism and total runtime.

• Can be access via SQL Editor, SQL statement cache view, Expensive statement view

• Can be saved (XML file) and reviewed later on

• THE TOOL OF CHOICE!

Plan Visualization (PlanViz)

PlanViz

PlanViz – display timelines (pre-SPS 06)

PlanViz – display timelines

PlanViz – display timelines (SPS 06)

• New PlanViz

Perspective

Trace options in SAP HANA Studio

• SQLTRACE (PYTHON TRACE)

• PERFORMANCE TRACE + FUNCTION PROFILER

• KERNEL PROFILER

• OPTIMIZER TRACES

• LOAD GRAPH

• STATISTICS SERVER

• EXPENSIVE STATEMENTS TRACE

• SQL PLAN CACHE

• EXPLAIN PLAN

• PLAN VISUALIZATION

Single statement analysis

Summary

• Use SAP HANA database as a service, but not as a black box!

• In case of performance problems, think about where the problem could occur.

• Use the proper tools to measure if the problem really is where you think it is.

Further Information

SAP Education and Certification Opportunities

SAP HANA Training Curriculum

https://training.sap.com/us/en/curriculum/hana

-g-en

Watch SAP TechEd Online

www.sapteched.com/online

SAP Public Web

SCN:

SAP In

-Memory computing: http://scn.sap.com/community/hana-in-memory

SAP HANA Developer

center: http://scn.sap.com/community/developer-center/hana

Further important information sources:

Public HANA community:

http://www.saphana.com/

Public

documentation: http://help.sap.com/hana_platform

SAP TechEd Virtual Hands-on Workshops and SAP TechEd Online

Continue your SAP TechEd education after the event!

SAP TechEd Virtual Hands-on Workshops

 Access hands-on workshops post-event

 Available January – March 2014

 Complementary with your SAP TechEd registration

SAP TechEd Online

 Access replays of keynotes, Demo Jam, SAP TechEd

LIVE interviews, select lecture sessions, and more!

 View content only available online

http://saptechedhandson.sap.com/

http://sapteched.com/online

Feedback

Please complete your session evaluation for RDP302.

Thanks for attending this SAP TechEd session.

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG.

The information contained herein may be changed without prior notice.

Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.

National product specifications may vary.

These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and

SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth

in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.

SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and

other countries.

Please see http://www.sap.com/corporate-en/legal/copyright/index.epx#trademark for additional trademark information and notices.