Knowledge quiz

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

Which statement about the visualized plan is true?

Choose the correct answer.

It is useful to understand the execution tree shown in the visualized plan when analyzing a performance issue.

You cannot check how data is processed from the table.

The visualized plan shows a tabular view of the execution tree.

The explain plan is more useful than the visualized plan in terms of understanding the query execution process.

Which of the following statements about M_OUT_OF_MEMORY_EVENTS is true?

Choose the correct answer.

You cannot find the statement hash that caused OOM.

You can only find the statement string that caused OOM.

You can find the time of the OOM event.

You cannot use the statement hash from M_OUT_OF_MEMORY_EVENTS to look up other information in M_SQL_PLAN_CACHE.

Assume there is a left outer join between 2 column searches, and the child column search on right-hand side generates a large volume of records, which slows down performance. How can you tackle the issue?

Choose the correct answer.

Check whether you can add an additional operator to reduce the number of records in the column search on the right-hand side.

Check whether you can make an additional column search within the column searches.

Check whether you can remove the column search on the right-hand side.

Check whether you can try any SQL hint, regardless of the query result.

What could be a reason why a SQL hint is not applied?

Choose the correct answer.

Because the optimizer never works with the SQL hint

Because there is a possibility that the result of the query changes

Because there is possibility that an out-of-memory event occurs

Because there is a possibility that the result of the query is the same

If a possible key reducer is not moved using SQL hint, what would be the appropriate analysis approach?

Choose the correct answer.

Create an additional operator that reduces the intermediate results.

Try random hints until a hint moves the possible key reducer.

Rewrite the query regardless of the query result.

Try random hints until a hint improves the performance.

Assume there are three column searches. The “column search 3” is a parent and it has a left outer join between 2 column searches. The “column search 1” is the child column search on left-hand side and it scans only one table T1. The “column search 2” is the child column search on right-hand side and it has inner join operator of two another tables – T2 and T3. This “column search 2” generates a large volume of records, which slows down performance. You want to make an additional join to reduce the intermediate results from “column search 2”. Where can you make the additional join?

Choose the correct answer.

In column search 1

In column search 2

In column search 3

Both in column search 1 and column search 2

Which column do you need to check for the timestamp of the last query compilation in M_SQL_PLAN_CACHE?

Choose the correct answer.

LAST_EXECUTION_TIMESTAMP

LAST_PREPARATION_TIMESTAMP

TOTAL_EXECUTION_TIME

TOTAL_PREPARATION_TIME

When you look at a composite OOM dump, what information can you check?

There are three correct answers.

STATEMENT_MEMORY_LIMIT

Problematic query result

Problematic query string

Problematic query plan

Memory consumption for all the queries that executed at the time of the composite OOM dump

If composite OOM is not reproduced, what can you assume and check to analyze the issue?

There are three correct answers.

Something may have changed, such as the query plan or data statistics.

For issue analysis, you need to check data flow of the business logic.

For issue analysis, you need to check the query compilation and execution timeline.

For issue analysis, you don't need to check data statistics.

You should try to reproduce the issue until it is reproduced.

10.

Which monitoring view can you look up to check a timeline when a TRUNCATE statement is executed?

Choose the correct answer.

M_OUT_OF_MEMORY_EVENTS

M_SQL_PLAN_CACHE

M_EXECUTED_STATEMENTS

M_CACHES