Exact of 1z0-054 pdf exam materials and braindumps for Oracle certification for client, Real Success Guaranteed with Updated 1z0-054 pdf dumps vce Materials. 100% PASS Oracle Database 11g: Performance Tuning exam Today!

2016 Sep 1z0-054 test

Q1. 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.

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


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

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 300M

sga_target big integer 0

SQL> show parameter target


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


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.)


A. Set the SGA_TARGET parameter to 300M.

B. Set the SGA_MAX_SIZE parameter to 400M.

C. Set the MEMORY_MAX_TARGET and MEMORY_TARGET parameters to 400M.

D. Set the MEMORY_MAX_TARGET and MEMORY_TARGET parameters to 100M.

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

Answer: AC

Q2. After an Automatic Database Diagnostic Monitor (ADDM) run, you receive a recommendation for buffer busy wait issue. View the Exhibit.

What is the significance of the Benefit(%) column in the Exhibit?


A. It produces better query plans if the recommendation is implemented.

B. It gives the maximum reduction in the number of input/output (I/O) operations.

C. It gives the maximum reduction in resource usage if the recommendation is implemented.

D. It gives the maximum reduction in database elapsed time if the recommendation is implemented.

Answer: D

Q3. Examine the following information from the AWR report:

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~ % Total

Event Waits Time (s) Call Time

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

CPU time 559 88.80

log file parallel write 2.181 28 4.42

SQL*Net more data from client 516.611 27 4.24

db file parallel write 13.383 13 2.04

db file sequential read 563 2 .27

Which could cause waits on the db file sequential read event?

A. too many full table scans

B. too large a database buffer cache

C. too many waits occurring while reading multiple blocks synchronously

D. too many index range scans occurring which refers to blocks not in the database buffer cache

Answer: D

Q4. You work as a consultant DBA for various clients. A performance issue in one of the online transaction processing (OLTP) systems is reported to you and you received the Automatic Workload Repository (AWR) report generated in the database. The main sections of the AWR report are shown in the Exhibits.

View the Exhibit named DBTIME and note % DB Time.

View the Exhibit named TIMEMODEL and note what has contributed to % DB Time.

View the Exhibit named EFFICIENCY and examine the various percentages shown.

Identify the option that has the correct answers for the questions given below:

1) Which factor indicates the main problem?

2) What is the main problem?

3) What solution would you recommend?

A. 1) The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time.

2) The CPU is very slow.

3) Increase the number of processors.

B. 1) hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODEL Exhibit.

2) This is due to inadequate Database Buffer Cache.

3) Increase the size of database buffer cache.

C. 1) sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the TIMEMODEL Exhibit.

2) There are too many soft parses.

3) Investigate the HOLD_CURSOR parameter setting in the application and set it appropriately.

D. 1) % Non-Parse CPU is low and Soft Parse % is also low in the EFFICIENCY Exhibit. This shows that very few statements are found in the cache.

2) There are too many hard parses.

3) Investigate the CURSOR_SHARING parameter setting and set it appropriately.

Answer: D

Q5. You work on an online transaction processing (OLTP) database in which the SALES table has 10,000 rows but only four distinct products are sold. View the Exhibit named HIST to check the distribution of values in the table and the histograms on the table.

View the Exhibits named QUERY-1 and QUERY-2 that show details in the V$SQL view for the queries executed on the SALES table.

You check the plan table and notice that both the queries that executed on the SALES table used index range scan. The second query retrieved most of the rows in the table but used index range scan.

Why would the second query use the same plan?

A. because the plan was bind aware

B. because the bind peeking never happened

C. because the OPEN_CURSORS parameter is set to a very low value

D. because the optimizer did not consider selectivity due to the use of bind variables

Answer: D


Refresh 1z0-054 free practice exam:

Q6. While reviewing the Top 5 Timed Events section in the Automatic Workload Repository report, you notice that the db file scattered read and db file sequential read events appear in the list.

Which two reasons could have caused these wait events? (Choose two.)

A. SQL statements performing frequent full table scans

B. tablespaces using manual segment space management

C. SQL statements performing frequent fast full index scans

D. excessive writes due to an aggressive checkpointing policy

Answer: AC

Q7. 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?

A. restoring statistics from statistics history up to the desired time

B. deleting all AWR snapshots collected after the time of desired statistics collection

C. applying the flashback table technique until the time of desired statistics collection

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

Answer: A

Q8. Identify two correct statements about the Active Session History (ASH) data. (Choose two.)

A. A part of SGA memory is used to store ASH data as rolling buffer.

B. The ASH data can be analyzed between any two small time intervals.

C. All ASH data in memory are flushed to disk by MMON in every 3 seconds.

D. All ASH data in memory are flushed to disk by MMNL process whenever the buffer is full.

Answer: AB

Q9. A user, SCOTT, complains that his database session, SID 249, is extremely slow in processing transactions. You check the ADDM report and do not find any overall database performance issues.

You decide to check the wait events, specifically in SCOTT's session and issue the following command:

SQL>SELECT wait_class_id, wait_class, total_waits, time_waited FROM v$session_wait_class

WHERE sid = 249;

View Exhibit1 and examine the output.

You note that the APPLICATION wait class is the second most expensive wait class after the IDLE wait class.

You issue the following query to identify individual waits in the APPLICATION wait class:

SQL>select event, total_waits, time_waited

from v$system_event e, v$event_name n

where n.event_id = e.event_id

and wait_class_id = 4217450380;

View Exhibit2 and examine the output.

Which view would you examine next to pinpoint the problem that is causing this performance issue?

A. DBA_HIST_SESSMETRIC_HISTORY - to find the highest value metrics in the session

B. V$SQLAREA statistics - to find the SQL statements with the highest number of executions

C. V$SESS_TIME_MODEL - to identify whether the SQL statements in the session were spending more of execution time in parsing or waiting for the CPU

D. V$EVENT_HISTOGRAM - to determine whether the TIME_WAITED is evenly distributed across the occurrences of the wait or whether some waits for the events were very long whilst others were short indicating more erratic response times.

Answer: D

Q10. View the Exhibit.Which statement is true about applying the threshold setting in the Exhibit.


A. The threshold does not depend on any baseline.

B. The threshold is decided by comparing the warning and threshold values with the LAST_NIGHT baseline.

C. The threshold is decided by comparing the warning and threshold values with the SYSTEM_MOVING_WINDOW baseline.

D. The threshold is decided by comparing the warning and critical values with the baseline set on the performance page.

Answer: A