Oracle 1Z0-084 Oracle Database 19c: Performance Management and Tuning Exam Exam Practice Test Questions

• Question 1

  You use SQL Tuning Advisor to tune a given SQL statement.The analysis eventually results in the implementation of a SQL Profile.You then generate the new SQL Profile plan and enforce it using a SQL Plan Baseline but forget to disable the SQL Profile and a few days later you find out that the SQL Profile is generating a new execution plan.Which two statements are true?

  • The existence of two concurrent plan stability methods generates a child cursor for every execution.
  • The SQL Profiles as well as SQL Plan Baseline are implemented using hints, so they both generate the same plan.
  • The execution plan is the one enforced by the SQL Profile.
  • The execution plan is the one enforced by the SQL Plan Baseline.
  • The SQL Plan Baseline must be accepted in order to be used for the execution plan.
  • The conflict between the two plan stability methods results in an error.

  Show Answer Answer: C,E Next

• Question 2

  What are the least elevated values of statistics_level and C0NTR0LJ4ANAGEMENT_PACK_ACCESS that allow the usage of Monitoring of Database Operations?

  • STATISTICS_LEVEL=ALL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC+TUNING
  • STATISTICS_LEVEL=BASIC and CONTROL_MANAGEMENT_PACK ACCESS=DIAGOSTIC
  • STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS-DIAGOSTIC*TUNING
  • STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC

  Show Answer Answer: A Next

• Question 3

  Which two Oracle Database features use database services?

  • Oracle Automatic Reoptimization
  • Database Resource Manager
  • Oracle SQL Performance Management
  • Oracle SQL Tuning Advisor
  • Oracle Scheduler

  Show Answer Answer: B,E Next

• Question 4

  Which three types of statistics are captured by statspack with snap level 6?

  • Parent and child latches
  • Parent and child latches
  • Optimizer execution plans
  • Plan usage data
  • Enqueue statistics
  • Segment-level statistics

  Show Answer Answer: A,E,F Next

• Question 5

  The CURS0R_SHARING and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameters are set to default. The top five wait events in an awr report are due to a large number of hard parses because of several almost identical SQL statements.Which two actions could reduce the number of hard parses?

  • Create the KEEP cache and cache tables accessed by the SQL statements.
  • Create the RECYCLE cache and cache tables accessed by the SQL statements.
  • Increase the size of the library cache.
  • Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE.
  • Set the CURSOR_SHARING parameter to FORCE.

  Show Answer Answer: C,E Next