Knowledge quiz

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

Which of the following statements about the CASCADE option for SQL Hint are correct?

There are two correct answers.

CASCADE is only supported in CALL statements.

CASCADE does not propagate SQL HINT into all inner SQL statements.

CASCADE is also propagated into the nested procedures.

CASCADE propagates SQL HINT into inner SQL statements before SQLScript optimization, so HINT is added on the definition code of SQLScript.

Which of the following statements about a parameterized query is correct?

Choose the correct answer.

A parameterized query must be recompiled whenever a different query parameter value is given.

Once a query plan of a parameterized query is compiled and cached in SQL Plan Cache, no additional compilation is needed when a parameter value is changed.

Once a query plan of a parameterized query is compiled, this query plan is always optimal for every parameter value.

The behavior of a parameterized query is the same as that of a literal query.

Which of following statements about the execution time of a CALL statement shown in Plan Visualizer trace is correct?

Choose the correct answer.

The compilation time of the inner SQL statements in a SQLScript procedure is included in the execution time of a CALL statement.

The compilation time of the inner SQL statements in a SQLScript procedure is not included in the execution time of a CALL statement.

The compilation time of the control-flow statements in a SQLScript procedure is included in the execution time of a CALL statement.

The compilation time of both the inner SQL statements and control-flow statements in a SQLScript procedure is included in the execution time of a CALL statement.

Assume there is a SQL statement "SELECT * FROM PRODUCTS" in SQLScript procedure, its STATEMENT_HASH value is "54f3a26840fd0eedc15f6c074cf26ada", and its plan ID is 12360002. What is the correct SQL statement to apply SQL HINT USE_HEX_PLAN for this SQL statement?

Choose the correct answer.

ALTER SYSTEM PIN STATEMENT HASH 12360002 WITH HINT (USE_HEX_PLAN);

ALTER SYSTEM PIN SQL PLAN CACHE ENTRY '54f3a26840fd0eedc15f6c074cf26ada' WITH HINT (USE_HEX_PLAN);

ALTER SYSTEM PIN STATEMENT HASH '54f3a26840fd0eedc15f6c074cf26ada' WITH HINT (USE_HEX_PLAN);

ALTER SYSTEM PIN SQL PLAN CACHE ENTRY 12360002 WITH HINT (USE_HEX_PLAN);

Which of following statements about SQL HINT NO_INLINE in SQLScript is correct?

Choose the correct answer.

SQL HINT NO_INLINE can block statement inlining and procedure flattening.

SQL HINT NO_INLINE cannot block statement inlining but can block procedure flattening.

SQL HINT NO_INLINE can block statement inlining but cannot block procedure flattening.

SQL HINT NO_INLINE can block neither statement inlining nor procedure flattening.

Which of following statements about using SQL Hints are correct?

There are two correct answers.

SQL Hints are instructions for the SAP HANA database server which influence the way a database request is processed.

SQL Hints can be used to optimize SAP HANA performance.

The amount of memory consumed by executing SQL/SQLScript cannot be changed by SQL Hints.

The SAP HANA database cannot support SQL Hints.

Which of following statements about using the inlining feature in SQLScript are correct?

There are two correct answers.

Inlining executes independent inner SQL statements in a SQLScript procedure in parallel.

Inlining merges dependent inner SQL statements in a SQLScript procedure into a single SQL statement.

Table variables are used to check dependencies among inner SQL statements in a SQLScript procedure.

Inlining eliminates dead code of imperative programming constructs in a SQLScript procedure.

Which of following statements about using table variables in SQLScript are correct?

There are two correct answers.

Table variables are used to store the result of a SQL statement in a SQLScript procedure.

Table variables cannot be used as a table in a FROM clause of a SQL statement in a SQLScript procedure.

Table variables are used to split a long complex SQL statement into multiple short queries in a SQLScript procedure.

Table variables must be used only once in a SQLScript procedure.

Which of the following statements about the SQLScript function BIND_AS_PARAMETER are correct?

There are two correct answers.

BIND_AS_PARAMETER controls the parameterization behavior of scalar variables explicitly.

BIND_AS_PARAMETER treats a scalar variable as a query parameter.

BIND_AS_PARAMETER treats a scalar variable as a constant value.

A SQL statement affected by BIND_AS_PARAMETER becomes a literal query.

10.

Assume that, unexpectedly, there is a very long running SQLScript procedure in your system. Which of the following statements about analyzing this performance issue are correct?

There are two correct answers.

It is helpful to collect the SQL Trace to find out which SQLScript procedure is taking a long time to run.

It is helpful to collect M_ACTIVE_PROCEDURES to find out which SQL statement in the SQLScript procedure is taking a long time to run.

It is helpful to collect "Explain Plan for CALL" to find out which SQLScript procedure is taking a long time to run.

It is helpful to collect the SQLScript Debugger to find out which SQL statement in the SQLScript procedure is taking a long time to run.