Oracle Database 12c R2: SQL Tuning for Developers Ed 2 Quiz Questions and Answers

Although views provide clean programming interfaces, they should be used carefully because they can cause suboptimal, resource-intensive queries when nested too deeply.

Answer :
  • TRUE

Identify the characteristics that must be supported by an application that is designed for SQL execution efficiency.

Answer :
  • Use cursors so that SQL statements are parsed once and executed multiple times.

In an environment with an application server that uses a connection pool, you use ________ to identify which trace files need to be combined to get an overall trace of the application.

Answer :
  • DBMS_APPLICATION_INFO

The _________ step involves separating the pieces of a SQL statement into a data structure that can be processed by other routines.

Answer :
  • Parsing

The view-merging optimization applies to views that contain only selections, projections, and joins.

Answer :
  • TRUE

Which statements are true in 12c?

Answer :
  • A query transformation has taken place.
  • The optimizer considered a nested loops join when table p and view v1 are joined even with the GROUP BY clause in the view.
  • A join predicate pushdown has become possible, and you are now taking advantage of the index on the SALES table to do a nested loops join instead of a hash join.

An EXPLAIN PLAN command executes the statement and inserts the plan used by the optimizer into a table.

Answer :
  • FALSE

Which of the following is NOT true about a PLAN_TABLE?

Answer :
  • You cannot create your own PLAN_TABLE.

A user needs to be granted some specialized privileges to generate AUTOTRACE statistics.

Answer :
  • TRUE

After monitoring is initiated, an entry is added to the _______view. This entry tracks the key performance metrics collected for an execution.

Answer :
  • V$PLAN_MONITOR