Knowledge quiz

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

If a query is part of the business logic and causes performance issues, what do you have to check?

There are three correct answers.

Query compilation

User

Query execution

Data flow

Final result

What do you have to check before you reproduce a performance issue?

There are three correct answers.

Host information

Data statistics

Replication status

Backup storage

Revision information

If you encounter a performance issue but there is no operator to reduce problematic intermediate results and no SQL hint works, what should you do next?

Choose the correct answer.

Do not use the query.

Delete the query.

Rewrite the query.

Change the data statistics in your system, regardless of your business.

Which of the following statements about partition pruning are true?

There are three correct answers.

Partition pruning can improve performance.

For partition pruning, you need to set the correct partition key and avoid expressions on filters.

Partition pruning is always applied when queries use partitioned tables.

Partition pruning works with filters that have expressions on partition columns.

SAP HANA tries to take advantage of partition pruning as much as possible.

If you want to know when a problematic query is compiled and executed, which SAP HANA views can you refer to?

There are two correct answers.

M_DATA_STATISTICS

M_SERVICE_THREADS

M_SQL_PLAN_CACHE

M_SERVICE_STATISTICS

HOST_SQL_PLAN_CACHE

You find a SQL hint that reduces the intermediate result and improves performance, but the hint is not applied. What could be the reason for this?

Choose the correct answer.

There is a possibility that the query result may be changed based on optimizer's decision.

The SQL hint creates a random query plan.

There is a possibility that the SQL hint changes the data statistics.

The plan that is newly generated by the SQL hint is too long.

Which of the following statements about using SQL hint for performance optimization is true?

Choose the correct answer.

SQL hint always leads you to the desirable plan.

There is a possibility that a different plan is generated than what you expected.

Applying SQL hint is always a good solution for a performance issue.

To solve a performance issue, you can try any SQL hint.

What are some of the steps you need to take when you rewrite queries?

There are three correct answers.

Draw the optimizer tree first to understand the data flow.

Find the dominant operator and possible key reducers.

Check if the changed result is acceptable for your business scenario.

Capture the SQL trace.

Try a random SQL hint.

Which of the following statements regarding query inlining is true?

Choose the correct answer.

It combines several queries into a single query.

If the queries are fully inlined, several queries are executed sequentially.

If the queries are fully inlined, the search space becomes small.

Query inlining ensures the optimizer always generates the optimal plan.

10.

What is one of the benefits of query inlining?

Choose the correct answer.

The result of each query is stored.

All the queries are executed one by one.

It always results in an optimal plan.

It is compiled and executed less often than a non-inlined queries.