Oracle Database 11g Performance Tuning

Here you have the best Oracle 1z0-054 practice exam questions

You have 192 total questions to study from
Each page has 5 questions, making a total of 39 pages
You can navigate through the pages using the buttons at the bottom
This questions were last updated on October 23, 2025
This site is not affiliated with or endorsed by Oracle.

Question 1 of 192

After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in the SPA output. Identify the two actions that you would suggest for these regressed SQL statements. (Choose two.)

Running SQL Access Advisor

Adding them to SQL Plan Baseline

Submitting them to SQL Tuning Advisor

Running Automatic Database Diagnostic Monitor (ADDM)

Correct Answer: B, C

Question 2 of 192

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.
Exam 1z0-054: Question 2 - Image 1

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?

Set the SGA_TARGET parameter to 300M.

Set the SGA_MAX_SIZE parameter to 400M.

Set the MEMORY_TARGET parameter to 100M.

Set the MEMORY_MAX_TARGET parameter to 300M.

Correct Answer: A

Question 3 of 192

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

because it can be used during peak hours

because it avoids unnecessary cursor invalidation

because it adjusts the high-water mark (HWM) immediately

because you have long-running queries that might span the shrink operation

because it does not allow any data manipulation language (DML) operations, thereby making the shrink operation faster

Correct Answer: A, B, D

Question 4 of 192

View the Exhibit and examine a portion of the output obtained from the following query:
Exam 1z0-054: Question 4 - Image 1

SQL> SELECT * FROM v$sys_time_model;
Select three correct interpretations of the time model statistics. (Choose three.)

DB time includes the wait time of all the nonidle and idle user sessions.

SQL execute elapsed time includes the time spent in performing fetches of query results.

DB CPU includes the CPU time spent on database user-level calls and background CPU time.

SQL execute elapsed time includes components of the hard parse elapsed time like bind elapsed time.

DB time includes the connection management call elapsed time excluding the background process time.

Correct Answer: B, D, E

Question 5 of 192

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

It is refreshed automatically based on the moving window baseline.

It compares details between any two selected time periods of the same duration.

It normalizes the statistics by the amount of time spent on the database for each time period.

It compares details between two consecutive time periods of the same or different durations and is refreshed every 60 minutes.

Correct Answer: B, C