A. Execute the statement with different bind values and set AUTOTRACE enabled for session.

B. Identify the SQL_ID for the statement and fetch the execution plan PLAN_TABLE.

C. View the execution plan for the statement from V$SQL_PLAN.

D. Identify the SQL_ID for the statementsand use DBMS_XPLAN.DISPLAY_CURSOR for that SQL_ID to view the execution plan.

E. Use the DBMS_XPLAN.DISPLAY function to view the execution plan.

A. The DEPT table is driving table and the EMP table join is the driven table.

B. The EMP table is the driving table and the DEPT table us the driven table.

C. Rows from the DEPT table are first hashed by the join key into memory and then joined to the EMP table on the join key.

D. The rows from the DEPT table are sorted first by the join key and then hashed into memory.

E. Rows from both the tables are sorted by the join key, but only rows from the DEPT table are hashed into memory.

A. Rows are fetched from the indexes on the PRODUCTS table and from the SALES table using full table scan simultaneously, and then hashed into memory.

B. Rows are fetched from the SALES table, and then a hash join operator joins with rows fetched from indexes on the PRODUCTS table.

C. The row with the ID column having the value 0 is the first step execution plan.

D. All the partitions of the SALES table are read in parallel.

A. Hints can be used to override the optimization approach specified with the OPTIMIZER_MODE parameter.

B. The optimizer uses the execution plan with lower cost even if a hint is specified.

C. The OPTIMIZER_FEATURES_ENABLE parameter must be set to a version supports the hints used.

D. Whenever a query uses table aliases, the hints in the query must use the aliases.

E. A schema name for the table must be used in the hint if the table us qualified in the FROM clause.

F. A statement block can have only one hint, and that hint must be immediately after SELECT, UPDATE, INSERT, MERGE, or DELETE keyword.

A. Subquery unnesting

B. Subquery factoring

C. Join predicate push-down

D. Join conversion

A. A single trace file is created for each session that uses the ACC_SRV service.

B. All trace information for the service connection to inst1 will be stored in a single trace file.

C. Only those SQL statements that are identified with the ACC_SRV service executed on the inst1 instance are recorded in trace files.

D. A trace file is not created because the module name is not specified.

E. All trace information for the ACC_SRV service connected to inst1 is stored in multiple trace files, which can be consolidated by using the tkprof utility.

A. Use the tkprof utility to consolidate the trace files and create an output that can directly be used for diagnostic purposes.

B. Use all trace files as input for the tkprof utility to consolidate the trace files for a module.

C. Use the trcess utility to consolidate all trace files into a single output file, which can then be processed by the tkprof utility.

D. Use one trace file at a time as input for the trcess utility and use tkprof utility to consolidate all the output files for a module.

A. They provide metadata about distribution of and occurrences of values in a table column.

B. They help the optimizer in deciding whether to use an index or a full table scan.

C. They capture the distribution of different values in an index for better selectivity estimates.

D. They help the optimizer to determine the fastest table join order.

E. They can be used only with indexed columns.

F. They provide improved selectivity estimates in the presence of data skew, resulting in execution plans with uniform distribution.

A. The data is aggregated from the ORDERS table before joining to CUSTOMERS.

B. The NESTED LOOP OUTER join is performed because the OPTIMZER_MODE parameter is set to ALL_ROWS.

C. For every row of CUSTOMERS table, the row matching the join predicate from the ORDERS table are returned.

D. An outer join returns NULL for the ORDERS table columns along with the CUSTOMERS table rows when it does not find any corresponding rows in the ORDER table.

A. The displayed plan may not necessarily be used by the optimizer.

B. The query will not fetch any rows; it will display only the execution plan and statistics.

C. Subsequent execution of this statement will use the displayed plan that is stored in v$SQL.

D. The execution plan generated can be viewed from v$SQLAREA.

E. The displayed plan will be stored in PLAN_TABLE.