← Back to exam page

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

Loading demo links...

Showing 1–3 of 10 questions

Question 1

You are working on an online transaction processing (OLTP) system. The middle-tier applications use connection pooling to connect to the database. Presently, you have a single-node database. The company plans to migrate the database to a RAC environment. Before you move to a RAC environment, you want to test the performance of the SQL statements and the peak workload on the new environment. To accomplish the Database Capture for replay, you identified the peak workload period on the existing system and started the Database Capture. Which client requests to the database can be captured as part of the workload capture? (Choose all that apply.)

Select all that apply, then click Submit answer.

  • ○

    flashback query
  • ○

    distributed transactions
  • ○

    logging in and logging out of sessions
  • ○

    all DDL statements having bind variables
  • ○

    direct path load of data from external files
Question 2

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

Which two conclusions can you draw from this? (Choose two.)

Select all that apply, then click Submit answer.

  • ○

    The latch was requested in no wait mode.
  • ○

    The latch was requested in willing-to-wait mode.
  • ○

    The shared pool operations to allocate and free memory in it caused the misses.
  • ○

    Automatic Memory Management is not enabled because the IMMEDIATE_GETS and IMMEDIATE_MISSES columns have zero in them.
Question 3

You are working on a database that was upgraded to Oracle Database 11g from Oracle Database 9i. An ADDM finding in this database says that the shared pool is inadequately sized, as shown in the Exhibit.

a45841eab7aa4f57aeb2a0c99761b5c5

You diagnosed that this is due to different kinds of workloads and this occurs only during peak hours. The following are the related parameter settings:

SQL> show parameter sga

name TYPE VALUE

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

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 300M

sga_target big integer 0

SQL> show parameter target

name TYPE VALUE

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

.....

fast_start_mttr_target integer 0

memory_max_target big integer 0

memory_target big integer 0

pga_aggregate_target big integer 100M

sga_target big integer 0

You want to balance the memory between the System Global Area (SGA) components within the SGA

depending on the workloads. Which two options would solve this problem? (Choose two.)

Select all that apply, then click Submit answer.

  • ○

    Set the SGA_TARGET parameter to 300M.
  • ○

    Set the SGA_MAX_SIZE parameter to 400M.
  • ○

    Set the MEMORY_MAX_TARGET and MEMORY_TARGET parameters to 400M.
  • ○

    Set the MEMORY_MAX_TARGET and MEMORY_TARGET parameters to 100M.
  • ○

    Set the PGA_AGGREGATE_TARGET parameter to 200M and the SGA_MAX_SIZE parameter to 400M.