Category: 1Z0-054 (v.1)

Exam 1Z0-054: Oracle Database 11g: Performance Tuning (update June 30th, 2014)

Which SQL plan baseline would be used if the SQL query in exhibit1 is executed again when the value of OPTIMIZ

View the Exhibit exhibit1 to examine the series of SQL commands and parameter settings.

View the Exhibit exhibit2 to examine the plans available in the SQL plan baseline.

The first plan (in red) is created when OPTIMIZER_MODE is set to ALL_ROWS and the second
plan (in blue) is created when OPTIMIZER_MODE is set to FIRST_ROWS.
Which SQL plan baseline would be used if the SQL query in exhibit1 is executed again when the value of
OPTIMIZER_MODE is set to FIRST_ROWS?

What could be the problem in this database?

You work as a DBA for a company and you have the responsibility of managing one of its online
transaction processing (OLTP) systems. The database encountered performance-related
problems and you generated an Automatic Workload Repository (AWR) report to investigate it
further. View the Exhibits and examine the AWR report.



What could be the problem in this database?

What would be your next step to avoid this event in future?

You are working on an online transaction processing (OLTP) system. You detected free buffer
waits events for your database instance running in a machine that has multiple CPUs. You

increased the database buffer cache size as the first step. After a few hours of work on the
database, further investigation shows that the same event is being recorded. What would be your
next step to avoid this event in future?

Which action would solve this problem?

You are working on a development 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.

You diagnosed that this is due to different kinds of workloads and this occurs only during peak
hours. You tried to resize this by shrinking the database buffer cache but that caused inadequate
buffer cache problems. 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 SGA
without affecting the size of the Program Global Area (PGA). Which action would solve this
problem?

Why would you choose this method?

You observed that some of the queries are performing poorly on the SALES_RECORDS table in
your database.
On further investigation, you find that at the end of each day the contents of the
SALES_RECORDS table are transferred to the SALES table and deleted from the
SALES_RECORDS table. The deleted operations cause the table to be sparsely populated. You
decided to use the ALTER TABLE…SHRINK SPACE COMPACT command to shrink the table.
Why would you choose this method? (Choose all that apply.)

Which two statements are true about the report produced by this script?

A batch workload that historically completed in the maintenance window between 10:00 PM and
midnight is currently showing poor performance and completing at 2 AM. To help in the diagnosis
of the performance degradation, the senior DBA in your organization asks you to execute the
awrddrpt.sql script to produce a Compare Periods report. Which two statements are true about the
report produced by this script? (Choose two.)

Which action would allow you to perform the preceding task successfully?

You plan to use adaptive thresholds as part of the performance tuning activity. You decide to
increase the window size of the default moving window baseline for all metric observations and
comparisons in your database. The following error occurs when you try to increase the window
size through Enterprise Manager:
Failed to commit: ORA-13541: system moving window baseline size (1296000) greater than
retention (1036800) ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 601 ORA-06512: at line 2 Which action would allow you to perform the preceding task successfully?


Page 1 of 2012345...1020...Last »