Ucertify 1z0-054 Questions are updated and all 1z0-054 answers are verified by experts. Once you have completely prepared with our 1z0-054 exam prep kits you will be ready for the real 1z0-054 exam without a problem. We have Rebirth Oracle 1z0-054 dumps study guide. PASSED 1z0-054 First attempt! Here What I Did.

♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1z0-054 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 1z0-054 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1z0-054-exam-dumps.html

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

Q102. You are managing an online transaction processing (OLTP) system. The database is supporting a large number of applications using connection pools to connect to the database.

Which method would you use for performance management?

A. Service aggregation for action within application module

B. Monitoring individual sessions by querying V$SESSION_LONGOPS

C. Monitoring individual sessions in an application by querying V$SESSION

D. Mapping the sessions for an application to one resource consumer group

Answer: A

Q103. Which is the correct description of SQL profiling while using SQL Tuning Advisor?

A. It is a set of recommendations by the optimizer to create new indexes.

B. It is auxiliary information collected by the optimizer for a SQL statement to eliminate estimation error.

C. It is a set of recommendations by the optimizer to refresh stale statistics to avoid bad execution plan.

D. It is a set of recommendations by the optimizer to restructure a SQL statement to avoid bad execution plan.

Answer: B

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

Q105. Your company wants to migrate a single instance Oracle 11g Release 1 database to a RAC environment. The database supports middle-tier applications using connection pooling for connecting to database.

Being a part of the performance management team, you want to test the performance of the SQL statements and the peak workload of the applications in the new environment before upgrading the production system.

Given below are some of the steps in random order to accomplish the task:

1) Capture the real workload on the existing system.

2) Create a SQL Tuning Set (STS) for the statements executed by the application.

3) Transport STS on the test environment and use SQL Tuning Advisor to compare the before and after performance.

4) Replay the workload on the test environment.

5) Analyze the divergence after replay.

6) Use Automatic Workload Repository (AWR) to capture the information for top SQL on the existing system.

7) Transport STS on the test environment and use SQL Performance Analyzer to compare the before and after performance.

Identify the correct steps required to perform the test.

A. 2 and 3 only

B. 6 and 7 only

C. 1, 4, and 5 only

D. 2, 3, and 5 only

E. 2, 3, 4, and 5 only

Answer: C

Q106. As a DBA, you notice that the response time of your database has degraded.

Which two pieces of tuning-related information can you examine in the alert log to identify possible causes? (Choose two.)

A. frequency of log switches

B. frequency of block corruption errors

C. concurrent parsing and buffer cache latch contention

D. the amount of CPU time spent on database user-level calls

Answer: AB

Q107. The columns CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID are frequently used together in the WHERE clause of the queries. The CUSTOMERS table is a big table with 20 GB of data. You notice that the selectivity for these three columns varies from the selectivity that the optimizer calculates.

What would you recommend to influence the selectivity calculated by the optimizer?

A. creating function-based indexes by concatenating all the columns

B. updating histogram statistics for these columns by using the DBMS_STATS.GATHER_TABLE_STATS procedure

C. using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and create index on the virtual column

D. using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and the DBMS_STATS.GATHER_TABLE_STATS procedure to collect statistics on the virtual column

Answer: D

Q108. You work for ABC Pvt Ltd. The company has recently upgraded one of its development databases to Oracle Database 11g from Oracle Database 10g. You noticed that the System Global Area (SGA) is undersized as shown in the Exhibit. To investigate further, you checked the related parameters as shown below:

SQL> show parameter sga_max_size


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

sga_max_size big integer 500M

SQL> show parameter target


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

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 0

memory_target big integer 0

pga_aggregate_target big integer 384M

sga_target big integer 384M

Which is the best solution that you would recommend?


A. Increase the value of the SGA_MAX_SIZE parameter.


C. Reduce the value of the PGA_AGGREGATE_TARGET parameter.

D. Increase the value of both SGA_TARGET and PGA_AGGREGATE_TARGET parameters.

E. Diagnose further to identify which component in SGA is undersized and then resize it.

Answer: B

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

Q110. You are a DBA in ABC Corp. You are working on a DSS system.. The applications directly connect to the database to perform transactions. The application users also perform direct loads. The company wants to upgrade from Oracle Database 10g version to Oracle Database 11g. You want to test the performance of the SQL statements in the new environment before upgrading the production database. To accomplish this, you set up the test database with Oracle Database 11g, with the same data and schemas as in production.

What would you recommend to accomplish the task?

A. gathering all the SQL statements and using SQL Tuning Advisor on the test database for performance analysis

B. capturing the workload on the production database, replaying it on the test machine, and comparing the performance

C. creating a SQL Tuning Set by capturing the SQL workload on the production database and using SQL Performance Analyzer for performance analysis

D. configuring AWR to capture the maximum number of SQL statements, transporting it to the test system, and using SQL Access Advisor for performance analysis

Answer: C