← Back to exam page

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

Loading demo links...

Showing 4–6 of 10 questions

Question 4

Examine the output of the following query:

SQL> SELECT c.name,a.addr,a.gets,a.misses,a.sleeps,

2 a.immediate_gets,a.immediate_misses,b.pid

3 FROM v$latch a, v$latchholder b, v$latchname c

4 WHERE a.addr = b.laddr(+) and a.latch# = c.latch#

5 AND c.name LIKE '&latch_name%' ORDER BY a.latch#;

LATCH NAME ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

------------ -------- -------- ------- ------ -------------- ----------------

shared pool 20016544 8520540 14112 3137 0 0

You calculated the Gets-to-Misses ratio, which is .99834, and this ratio is dropping over a period of time.

Which two actions can improve this? (Choose two.)

Select all that apply, then click Submit answer.

  • ○

    reducing hard parses
  • ○

    increasing the size of the shared pool
  • ○

    using only dedicated server connections
  • ○

    setting the CURSOR_SHARING parameter to EXACT
  • ○

    encouraging the use of more literal SQL statements
Question 5

You are working as a DBA in ABC Corp. You are working on online transaction processing (OLTP) system. The database uses ASM storage. One of the ASM disk goes offline because of hardware failure. When the disk is added back again after the fix, because the database is active, rebalance operations impact the I/O subsystem.Which two recommendations would you give to lower the impact on the I/O subsystem? (Choose two.)

Select all that apply, then click Submit answer.

  • ○

    Increase the number of ASMB processes.
  • ○

    Increase the number of DBWR_IO_SLAVES.
  • ○

    Decrease the value for the ASM_POWER_LIMIT parameter.
  • ○

    Increase the value for the ASM_POWER_LIMIT parameter.
  • ○

    Set the DISK_REPAIR_TIME disk attribute to a lower value.
  • ○

    Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.
Question 6

You observe that suboptimal execution plans for the queries are being generated on a table that previously used less resources. You have collected statistics on these tables two days ago. The optimizer statistics retention period is set to 31 days. You are able to find the timestamp information about statistics update from the DBA_TAB_STATS_HISTORY view. Because it is a frequently queried table, you would like the optimizer to generate better plans.

Which action would enable you to use the previous set of statistics on the objects that may lead to better execution plans?

Select an option, then click Submit answer.

  • ○

    restoring statistics from statistics history up to the desired time
  • ○

    deleting all AWR snapshots collected after the time of desired statistics collection
  • ○

    applying the flashback table technique until the time of desired statistics collection
  • ○

    setting the OPTIMIZER_PENDING_STATISTICS parameter to TRUE to use the previous version of statistics