Knowledge quiz

It's time to put what you've learned to the test, get 8 right to pass this week.

Which column information do you need to find an existing explain plan in M_SQL_PLAN_CACHE?

Choose the correct answer.

PLAN_ID

USER_NAME

STATEMENT_HASH

STATEMENT_STRING

Which of the following statements about parameterized queries are true?

There are three correct answers.

You must create a plan cache entry to collect the parameterized query's explain plan.

When you run explain plan EXPLAIN PLAN FOR… without plan cache, the recompiled plan is collected.

When you capture the visualized plan with plan cache, the recompiled plan is collected.

You should execute the parameterized query as a single line to avoid plan cache confusion through different carriage returns.

The generated execution plan of a parameterized query is always more efficient than a literal query for the same target query.

Which operator is processed first in the predefined order by the column search among the following candidates?

Choose the correct answer.

Table

Join

Group by

Order by

As of SAP HANA 2.0 SPS04, which trace configurations do you need to enable under [resource_tracking] to track memory consumption using SQL trace?

There are two correct answers.

enable_tracking

statement_memory_limit_threshold

memory_tracking

threshold_duration

global_allocation_limit

What is the name of the column search that is executed by the OLAP engine?

Choose the correct answer.

Column Search

Analytical Search

Row Search

ESX Search

What does the OPERATOR_PROPERTIES column in explain plan show?

There are three correct answers.

The SQL hints applied to the plan (as of SAP HANA 2.0 SPS03)

The logical enumeration rules applied to the plan

The memory consumption of the plan

Whether the plan is precompiled or recompiled

The current table size information

Which of the following statements about column searches is true?

Choose the correct answer.

Column searches only work on column tables and columnar operators.

Column searches only have a single logical operator.

Column searches can handle any kind of query operators.

Any SAP HANA execution engine can handle column searches.

Which SQL command should you use to find the existing explain plan that was used at the time a query was executed?

Choose the correct answer.

EXPLAIN PLAN FOR SQL PLAN CACHE ENTRY <STATEMENT_STRING>;

EXPLAIN PLAN FOR SQL PLAN CACHE ENTRY <PLAN_ID>;

EXPLAIN PLAN FOR SQL PLAN CACHE ENTRY <STATEMENT_HASH>;

ALTER SYSTEM CLEAR SQL PLAN CACHE;

Which of the following statements about composite out-of-memory (OOM) events is true?

Choose the correct answer.

A composite out-of-memory (OOM) event is related to the global allocation limit.

By default, there is no limit set on statement memory usage.

It is not possible to set a statement memory limit for individual users.

When a composite out-of-memory (OOM) event occurs, SAP HANA is restarted.

10.

When do you use SQL trace?

There are three correct answers.

When you want to diminish the scope of the SQL statements to identify the string that is causing problems

When there are many SQL statements coming from an application and you don't know which query is causing a performance issue

When you want to identify statements running internally as SQL script-based statements

When you want to see the compiled plan without executing the target query

When you want to see the compilation steps for the target query