← Back to exam page

1z0-054 – Oracle Database 11g: Performance Tuning

Loading demo links...

Showing 7–9 of 10 questions

Question 7

You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. Because the automatic optimizer statistics collection runs during maintenance window(in the night), the statistics on tables that are significantly modified throughout the day becomes stale. This leads to poor query performance on these tables and you want to minimize the

overhead in statistics collection. Identify a solution to this problem.

Select an option, then click Submit answer.

  • ○

    Change the STALE_PERCENT preference to zero for the tables.
  • ○

    Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
  • ○

    Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to zero during day time.
  • ○

    Gather statistics on the table when the tables have a representative number of rows, and then lock the statistics.
Question 8

You plan to collect statistics for an important table in your database to help improved query performance. You are uncertain about the optimizer behavior after the statistics have been collected because currently there are no statistics for indexes or partitions on the table. You wish to test the performance effect of the newly collected statistics. How would you prevent the optimizer from using the newly collected statistics for other sessions immediately after statistics collection?

Select an option, then click Submit answer.

  • ○

    Set the PUBLISH preference to FALSE for the table.
  • ○

    Set the NO_VALIDATE preference to TRUE for the table.
  • ○

    Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.
  • ○

    Set the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter to FALSE for the session.
  • ○

    Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE for the session.
Question 9

View the Exhibit and analyze the content of V$BUFFER_POOL_STATISTICS.

280ba433faeb42a891a692aa384c7cc6

Why does the query on the EMP table not access buffers from the keep pool?

Select an option, then click Submit answer.

  • ○

    because the query used SCOTT.EMP instead of EMP
  • ○

    because the query with * always uses the default pool
  • ○

    because the query with * always forces physical I/Os that bypass the keep pool
  • ○

    because the blocks of the EMP table are already available in one of the other buffer pools