1z0-084

1z0-084 practice questions, page 4 of 5, with answer review and explanations.

Question 16

Which three statements are true about using the In Memory (IM) column store? (Choose three.)

  1. It does not improve performance for queries using cached results of function evaluations on columns from the same table.
  2. It does not improve performance for queries that use join groups on columns from different tables.
  3. It can improve OLTP workload performance by avoiding the use of indexes.
  4. It does not improve performance for queries using user-defined virtual column results.
  5. It does not require all database data to fit in memory to improve query performance.
  6. It improves performance for queries joining several tables using bloom filter joins.

Question 17

An Oracle 19c database uses default values for all optimizer initialization parameters. After a table undergoes partition maintenance, a large number of wait events occur for: cursor: pin S wait on X Which command reduces the number of these wait events?

  1. ALTER SYSTEM SET CURSOR_SHARING = FORCE;
  2. ALTER SYSTEM SET CURSOR_SPACE_FOR_TIME = TRUE;
  3. ALTER SYSTEM SET CURSOR_INVALIDATION = DEFERRED;
  4. ALTER SYSTEM SET SESSION_CACHED_CURSORS = 500;

Question 18

Which two statements are true about cursor sharing? (Choose two.)

  1. Setting cursor_sharing to FORCE can result in a plan that is suboptimal for the majority of values bound to a bind variable when executing a cursor with one or more bind variables.
  2. Adaptive Cursor Sharing guarantees that a suboptimal plan will never be used on any execution of a SQL statement.
  3. Setting optimizer_capture_sql_plan_baselines to TRUE loads all adaptive plans for the same statement into the cursor cache.
  4. Setting cursor_sharing to EXACT prevents Adaptive Cursor Sharing from being used.
  5. Adaptive Cursor Sharing requires histograms on filtered columns, used in equality predicates, to allow different execution plans to be generated for statements whose bound values would normally generate different plans at hard parse time.

Question 19

Examine this statement and output: Which three statements are true? (Choose three.)

  1. Both 9822 and 8779 sessions are waiting for operating system resources.
  2. Session 8779 may be waiting due to a network problem.
  3. Session 9857 waited 1354 seconds for another process, which was also waiting for a transaction to end.
  4. Session 9857 is not waiting.
  5. Session 8779 may be waiting for a user or application response.
  6. Session 9822 will always stop waiting if the session that owns the TX enqueue issues a COMMIT statement as session 9822 is the first session in the transaction queue.

Question 20

Accessing the SALES table causes excessive db file sequential read wait events. Examine this AWR excerpt: Now, examine these attributes displayed by querying DBA_TABLES: Finally, examine these parameter settings: Which two must both be used to reduce these excessive waits? (Choose two.)

  1. Partition the SALES table.
  2. Increase PCTFREE for the SALES table.
  3. Re-create the SALES table.
  4. Compress the SALES table.
  5. Coalesce all SALES table indexes.